《SQL监控及性能优化教学内容.doc》由会员分享，可在线阅读，更多相关《SQL监控及性能优化教学内容.doc（123页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Good is good, but better carries it.精益求精，善益求善。SQL监控及性能优化-SQL性能监控及SQL语句优化性能监控作为SQL的数据库服务器，我们可以将其比作一个人，而SQL则是他的心脏，管理员就是他的大脑。要监控心脏是否健康首先要看他这个人是否健康。这两者是相辅相成的，少了一方都是不健康的。数据库服务器的性能监视器性能监视器性能工具的介绍性能监视器是一种简单而功能强大的可视化工具，用于实时收集系统状态并从日志文件中查看性能数据。使用性能监视器可以:获得对诊断系统问题和规划系统资源增长有用的性能数据、了解工作负载及其对系统资源的影响、观察工作负载和资源使用情
2、况的变化和趋势，以便计划未来的升级、通过监视结果来测试配置变化、诊断问题并确定需要优化的组件或进程。从“开始”菜单上选择“运行”。在“打开”文本框中输入“perfmon”，然后单击“确定”。在性能监视器工具中，在左侧窗格里选择SystemMonitor对象，然后右击“性能”图表。选择“添加计数器”。现在，可以开始选择这些对象和要监视的计数器了。ASP.NET应用程序性能计数器有关ASP.NET应用程序性能计数器的大部分信息最近已被合并到一个题为“改善.NET应用程序的性能和伸缩性”的综合文档中。下表描述了一些可用于监视和优化ASP.NET应用程序（包括ReportingServices）性能的
3、重要计数器。性能对象计数器实例描述Processor（处理器）%ProcessorTime（处理器时间百分比）_Total“%ProcessorTime”监视运行Web服务器的计算机的CPU利用率。低CPU利用率或者无法最大化CPU利用率（无论客户端负载为多少）都表明Web应用程序中存在对资源的争用或锁定。Process（进程）%ProcessorTime（处理器时间百分比）aspnet_wp或w3wp（具体情况视IIS版本而定）由ASP.NET工作进程所使用的处理器时间所占的百分比。在将标准负载情况下的性能与先前捕获的基准进行对比时，如果此计数器的值出现下降，则说明降低了对处理器的需求，因此
4、也提高了伸缩性。Process（进程）WorkingSet（工作集）aspnet_wp或w3wp（具体情况视IIS版本而定）由ASP.NET主动使用的内存数量。虽然应用程序开发人员对应用程序使用的内存数量拥有最大的控制权，但系统管理员也可通过调整会话的超时期限来显著影响这一点。Process（进程）PrivateBytes（专有字节）aspnet_wp或w3wp（具体情况视IIS版本而定）PrivateBytes是当前分配给该进程且不能由其他进程共享的内存数量（以字节计）。不时出现的尖峰表明某些地方存在瓶颈，会导致工作进程继续持有不再需要的内存。如果此计数器突然下降为接近0的值，则可能表示AS
5、P.NET应用程序由于无法预料的问题进行了重启。为了验证这一点，请监视“ASP.NETApplicationRestarts”计数器。ASP.NETApplications（ASP.NET应用程序）Requests/Sec（每秒的请求数）_Total允许您检验请求的处理速度是否于发送速度相适应。如果每秒请求数的数值低于每秒产生的请求数，则会出现排队现象。这通常意味着已经超过了最大请求速度。ASP.NETApplications（ASP.NET应用程序）ErrorsTotal（总错误数）_Total在执行HTTP请求期间发生的错误总数。包括任何分析器、编译或运行时错误。此计数器是“ErrorsD
6、uringCompilation”（编译错误数）、“ErrorsDuringPreprocessing”（预处理错误数）和“ErrorsDuringExecution”（执行错误数）计数器的总和。运转正常的Web服务器不应产生任何错误。如果错误发生在ASP.NETWeb应用程序中，它们的存在可能会让实际的吞吐量结果产生偏差。ASP.NETRequestExecutionTime（请求执行时间）显示了呈现所请求页面并将其传送给用户所需的时间（以毫秒计）。跟踪此计数器通常要比跟踪页面呈现时间效果更好。此计数器可以更全面地衡量从开始到结束的整个请求时间。在与基准进行对比时，如果此计数器的平均值较低，
7、则说明应用程序的伸缩性和性能均得到了改善。ASP.NETApplicationRestarts（应用程序重新启动）应用程序在Web服务器生存期间发生重新启动的次数。每次发生Application_OnEnd事件时，应用程序的重新启动次数都会增加。应用程序进行重新启动的原因可能是：更改了Web.config文件、更改了存储在应用程序的bin目录下的程序集、或者WebForms页面中发生了太多的更改。如果此计数器的值出现意料之外的增加，说明某些不可预知的问题导致Web应用程序被关闭。在这种情况下，应该认真调查问题原因。ASP.NETRequestsQueued（排队的请求数）在队列中等待服务的请求
8、数。如果此数字随着客户端负载的增加而呈现线性的增长，则说明Web服务器计算机已经达到了它能够处理的并发请求极限。此计数器的默认最大值为5,000。您可以在计算机的Machine.config文件中更改此设置。ASP.NETWorkerProcessRestarts（工作进程重新启动）工作进程在服务器计算机上重新启动的次数。如果出现意料之外的故障或者被有意回收，则工作进程会重新启动。如果此计数器的值出现意料之外的增加，应认真调查问题原因。除了上表中介绍的这些核心监视要素之外，在您试图诊断ASP.NET应用程序具有的特定性能问题时，下表中的性能计数器也可对您有所帮助。性能对象计数器实例描述ASP.
9、NETApplications（ASP.NET应用程序）PipelineInstanceCount（管线实例计数）_Total指定ASP.NET应用程序的活动请求管线实例的数量。由于只有一个执行线程可以在管线实例内运行，所以此数值反映了为特定应用程序处理的并发请求的最大数量。大多数情况下，在存在负载的情况下此数值较低为佳，这表明处理器得到了很好的利用。.NETCLRExceptions（.NETCLR异常）#ofExcepsThrown（引发的异常数）显示应用程序中引发的异常数。如果此数值出现意料之外的增加，说明可能存在性能问题。如果仅仅存在异常，则并不需要担心，因为异常对于某些代码路径来说是
10、正常工作的一部分。例如，HttpResponse.Redirect方法通过引发一个不可捕获的异常ThreadAbortException来完成工作。同样，对ASP.NET应用程序跟踪此计数器也更加有用。使用“ErrorsTotal”计数器确定该异常是否将导致应用程序出现意料之外的错误。System（系统）ContextSwitches/sec（每秒的上下文切换次数）测量Web服务器计算机上所有处理器切换线程上下文的速度。如果此计数器的值很高，可能表示对锁的争用频繁发生，或者在线程的用户模式和内核模式之间切换频繁。使用采样优化程序和其他工具执行进一步调查可证实上述猜测。ReportingServ
11、ices性能计数器ReportingServices包括一组它自己的性能计数器，用于收集有关报告处理和资源消耗方面的信息。可通过Windows性能监视器工具中出现的两个对象来监视实例和组件的状态和活动：MSRS2005WebService和MSRS2005WindowsService对象。MSRS2005WebService性能对象包括一组用来跟踪ReportServer处理过程的计数器，这些处理过程通常通过在线交互式报告浏览操作而引发。这些计数器在ASP.NET停止该Web服务后被重设。下表列出了可用于监视ReportServer性能的计数器，并描述了它们的目的。性能对象：RSWebServ
12、ice计数器描述ActiveSessions（活动会话数）活动会话的数量。此计数器反映了尚未过期的所有浏览器会话总数。这并不是同时处理的请求数，而是存储在ReportServerTempDB数据库中的会话数量。CacheHits/Sec（每秒缓存命中次数）每秒从目录中取得的报告请求的数量。如果此值增加，而“MemoryCacheHits”的值不增加，则说明报告数据没有被重新处理，但是页面被重新呈现。将此计数器与MemoryCacheHits/Sec计数器一同使用，可以确定用于缓存、磁盘或内存的资源是否充足。CacheMisses/Sec（每秒缓存未命中数）每秒未能从目录中（与内存中相对）返回报
13、告的请求数量。将此计数器与MemoryCacheMisses/Sec计数器一同使用，可以确定用于缓存、磁盘或内存的资源是否充足。FirstSessionRequests/Sec（每秒的首次会话请求数）每秒中从ReportServer缓存中启动的新的用户会话数量。MemoryCacheHits/Sec（每秒内存缓存命中数）每秒中从内存中的缓存里取得报告的次数。内存中缓存是ReportingServices缓存的一部分，用于在内存或临时文件中保存已呈现过的报告。这样可以为请求提供最佳的性能，因为无需执行任何处理工作。如果使用内存中缓存，报告服务器将不会通过查询SQLServer来获得缓存的内容。M
14、emoryCacheMisses/Sec（每秒内存缓存未命中数）每秒中未能从内存中的缓存里取得报告的次数。NextSessionRequests/Sec（每秒的下一次会话请求）每秒在现有会话中请求打开报告的次数。ReportRequests（报告请求）当前处于活动状态并且将由ReportServer进行处理的报告数量。ReportsExecuted/Sec（每秒执行的报告数）每秒成功执行的报告的数量。此计数器提供了有关报告处理量的统计信息。综合使用此计数器和Request/Sec，比较可从缓存中返回的报告请求的执行情况。Requests/Sec（每秒的请求数）每秒向ReportServer发出
15、的请求数。此计数器跟踪由ReportServer处理的所有类型的请求。TotalCacheHits（缓存命中总数）自服务启动以来，从缓存中获得报告的请求总数。此计数器在ASP.NET停止该Web服务后被重设。TotalCacheMisses（总的缓存未命中数）自服务启动以来，不能从缓存中获得报告的总次数。此计数器在ASP.NET停止该Web服务后被重设。可使用此计数器确定磁盘空间和内存是否充足。TotalMemoryCacheHits（总的内存缓存命中数）自服务启动以来，从内存中缓存里返回的已缓存报告的总数。此计数器在ASP.NET停止该Web服务后被重设。内存中缓存是在CPU内存中存储报告的
16、那部分缓存。如果使用内存中缓存，报告服务器将不会通过查询SQLServer来获得缓存的内容。TotalMemoryCacheMisses（总的缓存未命中数）自服务启动以来，针对内存中缓存的缓存未命中总数。此计数器在ASP.NET停止该Web服务后被重设。TotalProcessingFailures（处理故障总数）自服务启动以来，发生的所有报告处理故障的总数。此计数器在ASP.NET停止该Web服务后被重设。处理故障可能来自报告处理器，也可能来自任何扩展。TotalReportsExecuted（执行的报告总数）自服务启动以来得到成功执行的报告的总数。TotalRequests（总请求数）自服
17、务启动以来，向ReportServer发送的所有请求的总数。RSWindowsService性能对象包括一组用于跟踪报告处理过程的计数器，这些处理过程是通过预定操作而引发的。预定操作可能包括订阅和交付、报告执行快照以及报告历史。微软的工作负载中并不包含任何预定操作或交付操作，此处列出这些性能计数器仅是便于您进行参考。可使用此性能对象监视ReportServerWindows服务。如果您准备在一个横向伸缩配置中运行ReportServer，那么这些计数器应用于所选的服务器，而不是应用于横向伸缩配置整体。这些计数器在应用程序域回收之时将被重设。下表列出了可用于监视预定和交付操作的计数器，并描述了它
18、们的目的。性能对象：RSWindowsService计数器描述CacheFlushes/Sec(每秒缓存刷新次数)每秒刷新缓存的次数。CacheHits/Sec（每秒缓存命中数）每秒获取到缓存报告的请求数量。CacheMisses/Sec（每秒缓存未命中数）每秒未能从缓存中获得报告的请求的数量。Delivers/Sec（每秒交付数）每秒从各种交付扩展交付的报告的数量。Events/Sec（每秒事件数）每秒处理的事件数量。被监视的事件，包括SnapshotUpdated和TimedSubscription。MemoryCacheHits/Sec（每秒内存缓存命中数）每秒中从内存中的缓存里取得报告
19、的次数。MemoryCacheMisses/Sec（每秒内存缓存未命中数）每秒中未能从内存中的缓存里取得报告的次数。ReportRequests（报告请求数）当前处于活动状态并且将由ReportServer进行处理的报告数量。可使用此计数器评估缓存策略。向特定呈现扩展提交的请求数。请求的数量可能比执行的报告数量多许多。ReportsExecuted/Sec（每秒执行的报告数）每秒成功执行的报告的数量。SnapshotUpdates/Sec（每秒快照更新数）每秒报告执行快照的预定更新数量。TotalAppDomainRecycles（应用程序域回收总数）自服务启动以来回收的应用程序域总数。Tot
20、alCacheFlushes（缓存刷新总数）自服务启动以来，ReportServer的缓存更新总数。TotalCacheHits（缓存命中总数）自服务启动以来，从缓存中获得报告的请求总数。TotalCacheMisses（总的缓存未命中数）自服务启动以来，不能从缓存中获得报告的总次数。可使用此计数器确定是否需要更多磁盘空间或内存。TotalDeliveries（总交付数）由SchedulingandDeliveryProcessor交付的报告总数（对于所有交付扩展）。TotalEvents（总事件数）自服务启动以来发生的事件的总数。TotalMemoryCacheHits（总的内存缓存命中数）
21、自服务启动以来，从内存中缓存里返回的已缓存报告的总数。TotalMemoryCacheMisses（总的缓存未命中数）自服务启动以来，针对内存中缓存的缓存未命中总数。TotalProcessingFailures（处理故障总数）自服务启动以来，发生的所有报告处理故障的总数。处理故障可能来自报告处理器，也可能来自任何扩展。TotalRejectedThreads（被拒绝的线程总数）拒绝执行异步处理后在同一线程中作为同步过程在以后进行处理的数据处理线程总数。TotalReportExecutions（报告执行总数）已执行报告的总数。TotalRequests（请求总数）自服务启动以来得到成功执行的
22、报告的总数。TotalSnapshotUpdates（快照更新总数）自服务启动以来，报告执行快照进行更新的总数。如果您打算排除ReportingServices存在的性能问题，记录以下性能计数器通常很有帮助：ASP.NET、ASP.NETApplications、Process、System、Memory、PhysicalDisks、.NETExceptions、.NETMemory、.NETLoading、.NETCLRLocksandThreads以及.NETCLRData。可选的ReportingServices性能计数器以下列出了一些适用于RSWebService但在默认情况下并未安装
23、的性能计数器。但是，在执行性能优化工作时，可以通过这些计数器来改善您洞察性能的能力。为实现这个目的，请在命令提示符中执行以下语句：installutil.exe/uReportingServicesLibrary.dll然后再执行：installutil.exeReportingServicesLibrary.dll为了成功执行该语句，您可能首先需要修改您的路径，在路径中包含Microsoft.NETFramework的安装目录。在路径修改完毕后，请从包含ReportingServicesLibrary.dll文件的目录下执行先前语句。默认情况下，该文件安装在C:ProgramFilesMic
24、rosoftSQLServerMSSQLMSSQL.instanceReportingServicesReportServerbin目录下。这些计数器没有进行彻底的本地化。ActiveDatabaseConnections（活动数据库连接）某个时间处于活动状态的数据库连接的数量。只统计指向ReportServer目录的连接。ActiveDatasourceConnections（活动数据源连接）某个时间处于活动状态的数据库连接的数量。只统计由当前运行的报告打开的数据源连接。ActiveThreads（活动线程）当前处于活动状态的线程数量。在Web服务中，它包含一些为请求提供服务的线程。在交付服
25、务中，它包含工作线程以及维护和轮询线程。Bytecount（字节计数）对于上一次请求，在呈现当前报告时向客户端返回的字节数量。这与对应的执行日志条目相类似。RowCount（行计数）对于上一次请求，由当前报告返回的行的数量。这与对应的执行日志条目相类似。TimeinCompression（压缩时间）对于上一次请求，在快照和PDF报告压缩上花费的时间（以毫秒计）。Timeindatasourceaccess（数据源访问时间）对于上一次请求，在获取报告的数据源信息上花费的时间（以毫秒计）。其中包括执行查询和取回结果所需的时间。这与对应的执行日志条目相类似。Timeindatabase（数据库时间）
26、对于上一次请求，在获取ReportServer目录信息上花费的时间（以毫秒计）。Timeinprocessing（处理时间）对于上一次请求，在报告处理上花费的时间（以毫秒计）。这与对应的执行日志条目相类似。Timeinrendering（呈现时间）对于上一次请求，在呈现报告上花费的时间（以毫秒计）。这与对应的执行日志条目相类似。以上红色文字的性能指标为常用这里并不完全下面以OA数据库实例来说明。Cpu使用监控添加处理器(Processor)的计数器计数器中常用的是处理时间（processortime）右边的选项框中是处理器的实例。processortime指处理器用来执行非闲置线程时间的百分比
27、。计算方法是，测量范例间隔内非闲置线程活动的时间，用范例间隔减去该值。(每台处理器有一个闲置线程，该线程在没有其他线程可以运行时消耗周期)。这个计数器是处理器活动的主要说明器，显示在范例间隔时所观察的繁忙时间平均百分比。这个值是用100%减去该服务不活动的时间计算出来的。服务器有多少cpu就有多少实例，编号从0至n-1。OA为4核4线程cpu故有16个实例。缓存使用监控添加缓存（Cache）的计数器计数器中常用的是复制读取百分比（CopyReadHits）以及数据命中百分比（DataMapHits）它是指：由于页面已经在物理内存中，可以不从磁盘上检索页面的情况下解析在文件系统缓存中的数据映射的
28、百分比。内存使用监控PageReads/secPageWrites/secPageInput/secPageOutput/secPages/sec这些值都正常都很小SQL的事务监控可以用来监控sql事务的量SQL的锁监控这里有两个计数器LockWaits/sec（每秒钟的锁数量）NumberofDeadlocks/sec（每秒钟的死锁数量）。相信对sql锁关心的人也会注意到这两个计数器。当然，SQL自身的监控可以捕获到有关锁的更加具体、清晰的信息。物理磁盘的监控这里常用的计数器是Avg.DiskQueueLength（读取和写入请求的平均数）Avg.Disksec/Read（指以秒计算的在此盘
29、上读取数据的所需平均时间。）Avg.Disksec/Write（指以秒计算的在此盘上写入数据的所需平均时间。）这些事关乎到物理磁盘的性能，提供检测硬件的信息。我们知道数据使用久了会产生索引碎片，这也是不容忽视的。索引碎片在磁盘上的表现就是磁盘碎片多，当然磁盘碎片多还有其他原因，但作为数据库服务器来说，大多的碎片都是由数据产生的。我们也可以使用微软自带的磁盘碎片整理工具，但是要特别注意，在生产环境上不要使用，或者热备份后转移，或者暂停服务（当然大多生产都是不能停机的）。日志事件信息查询在数据库服务器或者SQL实例出现问题的情况下如何去查看由服务器记录的日志事件等信息？打开计算机管理，在系统工具中
30、我们可以看到事件查看器、性能日志和警报这两栏。在应用程序中可以看到右边很多警告、信息、审核失败等条目。同事记录了发生的时间、来源、用户、事件等信息。双击条目后展现出事件属性对话框。如果你对于该时间不是很熟悉，你可以看事件的描述，还可以利用时间ID去网上寻找资源，这一点非常有用。选择系统后可以看到记录的相关事件信息。用同样的方法来查看明细，确保到每个细节。而性能日志和警报栏里则显示的是监控保存的记录。SQL的性能监控在SQL的性能工具里有个SQLServerProfiler新建跟踪这里我们可以看到有个使用模板下拉框，在此可以选择模板，有StanardTSQLTSQL_DurationTSQL_G
31、roupedTSQL_LocksTSQL_ReplayTSQL_SpsTuning常用的是StanardTSQLTSQL_LocksTuning这里的模板也就是侧重点不同而选择了不同的事件以及事件的列名。我们看到这里有个TSQL_Locks监控，我很高兴看到这个SQL的锁监控，因为它更加具体的展现了锁的情况。这里选择保存到文件，如果不选择，SQL自动临时保存，关闭后就删除。设置文件大小，看数据库的应用而定。这里设置1G的意思是如果保存的文件达到1G则自动新建一个文件。SQL的命名是原始文件后添加阿拉伯数字12345。勾选启用跟踪停止时间，这样可以省去人工关闭。再来看看事件选择当鼠标移动到相应的
32、事件以及列名时会在左下方显示相关的说明，方便操作人员了解。当然在跟踪的时候可以进行筛选。将自己特别重点考虑的性能记录下来以供参考，其他的数据忽略，这样方便以后的查找。点击列筛选器弹出对话框：例如我们设定事件使用的CPU时间（毫秒）列并设置大于等于700同时勾选排除不包含值的行，这样我们就对数据进行了筛选。那如果你又要查CPU时间超过1000的怎么办？不怕，当跟踪结束后，打开Profiler读取已经保存的跟踪文件，在设置属性里，同样可以进行筛选。这样就方便了操作员进行数据的分析。在同个基础数据中进行分析，我非常赞同这点。有的人可能会问，会不会出现我需要的事件或者列模板没有显示怎么办？不怕，看到右
33、下角的显示所有事件，显示所有列了吗？勾选上，然后去挑自己需要的事件和列吧。呵呵。当然，如果你觉得每次都要自己勾选列名比较麻烦，那么就新建一个模板吧，一劳永逸，下次监控，你只需要选用自己的模板即可。运行后显示：当选中一行后，在下方将显示执行的SQL语句，如果此语句的CPU时间过长，我们可以拿出该语句进行分析，优化。另外SQL还有一个活动监视器，可以用来监视数据库服务器的性能情况。这里活动监视器把收集到的数据图视化了，以便能够更加直观的观察服务器的运行状况。我们可以看到，这里主要有进程、资源等待、数据文件I/O、最近耗费大量资源的查询4个条目。进程，主要记录相关的会话信息，例如可以观测到连接数据库
34、情况。例如这里我们看到主机名为PENGJUZHI-PC和HUYIHAO-PC的主机连接到数据库服务器因为我们知道从应用服务器APP连接到数据库的主机名是OA25_APP1或者OA25_APP2，这样我们就可以知道以上两个连接是客户用登陆名为OAtest的用户直接连到数据库的。当然这样的登陆事件也是被数据库日志记录下来，如果不成功的话。而实时跟踪监控，则可以检测到在跟踪阶段内独子连接到数据库的用户，及相关信息。在资源等待中，我们可以观察到BufferIO、CPU、Latch（锁）、Memory等重要的信息。我们观察这些数据来判断数据是否出现了异常，或者说数据的哪里出现了瓶颈。数据文件I/O，这里
35、的数据可以给我们现实数据读取，写入的速率是多少。可以看到OA数据库读取的速率是8.5M/s这充分说明了OA系统是一个读取量很大，但写入量很小的系统（相对而言）。这里我还要提醒大家，我们可以看到一个tempdb和master数据，这两个数据库是至关重要的数据，有兴趣的同事可以自己了解一下SQL的四个基本系统数据库。接下来，我们看到的是最近耗费大量资源的查询。这里显示了查询的语句、执行的频率、cpu耗时以及物理读写速率等信息。同时我们选中一条记录后，在对应的查询语句右击，可以弹出一个显示执行计划的菜单栏。选中之后，我们就可以进入查看执行计划的视图窗口。我们看到上面显示的提示说要我们建一个非聚集索引
36、（NONCLUSTEREDINDEX），再来看具体情况：索引查询开销占了8成。另外我们可以看到输出列表的格式OA3.dbo.OA_DEFINITION_DEPT_ROLE.Dept_ID这里的dbo就是我上文提到的架构，虽然我们在输出的时候只看到Dept_ID这样的字段，但是SQL实际处理的时候必须具体要特定的数据库、特定的架构。在下文的缓存机制中可以看到，我们在写语句的时候，指定架构名称，这样可以减少SQL自身去判定架构，改进SQL的性能。注意，以上我所提到的操作都是可以远程操作，而并非必须要本机登陆操作。当然必要的权限也是必需的。数据库日志查询在数据库的管理（Management）下有个数
37、据库日志（SQLServerLogs）可以看到日志列表，这些事系统自带的日志功能，记录发生错误的事件或对数据库重要的事件。我们双击一个打开看到：右边窗口显示了记录的事件、时间、错误类型、原因、说明等详细的信息。我们可以利用这些信息来判断在SQL出现异常的时候发生了什么事。尤其是利用它提供的ErrorID从SQL自带的帮助以及网络获取更多的信息以及相关的解决方法。除此之外，还有一些信息可取之道。譬如SQL的语句selecttop100*fromsys.messages这里提供的信息比较少甚至出现乱码字符（可以斟酌提取有用的信息个人意见）。当然，之上我所提到的有关操作或者语句是需要权限的，或者说你
38、所使用的用户必须拥有一个特定的架构，才能完成有关的操作。语句优化SQL的运行机制我们经常是写好查询SQL,然后调用程序执行SQL。但是它内部的工作流程是怎样的呢？先做哪一步，然后做哪一步等,我想还有大部分朋友和我一样都不一定清楚。第一步：应用程序把查询SQL语句发给服务器端执行。我们在数据层执行SQL语句时,应用程序会连接到相应的数据库服务器,把SQL语句发送给服务器处理。第二步：服务器解析请求的SQL语句。1:SQL计划缓存,经常用查询分析器的朋友大概都知道这样一个事实,往往一个查询语句在第一次运行的时候需要执行特别长的时间,但是如果你马上或者在一定时间内运行同样的语句,会在很短的时间内返回
39、查询结果。原因:1):服务器在接收到查询请求后,并不会马上去数据库查询,而是在数据库中的计划缓存中找是否有相对应的执行计划,如果存在,就直接调用已经编译好的执行计划,节省了执行计划的编译时间。2):如果所查询的行已经存在于数据缓冲存储区中,就不用查询物理文件了,而是从缓存中取数据,这样从内存中取数据就会比从硬盘上读取数据快很多,提高了查询效率.数据缓冲存储区会在后面提到。2:如果在SQL计划缓存中没有对应的执行计划,服务器首先会对用户请求的SQL语句进行语法效验,如果有语法错误,服务器会结束查询操作,并用返回相应的错误信息给调用它的应用程序。注意:此时返回的错误信息中,只会包含基本的语法错误信
40、息,例如select写成selec等,错误信息中如果包含一列表中本没有的列,此时服务器是不会检查出来的,因为只是语法验证,语义是否正确放在下一步进行。3:语法符合后,就开始验证它的语义是否正确,例如,表名,列名,存储过程等等数据库对象是否真正存在,如果发现有不存在的,就会报错给应用程序,同时结束查询。4:接下来就是获得对象的解析锁,我们在查询一个表时,首先服务器会对这个对象加锁,这是为了保证数据的统一性,如果不加锁,此时有数据插入,但因为没有加锁的原因,查询已经将这条记录读入,而有的插入会因为事务的失败会回滚,就会形成脏读的现象。5:接下来就是对数据库用户权限的验证,SQL语句语法,语义都正确
41、,此时并不一定能够得到查询结果,如果数据库用户没有相应的访问权限,服务器会报出权限不足的错误给应用程序,在稍大的项目中,往往一个项目里面会包含好几个数据库连接串,这些数据库用户具有不同的权限,有的是只读权限,有的是只写权限,有的是可读可写,根据不同的操作选取不同的用户来执行,稍微不注意,无论你的SQL语句写的多么完善,完美无缺都没用。6:解析的最后一步,就是确定最终的执行计划。当语法,语义,权限都验证后,服务器并不会马上给你返回结果,而是会针对你的SQL进行优化,选择不同的查询算法以最高效的形式返回给应用程序。例如在做表联合查询时,服务器会根据开销成本来最终决定采用hashjoin,merge
42、join,还是loopjoin,采用哪一个索引会更高效等等,不过它的自动化优化是有限的,要想写出高效的查询SQL还是要优化自己的SQL查询语句。当确定好执行计划后,就会把这个执行计划保存到SQL计划缓存中,下次在有相同的执行请求时,就直接从计划缓存中取,避免重新编译执行计划。第三步：语句执行。服务器对SQL语句解析完成后,服务器才会知道这条语句到底表态了什么意思,接下来才会真正的执行SQL语句。些时分两种情况:1):如果查询语句所包含的数据行已经读取到数据缓冲存储区的话,服务器会直接从数据缓冲存储区中读取数据返回给应用程序,避免了从物理文件中读取,提高查询速度。2):如果数据行没有在数据缓冲存
43、储区中,则会从物理文件中读取记录返回给应用程序,同时把数据行写入数据缓冲存储区中,供下次使用。说明:SQL缓存分好几种,这里有兴趣的朋友可以去搜索一下,有时因为缓存的存在,使得我们很难马上看出优化的结果,因为第二次执行因为有缓存的存在,会特别快速,所以一般都是先消除缓存,然后比较优化前后的性能表现,这里有几个常用的方法:DBCCDROPCLEANBUFFERS从缓冲池中删除所有清除缓冲区。DBCCFREEPROCCACHE从过程缓存中删除所有元素。DBCCFREESYSTEMCACHE从所有缓存中释放所有未使用的缓存条目。SQLServer2005数据库引擎会事先在后台清理未使用的缓存条目，以
44、使内存可用于当前条目。但是，可以使用此命令从所有缓存中手动删除未使用的条目。这只能基本消除SQL缓存的影响,目前好像没有完全消除缓存的方案,如果大家有,请指教。结论:只有知道了服务执行应用程序提交的SQL的操作流程才能很好的调试我们的应用程序。1:确保SQL语法正确;2:确保SQL语义上的正确性,即对象是否存在;3:数据库用户是否具有相应的访问权限。SQL的缓存机制如果说我比别人看得更远些,那是因为我站在了巨人的肩上.牛顿我的看法就是：如果说我比别人跑得更快些，那是因为我站在了计划缓存的肩膀上DMV动态管理视图DMF动态管理函数SQLSERVER可以避免编译之前执行的查询，使用4种机制使计划缓
45、存在许多情况下都可以访问：1、即席查询缓存2、自动参数化3、已准备查询，使用sp_executesql或通过API调用的prepare和execute方法4、存储过程或者其他编译对象（触发器和表值函数等）即席查询缓存：缓存的计划只有在后续批处理完全匹配时才能重用。仅限于完全文本匹配，在SQL中他的objtype为Adhoc注：SQL中的批：Select语句在各自的批处理中使用GO分隔。如果没有GO，则只有一个批处理，并且每个批都有自己的计划。对于即席查询计划的重用，整个批处理必须是相同的。自动参数化：对于某些查询，SQLSERVER可以决定将一个或者多个常量看做参数。将常量看做参数时，使用相同
46、基本模板的后续查询可以使用相同计划。已准备查询：objtype为Preparedsp_executesql:该过程从T-SQL批处理内部调用，位于即席缓存和存储过程中间。使用时需要确定参数及其数据类型，基本语法为sp_executesqlbatch_text,bath_parameter_definitions,param1,paramN具有相同值的batch_text和batch_parameter_definitions重复调用使用相同的缓存计划。Prepare和execute方法和sp_executesql类似，它由应用程序确定。不需要每次执行时都发送全文批处理。在预定义时会发送一次全文，返回可在执行时调用批处理的句柄。（这里我也不怎么理解，遇到的比较少。）已编译对象objtype为Proc存储过程和用户定义标量函数几乎是相同的，他们的编译计划被缓存，而且可以被重复使用。当已有的计划不能被使用时就需要重新编译，产生重新编译的原因可以分为两大类：与正确性相关的原因；与最优性相关的原因（这里不再介绍，有兴趣的同事可以查询相关资料）这样，我们知道了，要想使SQL运行的更快就需要有缓存计划，这样不需要SQLSERVER每次都重新编译产生计划。那么怎么样