SQLServer性能调优.docx
- 文档编号:3371883
- 上传时间:2022-11-22
- 格式:DOCX
- 页数:20
- 大小:286.24KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLServer 性能