001
| --WL 09-07-03
|
002
| /*Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返回的服务器状态信息可用于监控服务器实例的运行状况、诊断问题和优化性能。
|
003
|
004
| 常规服务器动态管理对象包括:
|
005
|
006
| dm_db_*:数据库和数据库对象
|
007
|
008
| dm_exec_*:执行用户代码和关联的连接
|
009
|
010
| dm_os_*:内存、锁定和时间安排
|
011
|
012
| dm_tran_*:事务和隔离
|
013
|
014
| dm_io_*:网络和磁盘的输入/输出
|
015
|
016
| 此部分介绍为监控 SQL Server 运行状况而针对这些动态管理视图和函数运行的一些常用查询。
|
017
| */
|
018
|
019
| --您可以运行以下查询来获取所有 DMV 和 DMF 名称
|
020
| SELECT
*
FROM
sys.system_objects
|
021
| WHERE
name
LIKE
'dm_%'
|
022
| ORDER
BY
name
|
023
|
024
| --CPU 瓶颈通常由以下原因引起:查询计划并非最优、配置不当、设计因素不良或硬件资源不足。下面的常用查询可帮助您确定导致 CPU 瓶颈的原因。
|
025
|
026
| --下面的查询使您能够深入了解当前缓存的哪些批处理或过程占用了大部分 CPU 资源。
|
027
|
028
| SELECT
TOP
50
|
029
|
SUM
(qs.total_worker_time)
AS
total_cpu_time,
|
030
|
SUM
(qs.execution_count)
AS
total_execution_count,
|
031
|
COUNT
(*)
AS
number_of_statements,
|
032
|
qs.sql_handle
|
033
| FROM
sys.dm_exec_query_stats
AS
qs
|
034
| GROUP
BY
qs.sql_handle
|
035
| ORDER
BY
SUM
(qs.total_worker_time)
DESC
|
036
|
037
| --下面的查询显示缓存计划所占用的 CPU 总使用率(带 SQL 文本)。
|
038
| SELECT
|
039
|
total_cpu_time,
|
040
|
total_execution_count,
|
041
|
number_of_statements,
|
042
|
s2.text
|
043
|
--(SELECT
SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN
statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) *
2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS
query_text
|
044
| FROM
|
045
|
(
SELECT
TOP
50
|
046
|
SUM
(qs.total_worker_time)
AS
total_cpu_time,
|
047
|
SUM
(qs.execution_count)
AS
total_execution_count,
|
048
|
COUNT
(*)
AS
number_of_statements,
|
049
|
qs.sql_handle
--,
|
050
|
--MIN(statement_start_offset) AS statement_start_offset,
|
051
|
--MAX(statement_end_offset) AS statement_end_offset
|
052
|
FROM
|
053
|
sys.dm_exec_query_stats
AS
qs
|
054
|
GROUP
BY
qs.sql_handle
|
055
|
ORDER
BY
SUM
(qs.total_worker_time)
DESC
)
AS
stats
|
056
|
CROSS
APPLY sys.dm_exec_sql_text(stats.sql_handle)
AS
s2
|
057
|
058
| --下面的查询显示 CPU 平均占用率最高的前 50 个 SQL 语句。
|
059
|
060
| SELECT
TOP
50
|
061
| total_worker_time/execution_count
AS
[
Avg
CPU
Time
],
|
062
| (
SELECT
SUBSTRING
(text,statement_start_offset/2,(
CASE
WHEN
statement_end_offset = -1
then
LEN(
CONVERT
(nvarchar(
max
), text)) * 2
ELSE
statement_end_offset
end
-statement_start_offset)/2)
FROM
sys.dm_exec_sql_text(sql_handle))
AS
query_text, *
|
063
| FROM
sys.dm_exec_query_stats
|
064
| ORDER
BY
[
Avg
CPU
Time
]
DESC
|
065
|
066
| --下面显示用于找出过多编译/重新编译的 DMV 查询
|
067
|
068
| select
*
from
sys.dm_exec_query_optimizer_info
|
069
| where
|
070
|
counter =
'optimizations'
|
071
|
or
counter =
'elapsed time'
|
072
|
073
|
074
|
075
|
076
| --下面的示例查询显示已重新编译的前 25 个存储过程。plan_generation_num 指示该查询已重新编译的次数。
|
077
|
078
| select
top
25
|
079
|
sql_text.text,
|
080
|
sql_handle,
|
081
|
plan_generation_num,
|
082
|
execution_count,
|
083
|
dbid,
|
084
|
objectid
|
085
| from
sys.dm_exec_query_stats a
|
086
|
cross
apply sys.dm_exec_sql_text(sql_handle)
as
sql_text
|
087
| where
plan_generation_num > 1
|
088
| order
by
plan_generation_num
desc
|
089
|
090
|
091
| /*效率较低的查询计划可能增大 CPU 占用率。
|
092
| 下面的查询显示哪个查询占用了最多的 CPU 累计使用率。
|
093
| */
|
094
| SELECT
|
095
|
highest_cpu_queries.plan_handle,
|
096
|
highest_cpu_queries.total_worker_time,
|
097
|
q.dbid,
|
098
|
q.objectid,
|
099
|
q.number,
|
100
|
q.encrypted,
|
101
|
q.[text]
|
102
| from
|
103
|
(
select
top
50
|
104
|
qs.plan_handle,
|
105
|
qs.total_worker_time
|
106
|
from
|
107
|
sys.dm_exec_query_stats qs
|
108
|
order
by
qs.total_worker_time
desc
)
as
highest_cpu_queries
|
109
|
cross
apply sys.dm_exec_sql_text(plan_handle)
as
q
|
110
| order
by
highest_cpu_queries.total_worker_time
desc
|
111
|
112
|
113
| --下面的查询显示一些可能占用大量 CPU 使用率的运算符(例如 ‘%Hash Match%’、‘%Sort%’)以找出可疑对象
|
114
|
115
| select
*
|
116
| from
|
117
|
sys.dm_exec_cached_plans
|
118
|
cross
apply sys.dm_exec_query_plan(plan_handle)
|
119
| where
|
120
|
cast
(query_plan
as
nvarchar(
max
))
like
'%Sort%'
|
121
|
or
cast
(query_plan
as
nvarchar(
max
))
like
'%Hash Match%'
|
122
| /*
|
123
| 如果已检测到效率低下并导致 CPU 占用率较高的查询计划,请对该查询中涉及的表运行
UPDATE
STATISTICS
以查看该问题是否仍然存在。然后,收集相关数据并将此问题报告给 PerformancePoint Planning 支持人员。
|
124
|
125
| 如果您的系统存在过多的编译和重新编译,可能会导致系统出现与 CPU 相关的性能问题。
|
126
|
127
| 您可以运行下面的 DMV 查询来找出过多的编译/重新编译。
|
128
|
129
| */
|
130
| select
*
from
sys.dm_exec_query_optimizer_info
|
131
| where
|
132
| counter =
'optimizations'
|
133
| or
counter =
'elapsed time'
|
134
|
135
|
136
| --开始内存压力检测和调查之前,请确保已启用 SQL Server 中的高级选项。请先对 master 数据库运行以下查询以启用此选项。
|
137
|
138
| sp_configure
'show advanced options'
|
139
| go
|
140
| sp_configure
'show advanced options'
, 1
|
141
| go
|
142
| reconfigure
|
143
| go
|
144
|
145
| --首先运行以下查询以检查内存相关配置选项。
|
146
|
147
| sp_configure
'awe_enabled'
|
148
| go
|
149
| sp_configure
'min server memory'
|
150
| go
|
151
| sp_configure
'max server memory'
|
152
| go
|
153
| sp_configure
'min memory per query'
|
154
| go
|
155
| sp_configure
'query wait'
|
156
| go
|
157
|
158
| --运行下面的 DMV 查询以查看 CPU、计划程序内存和缓冲池信息。
|
159
|
160
| select
|
161
| cpu_count,
|
162
| hyperthread_ratio,
|
163
| scheduler_count,
|
164
| physical_memory_in_bytes / 1024 / 1024
as
physical_memory_mb,
|
165
| virtual_memory_in_bytes / 1024 / 1024
as
virtual_memory_mb,
|
166
| bpool_committed * 8 / 1024
as
bpool_committed_mb,
|
167
| bpool_commit_target * 8 / 1024
as
bpool_target_mb,
|
168
| bpool_visible * 8 / 1024
as
bpool_visible_mb
|
169
| from
sys.dm_os_sys_info
|
170
|
171
| /*I/O 瓶颈
|
172
| 检查闩锁等待统计信息以确定 I/O 瓶颈。运行下面的 DMV 查询以查找 I/O 闩锁等待统计信息
|
173
| */
|
174
| select
wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
|
175
| from
sys.dm_os_wait_stats
|
176
| where
wait_type
like
'PAGEIOLATCH%'
and
waiting_tasks_count > 0
|
177
| order
by
wait_type
|
178
|
179
| /*如果 waiting_task_counts 和 wait_time_ms 与正常情况相比有显著变化,则可以确定存在 I/O 问题。获取 SQL Server 平稳运行时性能计数器和主要 DMV 查询输出的基线非常重要。
|
180
|
181
| 这些 wait_types 可以指示您的 I/O 子系统是否遇到瓶颈。
|
182
|
183
| 使用以下 DMV 查询来查找当前挂起的 I/O 请求。请定期执行此查询以检查 I/O 子系统的运行状况,并隔离 I/O 瓶颈中涉及的物理磁盘。
|
184
|
185
| */
|
186
|
187
| select
|
188
|
database_id,
|
189
|
file_id,
|
190
|
io_stall,
|
191
|
io_pending_ms_ticks,
|
192
|
scheduler_address
|
193
| from
sys.dm_io_virtual_file_stats(
NULL
,
NULL
)t1,
|
194
|
sys.dm_io_pending_io_requests
as
t2
|
195
| where
t1.file_handle = t2.io_handle
|
196
|
197
| /*
|
198
| 在正常情况下,该查询通常不返回任何内容。如果此查询返回一些行,则需要进一步调查。
|
199
|
200
| 您还可以执行下面的 DMV 查询以查找 I/O 相关查询。
|
201
| */
|
202
| select
top
5 (total_logical_reads/execution_count)
as
avg_logical_reads,
|
203
|
(total_logical_writes/execution_count)
as
avg_logical_writes,
|
204
|
(total_physical_reads/execution_count)
as
avg_physical_reads,
|
205
|
Execution_count, statement_start_offset, p.query_plan, q.text
|
206
| from
sys.dm_exec_query_stats
|
207
|
cross
apply sys.dm_exec_query_plan(plan_handle) p
|
208
|
cross
apply sys.dm_exec_sql_text(plan_handle)
as
q
|
209
| order
by
(total_logical_reads + total_logical_writes)/execution_count
Desc
|
210
|
211
|
212
| --下面的 DMV 查询可用于查找哪些批处理/请求生成的 I/O 最多。如下所示的 DMV 查询可用于查找可生成最多 I/O 的前五个请求。调整这些查询将提高系统性能
|
213
|
214
| Code
|
215
| select
top
5
|
216
|
(total_logical_reads/execution_count)
as
avg_logical_reads,
|
217
|
(total_logical_writes/execution_count)
as
avg_logical_writes,
|
218
|
(total_physical_reads/execution_count)
as
avg_phys_reads,
|
219
|
Execution_count,
|
220
|
statement_start_offset
as
stmt_start_offset,
|
221
|
sql_handle,
|
222
|
plan_handle
|
223
| from
sys.dm_exec_query_stats
|
224
| order
by
(total_logical_reads + total_logical_writes)
Desc
|
225
|
226
| /*阻塞
|
227
| 运行下面的查询可确定阻塞的会话。
|
228
| */
|
229
| select
blocking_session_id, wait_duration_ms, session_id
from
|
230
| sys.dm_os_waiting_tasks
|
231
| where
blocking_session_id
is
not
null
|
232
|
233
| --使用此调用可找出 blocking_session_id 所返回的 SQL。例如,如果 blocking_session_id 是 87,则运行此查询可获得相应的 SQL。
|
234
|
235
| dbcc INPUTBUFFER(87)
|
236
|
237
| --下面的查询显示 SQL 等待分析和前 10 个等待的资源。
|
238
|
239
| select
top
10 *
|
240
| from
sys.dm_os_wait_stats
|
241
| --where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
|
242
| order
by
wait_time_ms
desc
|
243
|
244
| /*若要找出哪个 spid 正在阻塞另一个 spid,可在数据库中创建以下存储过程,然后执行该存储过程。此存储过程会报告此阻塞情况。键入 sp_who 可找出 @spid;@spid 是可选参数。
|
245
| */
|
246
| create
proc dbo.sp_block (@spid
bigint
=
NULL
)
|
247
| as
|
248
| select
|
249
|
t1.resource_type,
|
250
|
'database'
=db_name(resource_database_id),
|
251
|
'blk object'
= t1.resource_associated_entity_id,
|
252
|
t1.request_mode,
|
253
|
t1.request_session_id,
|
254
|
t2.blocking_session_id
|
255
| from
|
256
|
sys.dm_tran_locks
as
t1,
|
257
|
sys.dm_os_waiting_tasks
as
t2
|
258
| where
|
259
|
t1.lock_owner_address = t2.resource_address
and
|
260
|
t1.request_session_id =
isnull
(@spid,t1.request_session_id)
|
261
|
262
| /*以下是使用此存储过程的示例。*/
|
263
| exec
sp_block
|
264
| exec
sp_block @spid = 7
|
265
|
266
| SELECT
*
FROM
dbo.Orders
|
267
|
268
| SELECT
customerID ,EmployeeID ,ShippedDate ,
COUNT
(*)
AS
numorders
|
269
| FROM
dbo.Orders
|
270
| WHERE
orderdate >=
'19980504'
|
271
| AND
orderdate <=
'19980506'
|
272
| GROUP
BY
customerID ,EmployeeID ,ShippedDate
|
273
| WITH
CUBE
|