SQLServer性能调优.docx
《SQLServer性能调优.docx》由会员分享，可在线阅读，更多相关《SQLServer性能调优.docx（20页珍藏版）》请在冰豆网上搜索。
SQLServer性能调优
性能调优
概述
这个实验演示了一些重要的性能优化工具，例如Profiler，动态管理视图和数据库调节顾问。
准备步骤：
配置SQLProfiler
场景
本实验使用SidebySide虚拟机
为了后续的练习，你将使用SQLProfiler进行性能分析。
任务
详细步骤
使用服务器（使用VirtualPC）.
从开始菜单中启动VirtualPC。
如果控制台没有出现的话，查看系统托盘，双击MicrosoftVirtualPC启动。
找到本试验用到的虚拟机，点击Start。
当虚拟机启动后，点击“Action”菜单，选择“Ctrl+Alt+Del”（或者按键盘上的右Alt键和Del键），打开登录框。
以Administrator身份登录，密码为password01!
。
配置SQLProfiler
1.点击“Start”，选择“Programs|MicrosoftSQLServer2005|PerformanceTools|SQLServerProfiler”。
1.在“Profiler”窗口中，在主菜单中点击File，然后选择NewTrace。
2.在ConnecttoServer对话框中，在Servername文本框中确认连接的SQLServer2005实例名。
3.点击Connect。
4.在TraceProperties对话框中，输入exercise1_analysis（在后面的实验中将改变）。
5.在Usethetemplate下拉列表中，选择Standard（Default）。
6.选中Savetofile，选择E:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\LOG文件夹，输入跟踪文件的名称为exercise1_analysis，扩展名默认为.trc。
（跟踪文件的名称在以后的练习中会有不同）
7.在Setmaximumfilesize（MB）字段中输入100。
8.在TraceProperties对话框中，点击EventsSelection栏
注意:
实验中的所有联系都是用下列的设置
9.确认在Eventsselection中选中下列选项
ErrorsandWarnings:
HashWarnings
ExecutionWarnings
BlockedProcessReport
Locks:
Lock:
DeadlockChain
Lock:
Escalation
DeadlockGraph
Lock:
Deadlock
Performance:
PerformanceStatistics
TSQL:
SQL:
BatchStarting
PrepareSQL
UnprepareSQL
SQL:
BatchCompleted
10.点击Run。
额外准备步骤：
配置DatabaseTuningAdvisor
场景
在后续的项目中将使用DatabaseTuningAdvisor做性能分析。
在整个实验中将多次重复这个步骤。
注意
这个实验将配置DatabaseTuningAdvisor来生成调节建议。
任务
详细步骤
配置DatabaseTuningAdvisor
1.点击“Start”，选择“Programs|MicrosoftSQLServer2005|PerformanceTools|DatabaseTuningAdvisor”，将显示ConnecttoServer对话框。
2.在ConnecttoServer对话框中，在Servername文本框中确认连接的SQLServer2005实例名。
3.确认在Authentication下拉列表中选择的是WindowsAuthentication。
4.点击Connect。
5.在SessionName字段中输入exercise2_analysis。
后续实验中会话名称将递增。
6.在Workload类别中选择File选项，并选择创建名为Exercise1的跟踪文件。
7.在Databaseandtables列表中选择AdventureWorks。
8.确认选中了SaveTuningLog。
9.在顶级视图中选择TuningOptions栏。
10.清除LimitTuningTime复选框。
11.在PhysicalDesignStructures（PDS）touseindatabasesection中选择indexes。
12.在PartitioningStrategytoemploy选项节中选择NoPartitioning。
13.在PhysicalDesignStructures（PDS）tokeepindatabase节中，选择DonotkeepanyExistingPDS。
14.在工具栏上点击绿色的三角形图标运行分析。
15.在Recommendation列中将显示结果。
为了实现这些建议，在相应的项上点击Definition列。
在弹出窗口中将显示SQLServer生成的实现脚本。
练习1：
创建表和视图
场景
这个练习中将创建需要使用的数据
任务
详细步骤
创建连接
1.点击“Start”，选择“Programs|MicrosoftSQLServer2005|SQLServerManagementStudio”，将显示ConnecttoServer对话框。
2.在ConnecttoServer对话框中，在Servername文本框中确认连接的SQLServer2005实例名。
3.确认在Authentication下拉列表中选择的是WindowsAuthentication。
4.点击Connect。
5.在工具栏中点击NewQuery，使用WindowsAuthentication连接到当前实例。
6.在SQLEditor工具栏上选择Adventureworks。
7.重复3次步骤5-6。
当完成时，将有4个SQLEditor会话。
创建自定义管理视图，这个视图将通过一些动态管理视图抽取一些性能瓶颈的数据。
8.在第4个会话中，执行附录1中的代码创建自定义的管理视图。
创建lab_table1表，并填充10000行数据
9.在第4个会话中，执行附录2中的代码创建lab_table1。
创建lab_table2表，并填充10000行数据
10.在第4个会话中，执行附录3中的代码创建lab_table2。
练习2：
死锁
场景
在这个练习中，你将创建一个数据库的死锁并使用SQLProfiler和自定义视图分析它。
任务
详细步骤
跟踪死锁
1.使用SQLProfiler开始跟踪并将结果保存为exercise2_analysis.trc。
（更多信息请参考准备步骤）。
执行命令生成死锁。
直到提示执行命令前，不要运行这些查询。
2.在第1个会话中，输入下列语句
BEGINTRAN
UPDATEdbo.lab_table2
SETcol2=col2+'km'
col3=col4+'g'
col4=col2+'c'
WAITFORdelay'00:
00:
20'
UPDATEdbo.lab_table1
SETcol2=col2+'km'
col3=col4+'g'
col4=col2+'c'
COMMITTRAN
3.在第2个会话中，输入下列语句
BEGINTRAN
UPDATEdbo.lab_table1
setcol2=col2+'km'
col3=col4+'g'
col4=col2+'c'
WAITFORdelay'00:
00:
20'
UPDATEdbo.lab_table2
setcol2=col2+'km'
col3=col4+'g'
col4=col2+'c'
COMMITTRAN
4.在第3个会话中，输入下列语句
Select*Fromvw_process_waiting_for_resourcesorderbywait_duration_ms
执行命令，并捕获死锁信息
5.同时执行第1个和第2个会话中的命令。
执行第3个窗口中的代码。
每隔5秒执行1次第3个窗口的代码。
将捕获的结果粘贴到文本编辑器或excel中。
结果如附录3
6.在第1个和第2个窗口执行完成后，在SQLProfiler中点击红色的方形按钮，停止跟踪。
在SQLProfiler下面的视图中最大化DeadlockGraph图形。
结果如附录3
清理
7.在所有的SQLEditor中清除所有的代码。
练习3：
性能分析和调节
场景
在这个练习中你将使用SQLProfiler，自定义视图和DatabaseTuningAdvisor来进行性能分析。
任务
详细步骤
设置SQLProfiler跟踪操作
1.使用SQLProfiler开始跟踪并将结果保存为exercise3_analysis.trc。
（更多信息请参考准备步骤）。
填充数据
2.在第1个SQLEditor窗口中，执行下列代码，创建lab_table1
USE[AdventureWorks]
GO
IFOBJECT_ID（'dbo.lab_table1'）ISNOTNULL
BEGIN
DROPTABLElab_table1
END
GO
CREATETABLEdbo.lab_table1
（
col1INTIDENTITY（1,1）PRIMARYKEYCLUSTEREDWITHFILLFACTOR=90,
col2VARCHAR（10）NOTNULLDEFAULT'Harry',
col3VARCHAR（10）NOTNULLDEFAULT'Brenda',
col4VARCHAR（10）NOTNULLDEFAULT'Larry'
）
GO
3.在第1个SQLEditor窗口，执行下列代码lab_table2
USE[AdventureWorks]
GO
IFOBJECT_ID（'dbo.lab_table2'）ISNOTNULL
BEGIN
DROPTABLEdbo.lab_table2
END
GO
CREATETABLEdbo.lab_table2
（
col1INTIDENTITY（1,1）PRIMARYKEYCLUSTEREDWITHFILLFACTOR=90,
col2VARCHAR（10）NOTNULLDEFAULT'Harry',
col3VARCHAR（10）NOTNULLDEFAULT'Brenda',
col4VARCHAR（10）NOTNULLDEFAULT'Larry'
）
GO
CREATENONCLUSTEREDINDEXINC_col2ONdbo.lab_table2（col2）WITH（FILLFACTOR=90）
GO
CREATENONCLUSTEREDINDEXINC_col3ONdbo.lab_table2（col3）WITH（FILLFACTOR=90）
GO
CREATENONCLUSTEREDINDEXINC_col4ONdbo.lab_table2（col4）WITH（FILLFACTOR=90）
GO
4.清除第2个SQLEditor窗口的代码。
复制并执行下列代码，向表lab_table1中添加10000行数据并注意时间。
SETNOCOUNTON
DECLARE@l_countBIGINT
SELECT@l_count=1
WHILE（@l_count<=10000）
BEGIN
INSERTINTOlab_table1
DEFAULTVALUES
SELECT@l_count=@l_count+1
END
SETNOCOUNTOFF
GO
观察性能问题。
在插入数据的同时，通过自定义视图计算延时，会发现有过度的上下文切换和过多的I/O。
5.在第3个窗口中执行下列代码
SELECT*FROMvm_process_waiting_for_resourcesORDERBYwt.wait_duration_ms
结果如附录4
在不同的会话中同时插入并更新lab_table2
6.在第2个SQLEditor窗口中移出原有代码并运行下列代码
SELECTGETDATE（）
SETNOCOUNTON
DECLARE@l_countBIGINT
SELECT@l_count=1
SETIDENTITY_INSERTdbo.lab_table2ON
WHILE（@l_count<=1000000）
BEGIN
INSERTINTOdbo.lab_table2（col1,col2,col3,col4）
SELECTcol1,col2,col3,col4
FROMdbo.lab_table1
WHEREcol1BETWEEN@l_countAND@l_count+1000
ANDdbo.lab_table1.col1NOTIN（SELECTcol1FROMdbo.lab_table2）
SELECT@l_count=@l_count+1000
END
SELECTCOUNT（*）rows_insertedFROMdbo.lab_table2
SETIDENTITY_INSERTdbo.lab_table2OFF
SETNOCOUNTOFF
GO
SELECTGETDATE（）
GO
7.在第4个SQLEditor窗口中删除原有代码，并执行下列代码
USE[AdventureWorks]
GO
SELECTGETDATE（）
UPDATEdbo.lab_table2
SETcol2=col2+'km'
col3=col4+'g'
col4=col2+'c'
SELECTGETDATE（）
GO
使用自定义视图显示性能问题。
与之前对lab_table1的操作相比，这个操作更快。
8.在第3个窗口中执行下列代码
SELECT*FROMvm_process_waiting_for_resourcesORDERBYwt.wait_duration_ms
9.一旦所有的操作完成，停止跟踪。
使用DatabaseTuningAdvisor分析跟踪文件。
10.使用DatabaseTuningAdvisor分析跟踪并查看给出的优化建议。
（关于DatabaseTuningAdvisor的使用方法请查看额外准备步骤）。
11.注意DatabaseTuningAdvisor给出的建议删除了一些索引。
重新创建lab_table2，不添加非聚簇索引。
12.在第1个SQLEditor窗口中执行下列代码。
USE[AdventureWorks]
GO
IFOBJECT_ID（'dbo.lab_table2'）ISNOTNULL
BEGIN
DROPTABLEdbo.lab_table2
END
GO
CREATETABLEdbo.lab_table2
（
col1INTIDENTITY（1,1）PRIMARYKEYCLUSTEREDWITHFILLFACTOR=90,
col2VARCHAR（10）NOTNULLDEFAULT'Harry',
col3VARCHAR（10）NOTNULLDEFAULT'Brenda',
col4VARCHAR（10）NOTNULLDEFAULT'Larry'
）
GO
在从lab_table1向lab_table2插入数据，同时在其他窗口中更新lab_table2。
注意操作时间。
13.在第2个SQLEditor窗口中执行select/insert操作
SELECTGETDATE（）
SETNOCOUNTON
DECLARE@l_countBIGINT
SELECT@l_count=1
SETIDENTITY_INSERTdbo.lab_table2ON
WHILE（@l_count<=1000000）
BEGIN
INSERTINTOdbo.lab_table2（col1,col2,col3,col4）
SELECTcol1,col2,col3,col4
FROMdbo.lab_table1
WHEREcol1BETWEEN@l_countAND@l_count+1000
ANDdbo.lab_table1.col1NOTIN（SELECTcol1FROMdbo.lab_table2）
SELECT@l_count=@l_count+1000
END
SELECTCOUNT（*）rows_insertedFROMdbo.lab_table2
SETIDENTITY_INSERTdbo.lab_table2OFF
SETNOCOUNTOFF
GO
SELECTGETDATE（）
GO
14.在第4个窗口中执行更新操作
USE[AdventureWorks]
GO
SELECTGETDATE（）
UPDATEdbo.lab_table2
SETcol2=col2+'km'
col3=col4+'g'
col4=col2+'c'
SELECTGETDATE（）
GO
15.同时在第3个SQLEditor窗口中执行下述语句，分析执行的性能和瓶颈
SELECT*FROMvm_process_waiting_for_resourcesORDERBYwt.wait_duration_ms
注意在2种情况下的性能提升。
16.关闭DatabaseTuningAdvisor,SQLServerProfiler和SQLServerManagementStudio。
练习4：
分析性能数据
场景
在这个练习中，你可以在SQLServerProfiler中合并从PerformanceMonitor中收集的数据。
任务
详细步骤
配置SQLServerProfiler查看WindowsPerformanceMonitor数据。
这是我们可以分析哪些是比较耗费性能的查询，并发现瓶颈。
1.点击“Start”，选择“Programs|MicrosoftSQLServer2005|PerformanceTools|SQLServerProfiler”。
将跟踪文件保存为demo_analysis.trc。
2.打开PerformanceMonitor。
右键单击CounterLogs并选择NewLogSettings，适当命名并Addcounter：
Processor-%ProcessorTime[_Total]和SQLAgent:
Statistics-SQLServerRestarted。
然后配置计数器日志保存日志文件。
3.允许Trace和Profiler并行运行几分钟，执行练习4中的一些脚本。
4.冻结WindowsPerformanceMonitorDisplay。
停止counterlog。
停止SQLProfiler。
5.关闭WindowsPerformanceMonitor和SQLProfiler。
6.打开SQLProfiler的跟踪文件demo_analysis.trc
7.在file菜单中，点击ImportPerformanceData
8.打开保存的PerformanceMonitor计数器日志。
将显示PerformanceCounterLimitDialog。
选中所有的计数器，然后点击OK.
9.在Trace中点击任何一行，将同时显示计数器中的值。
附录1：
创建自定义管理视图
USE[AdventureWorks]
GO
IFOBJECT_ID（'dbo.vw_process_waiting_for_resources'）ISNOTNULL
BEGIN
DROPVIEWdbo.vw_process_waiting_for_resources
END
GO
CREATEVIEWdbo.vw_process_waiting_for_resources
AS
SELECTwt.session_id
wt.wait_duration_ms
wt.wait_type
e.blockedblocked_by
t.kernel_time
t.usermode_time
w.state
wt.blocking_task_address
wt.resource_description
w.affinity
e.cpu
e.physical_io
e.memusage
w.context_switch_count
w.pending_io_count
d.Total_Reads
d.Total_Writes
w.is_fiber
FROM
sys.dm_os_waiting_taskswt
INNERJOIN
sys.dm_os_workersw
ON（wt.waiting_task_address=w.task_address）
INNERJOIN
sys.dm_os_threadst
ON（t.worker_address=w.worker_address
ANDt.scheduler_address=w.scheduler_address
ANDt.thread_address=w.thread_address）
INNERJOIN
（SELECTsession_id,SUM（num_reads）total_reads,SUM（num_write
升级会员 