SQLServer2005性能调优58992

1、性能调优概述这个实验演示了一些重要的性能优化工具，例如Profiler，动态管理视图和数据库调节顾问。准备步骤：配置 SQL Profiler场景本实验使用SidebySide虚拟机为了后续的练习，你将使用SQL Profiler进行性能分析。任务详细步骤使用服务器（使用Virtual从开始菜单中启动Virtual PC。如果控制台没有岀现的话，查看系统PC ).托盘，双击 Microsoft Virtual PC 启动。 找到本试验用到的虚拟机，点击Start。当虚拟机启动后，点击Action ”菜单，选择 Ctrl+Alt+Del ”（或者按键盘上的右 Alt键和Del键），打开登录框。以

2、 Administrator身份登录，密码为 password。配置 SQL Profiler1.点击 Start ，选择 Programs|Microsoft SQL Server2005|Performanee Tools|SQL Server Profiler”。2.在 Profiler 囱口中，在主采单中点击 File，然后选择 New Trace。3.在Connect to Server对话框中，在 Server name 文本框中确认连接的SQL Server 2005实例名。4.点击 Connect。5.在 Trace Properties 对话框中，输入 exercise1_a

3、nalysis（在后面的实验中将改变）。6.在 Use the template 下拉列表中，选择 Standard(Default) 。7.选中 Save to file ，选择 E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG文件夹，输入跟踪文件的名称为exercisel analysis ，扩展名默认为.trc。（跟踪文件的名称在以后 的练习中会有不同）8.在 Set maximum file size(MB)字段中输入 100。9.在 Trace Properties对话框中，点击Events Selection栏注意：实验中的所有联

4、系都是用下列的设置10.确认在Events selection中选中下列选项Errors and Warnin gs:Hash WarningsExecuti on WarningsBlocked Process ReportLocks:Lock: Deadlock ChainLock: Escalati on Deadlock Graph Lock: DeadlockPerforma nee:Performa nee StatisticsTSQL:SQL: Batch Start ing PrepareSQLUn prepareSQLSQL: BatchCompleted11.点击 Run。

5、额外准备步骤：配置 Database Tuning Advisor场景在后续的项目中将使用 Database Tuning Advisor做性能分析。在整个实验中将多次重复这个步骤。这个实验将配置 Database Tuning Advisor来生成调节建议。任务详细步骤配置 Database Tuning Advisor1.点击 Start ，选择 Programs|Microsoft SQL Server 2005|Performa nee Tools|Database Tuning Advisor Co nn ect to Server对话框。将显示2.在Connect to Serve

6、r对话框中，在连接的SQL Server 2005实例名。Server n ame文本框中确认3.确认在Authentication下拉列表中选择的是WindowsAuthe nticati on。4.点击 Connect。5.在 Session Name 字段中输入 exercise2_analysis 。话名称将递增。-后续实验中会6.在Workload类别中选择File选项, 的跟踪文件。并选择创建名为Exercise17.在Database and tables 列表中选择Adve ntureWorks 。8.确认选中了 Save Tu ning Log 。9.在顶级视图中选择Tuni

7、ng Optio ns栏。10.清除 Limit Tuning Time复选框。11.在 Physical Design Structures(PDS) to 中选择indexes。use in database secti on12.在 Partitioning Strategy to employ Partiti oning。选项节中选择No13.在 Physical Design Structures(PDS) to keep in database节中，选择 Do not keep any Existing PDS。14. 在工具栏上点击绿色的三角形图标运行分析。15. 在Recomm

8、endation列中将显示结果。为了实现这些建议，在相应的项上点击 Definition 列。在弹岀囱口中将显示SQL Server生成的实现脚本。练习1 :创建表和视图场景这个练习中将创建需要使用的数据任务详细步骤创建连接1.点击 Start ，选择 Programs|Microsoft SQL Server 2005|SQLServer Man ageme nt Studio 框。”，将显示 Connect to Server 对话2.在Connect to Server对话框中，在 Server name 文本框中确认连接的SQL Server 2005实例名。3.确认在Authenti

9、cation下拉列表中选择的是WindowsAuthe nticati on 。4.点击 Connect。5.在工具栏中点击 New Query 接到当前实例。，使用 Windows Authentication连6.在SQL Editor工具栏上选择Adve ntureworks 。7.重复3次步骤5-6。当完成时，将有 4个SQL Editor会话。创建自定义管理视图，这个 视图将通过一些动态管理视 图抽取一些性能瓶颈的数8.在第4个会话中，执行附录1中的代码创建自定义的管理视图。据。创建 Iab_table1表，并填充9.在第4个会话中，执行附录2中的代码创建 lab_table1 。1

