SQLServer性能优化工具

SQLServer性能优化工具数据和工作负荷示例使用下例说明SQLServer性能工具的使用。首先创建下表。createtabletesttable(nkey1intidentity,col2char(300)default'abc',ckey1char(1))接下来，在这个表中填充10,000行测试数据。可以为列nkey1中所填充的数据创建非聚集索引。可以为列ckey1中的数据创建聚集索引，col2中的数据仅仅是填充内容，将每一行增加300字节。declare@counterintset@counter=1while(@counter=2000)begininserttesttable(ckey1)values('a')inserttesttable(ckey1)values('b')inserttesttable(ckey1)values('c')inserttesttable(ckey1)values('d')inserttesttable(ckey1)values('e')set@counter=@counter+1end数据库服务器将进行下面的两个查询：selectckey1,col2fromtesttablewhereckey1='a'selectnkey1,col2fromtesttablewherenkey1=5000ProfilerSQLServerProfiler记录数据库服务器中所发生活动的详细信息。可以配置Profiler以便用大量的可配置性能信息监视并记录在SQLServer中执行查询的一个或多个用户。可在Profiler中记录的性能信息有：I/O统计信息、CPU统计信息、锁定请求、T-SQL和RPC统计信息、索引和表扫描、警告和引发的错误、数据库对象的创建/除去、连接/断开、存储过程操作、游标操作等等。有关SQLProfiler可记录的全部信息，请在SQLServerBooksOnline中搜索字符串“Profiler”。将Profiler信息装载到.trc文件中以便用于IndexTuningWizard中Profiler和IndexTuningWizard是强大的工具组合，以帮助数据库管理员在表中创建适当的索引。Profiler将查询所消耗的资源记录在.trc文件中。.trc文件可以由IndexTuningWizard读取，IndexTuningWizard同时考虑.trc信息和数据库表，然后建议应创建什么样的索引。IndexTuningWizard可让管理员选择是自动创建数据库的适当索引，调度索引以便在以后自动创建还是产生一个可以在以后查看和执行的T-SQL脚本。以下是分析查询负荷的步骤：设置Profiler从SQLServerEnterpriseManager菜单中选择Tools/SQLServerProfiler启动Profiler。按CTRL+N组合键新建Profiler跟踪。键入此跟踪的名称。选择CapturetoFile:复选框，然后选择要将Profiler信息输出到其中的.trc文件。单击OK。运行工作负荷启动QueryAnalyzer（从SQLServerEnterpriseManager菜单中选择Tools/SQLServerQueryAnalyzer或者从开始菜单中选择开始\程序\MicrosoftSQLServer7.0\QueryAnalyzer）。连接到SQLServer并设定将在其中创建表的当前数据库。键入或复制以下查询并将它们粘贴到QueryAnalyzer的查询窗口：selectckey1,col2fromtesttablewhereckey1='a'selectnkey1,col2fromtesttablewherenkey1=5000按CTRL+E执行这两个查询。停止Profiler单击红色的正方形以停止Profiler跟踪。将.trc装载到IndexTuningWizard从Profiler菜单中选择Tools\IndexTuningWizardsU启动IndexTuningWizard。单击Next。选择要分析的数据库。单击Next。保持Ihaveasavedworkloadfile选项按钮被选，然后单击Next。选择Myworkloadfile选项按钮，找到用Profiler创建的.trc文件，然后单击Next。在SelectTablestoTune对话框中，选择需要进行分析的表，然后单击Next。IndexTuningWizard将在IndexRecommendations对话框中指出应创建的索引。单击Next。此向导可让您选择是立即创建索引，调度将在以后自动执行的索引创建任务还是创建带创建索引命令的T-SQL脚本。选择需要的选项，然后单击Next。单击Finish。IndexTuningWizard为示例数据库和工作负荷生成的T-SQL。/*Createdby:IndexTuningWizard*//*Date:9/7/98*//*Time:6:42:00PM*//*Server:HENRYLNT2*//*Database:test*//*Workloadfile:E:\mssql7\Binn\profiler_load.sql*/USE[test]BEGINTRANSACTIONCREATECLUSTEREDINDEX[testtable2]ON[dbo].[testtable]([ckey1])if(@@error0)rollbacktransactionCREATECLUSTEREDINDEX[testtable2]ON[dbo].[testtable]([ckey1])if(@@error0)rollbacktransactionCOMMITTRANSACTIONIndexTuningWizard为示例表和数据所建议的索引就是我们预期的索引。ckey1只有5个唯一值，且每一个值都有2,000行。假定其中的一个示例查询(selectckey1,col2fromtesttablewhereckey1='a')要求根据ckey1中的某个值对表进行检索，那么在ckey1列中创建聚集索引是有意义的。第二个查询(selectnkey1,col2fromtesttablewherenkey1=5000)根据列nkey1的值提取一行。Nkey1唯一，且有10,000行，因此在该列创建非聚集的索引是有意义的。Profiler/IndexTuningWizard组合在涉及许多表和许多查询的实际数据库服务器环境中功能非常强大。当数据库正在进行典型查询时，请使用Profiler记录.trc文件。然后将.trc文件装载到IndexTuningWizard，以确定是否创建了正确的索引。根据IndexTuningWizard中的提示自动生成并调度索引创建作业以便在非尖峰时刻运行。定期运行Profiler/IndexTuningWizard（比如每周）以查看数据库服务器中所执行的查询是否有较大改动，如果是，则可能需要不同的索引。定期使用Profiler/IndexTuningWizard有助于数据库管理员在查询工作负荷改变以及数据库大小随着时间而增加的同时，保持SQLServer以最佳状态运行。有关详细信息，请在SQLServerBooksOnline中搜索“IndexTuningWizard”和“IndexTuningRecommendations”。将Profiler信息加载到SQLServer表以进行分析Profiler提供的另一个选项是将信息记录在SQLServer表中。完成后，就可以查询整个表以确定是否有某些查询消耗了过多资源。将Profiler信息记录在SQLServer表中从SQLServerEnterpriseManager菜单中选择Tools/SQLServerProfiler启动Profiler。按CTRL+N组合键新建Profiler跟踪。键入跟踪的名称。单击CapturetoTable:复选框，然后选择要将Profiler信息输出到其中的SQLServer表。单击OK。结束后，单击红色的正方形停止Profiler跟踪。用QueryAnalyzer分析Profiler中记录的信息在将这些信息记录到SQLServer表中后，可以用QueryAnalyzer计算出系统中的哪些查询消耗资源最多。这样，数据库管理员就可以集中时间改进最需要帮助的查询。例如，通常用以下查询分析从Profiler记录到SQLServer表中的数据。此查询检索数据库服务器中消耗CPU资源最多的头3项。返回读和写I/O信息以及查询的持续时间（用毫秒计）。如果用Profiler记录了大量的信息，那么在这个表中创建索引以加快分析查询是有意义的。例如，如果CPU即将成为分析这个表的一个重要标准，那么在CPU列创建非聚集索引应该是一个不错的主意。selecttop3TextData,CPU,Reads,Writes,Durationfromprofiler_out_tableorderbycpudesc有关详细信息，请在SQLServerBooksOnline中搜索字符串“ViewingandAnalyzingTraces”、“TroubleshootingSQLServerProfiler”、“TipsforUsingSQLServer”、“CommonSQLServerProfilerScenarios”、“StartingSQLServerProfiler”和“MonitoringwithSQLServerProfiler”。QueryAnalyzerI/O统计信息QueryAnalyzer的ConnectionsOptions对话框General选项卡中提供了一个ShowstatsI/O选项。选择此复选框可以获取有关QueryAnalyzer中正在执行的查询所消耗I/O量的信息。例如，当选择ShowstatsI/O选项时，查询“selectckey1,col2fromtesttablewhereckey1='a'”除返回结果集以外，还返回以下I/O信息：Table'testtable'.Scancount1,logicalreads400,physicalreads382,read-aheadreads400.同样，当选择ShowstatsI/O选项时，查询“selectnkey1,col2fromtesttablewherenkey1=5000”除了返回结果集以外，还返回以下I/O信息：Table'testtable'.Scancount1,logicalreads400,physicalreads282,read-aheadreads400.使用I/O统计信息是一种监视查询调整效果的有效方法。例如，在此示例表中创建IndexTuningWizard在上面所推荐的两个索引，然后再次运行查询。在“selectckey1,col2fromtesttablewhereckey1='a'”的查询中，聚集索引改进性能的情况如下所示。假定查询需要提取该表的20%，则性能改进应该是比较合理的：Table'testtable'.Scancount1,logicalreads91,physicalreads5,read-aheadreads32.在“selectnkey1,col2fromtesttablewherenkey1=5000”的查询中，创建非聚集索引对于查询的性能有着很显著的影响。假定此查询只需要从10,000行的表中提取一行，那么用非聚集索引改善性能应该是比较合理的：Table'testtable'.Scancount1,logicalreads5,physicalreads0,read-aheadreads0.ShowPlan通过显示Queryoptimizer正在执行的任务的详细信息，使用ShowPlan可将注意力集中在有问题的SQL查询上。SQLServe