Sql Server CPU 性能排查及優(yōu)化的相關(guān) Sql 語句，非常好的SQL語句，記錄于此：
--
Begin Cpu 分析優(yōu)化的相關(guān) Sql
--
使用DMV來分析SQL Server啟動以來累計使用CPU資源最多的語句。例如下面的語句就可以列出前50名。
select
c.last_execution_time,c.execution_count,c.total_logical_reads,c.total_logical_writes,c.total_elapsed_time,c.last_elapsed_time,
q.
[
text
]
from
(
select
top
50
qs.
*
from
sys.dm_exec_query_stats qs
order
by
qs.total_worker_time
desc
)
as
c
cross
apply sys.dm_exec_sql_text(plan_handle)
as
q
order
by
c.total_worker_time
desc
go
--
返回最經(jīng)常運行的100條語句
SELECT
TOP
100
cp.cacheobjtype,cp.usecounts,cp.size_in_bytes,qs.statement_start_offset,qs.statement_end_offset,qt.dbid ,qt.objectid
,
SUBSTRING
(qt.
text
,qs.statement_start_offset
/
2
,
(
case
when
qs.statement_end_offset
=
-
1
then
len
(
convert
(
nvarchar
(
max
), qt.
text
))
*
2
else
qs.statement_end_offset
end
-
qs.statement_start_offset)
/
2
)
as
statement
FROM
sys.dm_exec_query_stats qs
cross
apply sys.dm_exec_sql_text(qs.sql_handle)
as
qt
inner
join
sys.dm_exec_cached_plans
as
cp
on
qs.plan_handle
=
cp.plan_handle
where
cp.plan_handle
=
qs.plan_handle
and
cp.usecounts
>
4
ORDER
BY
[
dbid
]
,
[
Usecounts
]
DESC
--
返回做IO數(shù)目最多的50條語句以及它們的執(zhí)行計劃
select
top
50
(total_logical_reads
/
execution_count)
as
avg_logical_reads,
(total_logical_writes
/
execution_count)
as
avg_logical_writes,
(total_physical_reads
/
execution_count)
as
avg_phys_reads,
Execution_count,
statement_start_offset
as
stmt_start_offset, statement_end_offset
as
stmt_end_offset,
substring
(sql_text.
text
, (statement_start_offset
/
2
),
case
when
(statement_end_offset
-
statement_start_offset)
/
2
<=
0
then
64000
else
(statement_end_offset
-
statement_start_offset)
/
2
end
)
as
exec_statement, sql_text.
text
,plan_text.
*
from
sys.dm_exec_query_stats
cross
apply sys.dm_exec_sql_text(sql_handle)
as
sql_text
cross
apply sys.dm_exec_query_plan(plan_handle)
as
plan_text
order
by
(total_logical_reads
+
total_logical_writes)
/
Execution_count
Desc
--
計算signal wait占整wait時間的百分比
--
指令等待 CPU 資源的時間占總時間的百分比。如果超過 25% ，說明 CPU 緊張
select
convert
(numeric(
5
,
4
),
sum
(signal_wait_time_ms)
/
sum
(wait_time_ms))
from
Sys.dm_os_wait_stats
--
計算'Cxpacket'占整wait時間的百分比
--
Cxpacket:Sql Server 在處理一句代價很大的語句，要不就是沒有合適的索引或篩選條件沒能篩選足夠的記錄，使得語句要返回大量的結(jié)果，當(dāng) >5% 說明有問題
declare
@Cxpacket
bigint
declare
@Sumwaits
bigint
select
@Cxpacket
=
wait_time_ms
from
Sys.dm_os_wait_stats
where
wait_type
=
'
Cxpacket
'
select
@Sumwaits
=
sum
(wait_time_ms)
from
Sys.dm_os_wait_stats
select
convert
(numeric(
5
,
4
),
@Cxpacket
/
@Sumwaits
)
--
查詢當(dāng)前數(shù)據(jù)庫上所有用戶表格在Row lock上發(fā)生阻塞的頻率
declare
@dbid
int
select
@dbid
=
db_id
()
Select
dbid
=
database_id, objectname
=
object_name
(s.
object_id
)
, indexname
=
i.name, i.index_id
--
, partition_number
, row_lock_count, row_lock_wait_count
,
[
block %
]
=
cast
(
100.0
*
row_lock_wait_count
/
(
1
+
row_lock_count)
as
numeric(
15
,
2
))
, row_lock_wait_in_ms
,
[
avg row lock waits in ms
]
=
cast
(
1.0
*
row_lock_wait_in_ms
/
(
1
+
row_lock_wait_count)
as
numeric(
15
,
2
))
from
sys.dm_db_index_operational_stats (
@dbid
,
NULL
,
NULL
,
NULL
) s, sys.indexes i
where
objectproperty
(s.
object_id
,
'
IsUserTable
'
)
=
1
and
i.
object_id
=
s.
object_id
and
i.index_id
=
s.index_id
order
by
row_lock_wait_count
desc
--
End Cpu 分析優(yōu)化的相關(guān) Sql
相關(guān)文章：
Sql Server Cpu 100% 的常見原因及優(yōu)化
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