10、0000行数据创建 lab_table2表，并填充10.在第4个会话中，执行附录3中的代码创建 lab_table2。10000行数据练习2 :死锁场景在这个练习中，你将创建一个数据库的死锁并使用SQL Profiler和自定义视图分析它。任务详细步骤跟踪死锁1.使用SQL Profiler开始跟踪并将结果保存为exercise2 analysis.trc。（更多信息请参考准备步骤）。执行命令生成死锁。直到提 示执行命令前，不要运行这 些查询。2.在第1个会话中，输入下列语句BEGIN TRANUPDATE dbo.lab_table2SET col2 = col2 + km,col3 = c

11、ol4 + g,col4 = col2 + cWAITFOR delay 00:00:20UPDATE dbo.lab_table1SET col2 = col2 + km,col3 = col4 + g,col4 = col2 + cCOMMIT TRAN3.在第2个会话中，输入下列语句BEGIN TRANUPDATE dbo.lab_table1set col2 = col2 + km,col3 = col4 + g,col4 = col2 + cWAITFOR delay 00:00:20UPDATE dbo.lab_table2set col2 = col2 + km,col3 = c

12、ol4 + g,col4 = col2 + cCOMMIT TRAN4.在第3个会话中，输入下列语句Select * From vw_process_wait in g_for_resources order by wait_durati on_ms执行命令，并捕获死锁信息5.同时执行第1个和第2个会话中的命令。执行第 3个窗口中的代 码。每隔5秒执行1次第3个窗口的代码。将捕获的结果粘贴到 文本编辑器或excel中。结果如附录36.在第1个和第2个窗口执行完成后，在SQL Profiler中点击红色的方形按钮，停止跟踪。在SQL Profiler下面的视图中最大化Deadlock Graph

13、 图形。结果如附录3清理7.在所有的SQL Editor中清除所有的代码。练习3 :性能分析和调节场景在这个练习中你将使用SQL Profiler,自定义视图和 Database Tuning Advisor来进行性能分析。任务详细步骤设置SQL Profiler跟踪操作1.使用SQL Profiler开始跟踪并将结果保存为exercise3 analysis.trc。（更多信息请参考准备步骤）。填充数据2.在第1个SQL Editor窗口中，执行下列代码，创建lab_table1USE Adve ntureWorksGOIF OBJECT_ID ( dbo.lab_table1) IS NO

14、TNULLBEGINDROP TABLE lab table1ENDGOCREATE TABLE dbo.lab_table1(coll INT IDENTITY(1,1) PRIMARYKEY CLUSTERED WITH FILLFACTOR = 90,col2 VARCHAR(10) NOT NULL DEFAULTHarry,col3 VARCHAR(10) NOT NULL DEFAULTBre nda,col4 VARCHAR(10) NOT NULL DEFAULTLarry)GO3.在第1个SQL Editor窗口，执行下列代码lab_table2USE Adve ntureW

15、orksGOIF OBJECT_ID ( dbo.lab_table2) IS NOTNULLBEGINDROP TABLE dbo.lab_table2ENDGOCREATE TABLE dbo.lab_table2(col1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED WITH FILLFACTOR = 90,col2 VARCHAR (10) NOT NULL DEFAULT Harry,col3 VARCHAR (10) NOT NULL DEFAULTBre nda,col4 VARCHAR (10) NOT NULL DEFAULTLarry)G

16、OCREATE NONCLUSTERED INDEX INC_col2 ON dbo.lab_table2 (col2) WITH ( FILLFACTOR =90)GOCREATE NONCLUSTERED INDEX INC_col3 ON dbo.lab_table2 (col3) WITH ( FILLFACTOR =90)GOCREATE NONCLUSTERED INDEX INC_col4 ON dbo.lab_table2 (col4) WITH ( FILLFACTOR =90)GO4.清除第2个SQL Editor窗口的代码。复制并执行下列代码，向表 Iab_table1中

17、添加10000行数据并注意时间。SET NOCOUNT ONDECLARE l_cou nt BIGINTSELECT l_cou nt = 1WHILE (l_cou nt = 10000)BEGININSERT INTO lab_table1DEFAULT VALUESSELECT l_cou nt = l_cou nt + 1ENDSET NOCOUNT OFFGO观察性能问题。在插入数据 的同时，通过自定义视图计 算延时，会发现有过度的上 下文切换和过多的I/O。5.在第3个窗口中执行下列代码SELECT * FROM vm_process_wait in g_for_resource

18、s ORDER BY wt.wait_duratio n_ms结果如附录4在不同的会话中同时插入并更新 Iab_table26.在第2个SQL Editor窗口中移岀原有代码并运行下列代码SELECT GETDATE()SET NOCOUNT ONDECLARE l_cou nt BIGINTSELECT l_cou nt = 1SET IDENTITY_INSERT dbo.lab_table2 ONWHILE (l_cou nt = 1000000)BEGININSERT INTO dbo.lab_table2 ( col1, col2, col3, col4 )SELECT col1,

19、col2, col3, col4FROM dbo.lab_table1WHERE col1 BETWEEN l_cou nt AND l_cou nt + 1000AND dbo.lab_table1.col1 NOT IN(SELECT col1 FROM dbo.lab_table2 )SELECT l_cou nt = l_cou nt + 1000 ENDSELECT COUNT( * ) rows_i nserted FROM dbo.lab_table2SET IDENTITY_INSERT dbo.lab_table2 OFFSET NOCOUNT OFFGOSELECT GET

20、DATE()GO7.在第4个SQL Editor窗口中删除原有代码，并执行下列代码USE Adve ntureWorksGOSELECT GETDATE()UPDATE dbo.lab_table2SET col2 = col2 + km,col3 = col4 + g,col4 = col2 + cSELECT GETDATE()GO使用自定义视图显示性能问题。和之前对 Iab_table1 的 操作相比，这个操作更快。8.在第3个窗口中执行下列代码SELECT * FROM vm_process_wait in g_for_resources ORDER BY wt.wait_durati

21、o n_ms9.一旦所有的操作完成，停止跟踪。使用 Database Tuning Advisor分析跟踪文件。10. 使用Database Tuning Advisor分析跟踪并查看给岀的优化建议。（关于Database Tuning Advisor的使用方法请查看额外准备步骤）。11. 注意Database Tuning Advisor给岀的建议删除了一些索引。重新创建Iab_table2，不添 加非聚簇索引。12.在第1个SQL Editor窗口中执行下列代码。USE Adve ntureWorksGOIF OBJECT_ID ( dbo.lab_table2) IS NOTNULLBE

22、GINDROP TABLE dbo.lab_table2ENDGOCREATE TABLE dbo.lab_table2(col1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED WITH FILLFACTOR = 90,col2 VARCHAR (10) NOT NULL DEFAULT Harry,col3 VARCHAR (10) NOT NULL DEFAULTBre nda,col4 VARCHAR (10) NOT NULL DEFAULTLarry)GO在从 Iab_table1 向Iab_table2 插入数据，同时 在其他窗口中更新lab_ta

23、ble2。注意操作时间。13.在第2个SQL Editor窗口中执行 select/insert操作SELECT GETDATE()SET NOCOUNT ONDFCIARF l count BIGINTSELECT l_cou nt = 1SET IDENTITY_INSERTdbo.lab_table2ONWHILE (l_cou nt = 1000000)BEGININSERTINTO dbo.lab_table2( col1,col2, col3, col4 )SELECT coll, col2, col3, col4FROM dbo.lab_table1WHERE coll BETW

24、EEN l_cou nt ANDl_cou nt + 1000AND dbo.lab_table1.col1 NOTIN ( SELECT col1 FROM dbo.lab_table2)SELECT l_cou nt = l_cou nt + 1000ENDSELECT COUNT( * ) rows_i nserted FROM dbo.lab_table2SET IDENTITY_INSERT dbo.lab_table2OFFSET NOCOUNT OFFGOSELECT GETDATE()GO14. 在第4个窗口中执行更新操作USE Adve ntureWorksGOSELECT

25、GETDATE()UPDATE dbo.lab_table2SET col2 = col2 + km,col3 = col4 + g,col4 = col2 + cSELECT GETDATE()GO15. 同时在第3个SQL Editor窗口中执行下述语句，分析执行的性能 和瓶颈SELECT * FROM vm_process_wait in g_for_resources ORDER BY wt.wait_duratio n_ms注意在2种情况下的性能提升。16. 关闭 Database Tuning Advisor, SQL Server Profiler和 SQL ServerMan

26、ageme nt Studio 。练习4 :分析性能数据场景在这个练习中，你可以在SQL Server Profiler中合并从 Performanee Monitor中收集的数据。任务详细步骤配置 SQL Server Profiler 查 看 Windows Performanee Monitor数据。这是我们可以 分析哪些是比较耗费性能的 查询，并发现瓶颈。1.点击 Start ”，选择 Programs|Microsoft SQL Server2005|Performanee Tools|SQL Server Profiler”。将跟踪文件保存为 demo_analysis.trc 。

27、2.打开 Performanee Monitor。右键单击 Counter Logs 并选择 NewLog Settings，适当命名并 Add counter : Processor-%Processor Time_Total 和 SQLAgent:Statistics-SQL Server Restarted 。然后 配置l计数器日志保存日志文件。3.允许Trace和Profiler并行运行几分钟，执行练习4中的一些脚本。4.冻结 Windows Performanee Monitor Display 。停止 counter log 。 停止 SQL Profiler。5.关闭 Windo

28、ws Performanee Monitor和 SQL Profiler。6.打开 SQL Profiler 的跟踪文件 demo_analysis.trc7.在 file 菜单中，点击 Import Performanee Data8.打开保存的 Performa nee Mo nitor计数器日志。将显示Performa nee Cou nter Limit Dialog。选中所有的计数器，然后点击0K.9.在Trace中点击任何一行，将同时显示计数器中的值。附录1 ：创建自定义管理视图USE Adve ntureWorksGOIF OBJECT_ID ( dbo.vw_process_w

29、ait in g_for_resources ) IS NOT NULLBEGINDROP VIEW dbo.vw_process_wait in g_for_resourcesENDGOCREATE VIEW dbo.vw_process_wait in g_for_resourcesASSELECT wt.session_id,wt.wait_duratio n_ms,wt.wait_type,e.blocked blocked_by,t.ker nel_time,t.usermode_time,w.state,wt.block in g_task_address,wt.resource_

30、descripti on,w.affi nity,e.cpu,e.physicalo,e.memusage,w.c on text_switch_cou nt,w.pe ndin g_io_cou nt,d.Total_Reads,d.Total_Writes,w.is_fiberFROMsys.dm_os_wait in g_taskswtINNER JOINsys.dm_os_workerswON ( wt.waiting_task_address = w.task_address )INNER JOINsys.dm_os_threads tON ( t.worker_address= w

31、.worker_addressAND t.scheduler_address = w.scheduler_addressAND t.thread_address = w.thread_address )INNER JOIN( SELECT session_id, SUM( num_reads ) total_reads, SUM( num_writes ) total_writes FROM sys.dm_exec_c onn ecti onsGROUP BY session_id ) dON ( wt.session_id = d.session_id )INNER JOIN( SELECT

32、 spid, blocked, cpu, physical_io,memusage FROM sys.sysprocesses ) eON ( e.spid = wt.session_id )GO附录2 :创建数据表1USE Adve ntureWorksGOIF OBJECT_ID ( dbo.lab_table1 ) IS NOT NULLBEGINDROP TABLE lab_table1ENDGOCREATE TABLE dbo.lab_table1(coll INT IDENTITY(1,1) PRIMARY KEY CLUSTERED WITH FILLFACTOR = 90, c

33、ol2 VARCHAR (10) NOT NULL DEFAULT Harry,col3 VARCHAR (10) NOT NULL DEFAULT Bre nda,col4 VARCHAR (10) NOT NULL DEFAULT Larry)GOSET NOCOUNT ONDECLARE l_cou nt BIGINTSELECT l_cou nt = 1WHILE (l_cou nt = 10000)BEGININSERT INTO Iab_table1DEFAULT VALUESSELECT l_cou nt = l_cou nt + 1ENDSET NOCOUNT OFFGO附录3

34、 :创建数据表2USE Adve ntureWorksGOIF OBJECT_ID ( dbo.lab_table2 ) IS NOT NULLBEGINDROP TABLE dbo.lab_table2ENDGOCREATE TABLE dbo.lab_table2(col1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED WITH FILLFACTOR = 90,col2 VARCHAR (10) NOT NULL DEFAULT Harry,col3 VARCHAR (10) NOT NULL DEFAULT Bre nda,col4 VARCHAR (10) NOT NULL DEFAULT Larry)GOCREATE NONCLUSTERED INDEX INC_col2 ON dbo.la