原文:
人人都是 DBA（XII）查詢信息收集腳本匯編
什么？有個 SQL 執行了 8 秒！
哪里出了問題？臣妾不知道啊，得找 DBA 啊。
DBA 人呢？離職了！！擦！！！
程序員在無處尋求幫助時，就得想辦法自救，努力讓自己變成 "偽 DBA"。
索引
- 按頁編號查看數據表信息
- 獲取查詢 SELECT 語句的執行次數排名
- 看看哪些 Ad-hoc Query 在浪費資源
- 查看當前處于等待狀態的 Task 在等什么
- 查詢誰在占著 Session 連接
- 查詢程序占用的 SPID 信息
- 查詢所有執行 SQL 對應的 sql_handle
- 查詢最近 60 秒平均執行時間超過 300 毫秒的 SQL 語句
- 查詢最近 60 秒平均執行時間超過 100 毫秒的非 SELECT 語句
- 查詢最近 60 秒累計總執行次數大于 1000 次的 SQL 語句
- 查詢前 10 個可能是性能最差的 SQL 語句
- 看看當前哪些查詢正在活躍著
按頁編號查看數據表信息
SELECT
sc.
[
name
]
AS
[
schema
]
,o.
[
name
]
AS
[
table_name
]
,o.type_desc
,obd.
[
file_id
]
,obd.page_id
,obd.page_level
,obd.row_count
,obd.free_space_in_bytes
,obd.is_modified
,obd.numa_node
FROM
sys.dm_os_buffer_descriptors
AS
obd
JOIN
sys.allocation_units
AS
au
ON
obd.allocation_unit_id
=
au.allocation_unit_id
JOIN
sys.partitions
AS
p
ON
au.container_id
=
p.partition_id
JOIN
sys.objects
AS
o
ON
p.
[
object_id
]
=
o.
[
object_id
]
JOIN
sys.schemas
AS
sc
ON
o.
[
schema_id
]
=
sc.
[
schema_id
]
WHERE
database_id
=
DB_ID
()
AND
o.is_ms_shipped
=
0
ORDER
BY
obd.page_id
,o.
[
name
]
獲取查詢 SELECT 語句的執行次數排名
SQL Server 2012 版本
SELECT
TOP
(
100
) qs.execution_count
,qs.total_rows
,qs.last_rows
,qs.min_rows
,qs.max_rows
,qs.last_elapsed_time
,qs.min_elapsed_time
,qs.max_elapsed_time
,total_worker_time
,total_logical_reads
,
SUBSTRING
(qt.
[
text
]
, qs.statement_start_offset
/
2
+
1
, (
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
query_text
FROM
sys.dm_exec_query_stats
AS
qs
WITH
(NOLOCK)
CROSS
APPLY sys.dm_exec_sql_text(qs.sql_handle)
AS
qt
ORDER
BY
qs.execution_count
DESC
OPTION
(RECOMPILE);
SQL Server 2008 R2 版本
SELECT
TOP
(
100
) qs.execution_count
,qs.last_elapsed_time
,qs.min_elapsed_time
,qs.max_elapsed_time
,total_worker_time
,total_logical_reads
,
SUBSTRING
(qt.
[
text
]
, qs.statement_start_offset
/
2
+
1
, (
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
query_text
FROM
sys.dm_exec_query_stats
AS
qs
WITH
(NOLOCK)
CROSS
APPLY sys.dm_exec_sql_text(qs.sql_handle)
AS
qt
ORDER
BY
qs.execution_count
DESC
OPTION
(RECOMPILE);
看看哪些 Ad-hoc Query 在浪費資源
SELECT
TOP
(
50
)
[
text
]
AS
[
QueryText
]
,cp.cacheobjtype
,cp.objtype
,cp.size_in_bytes
/
1024
AS
[
Plan Size in KB
]
FROM
sys.dm_exec_cached_plans
AS
cp
WITH
(NOLOCK)
CROSS
APPLY sys.dm_exec_sql_text(plan_handle)
WHERE
cp.cacheobjtype
=
N
'
Compiled Plan
'
AND
cp.objtype
IN
(
N
'
Adhoc
'
,N
'
Prepared
'
)
AND
cp.usecounts
=
1
ORDER
BY
cp.size_in_bytes
DESC
OPTION
(RECOMPILE);
查看當前處于等待狀態的 Task 在等什么
SELECT
dm_ws.wait_duration_ms
,dm_ws.wait_type
,dm_es.STATUS
,dm_t.
TEXT
,dm_qp.query_plan
,dm_ws.session_ID
,dm_es.cpu_time
,dm_es.memory_usage
,dm_es.logical_reads
,dm_es.total_elapsed_time
,dm_es.program_name
,
DB_NAME
(dm_r.database_id) DatabaseName
,dm_ws.blocking_session_id
,dm_r.wait_resource
,dm_es.login_name
,dm_r.command
,dm_r.last_wait_type
FROM
sys.dm_os_waiting_tasks dm_ws
INNER
JOIN
sys.dm_exec_requests dm_r
ON
dm_ws.session_id
=
dm_r.session_id
INNER
JOIN
sys.dm_exec_sessions dm_es
ON
dm_es.session_id
=
dm_r.session_id
CROSS
APPLY sys.dm_exec_sql_text(dm_r.sql_handle) dm_t
CROSS
APPLY sys.dm_exec_query_plan(dm_r.plan_handle) dm_qp
WHERE
dm_es.is_user_process
=
1
;
查詢誰在占著 Session 連接
CREATE
TABLE
#sp_who2 (
SPID
INT
,STATUS
VARCHAR
(
255
)
,LOGIN
VARCHAR
(
255
)
,HostName
VARCHAR
(
255
)
,BlkBy
VARCHAR
(
255
)
,DBName
VARCHAR
(
255
)
,Command
VARCHAR
(
255
)
,CPUTime
INT
,DiskIO
INT
,LastBatch
VARCHAR
(
255
)
,ProgramName
VARCHAR
(
255
)
,SPID2
INT
,REQUESTID
INT
)
INSERT
INTO
#sp_who2
EXEC
sp_who2
SELECT
*
FROM
#sp_who2 w
--
WHERE w.ProgramName = 'xxx'
DROP
TABLE
#sp_who2
查詢程序占用的 SPID 信息
SELECT
spid
,a.
[
status
]
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,b.
[
name
]
,loginame
FROM
master.dbo.sysprocesses a
INNER
JOIN
master.dbo.sysdatabases b
ON
a.dbid
=
b.dbid
where
hostname
!=
''
ORDER
BY
program_name
查詢所有執行 SQL 對應的 sql_handle
DECLARE
@current_sql_handle
BINARY
(
20
);
DECLARE
@sql_text_list
TABLE
(
sql_handle
BINARY
(
20
)
,
TEXT
NVARCHAR
(
max
)
);
DECLARE
sql_handle_cursor
CURSOR
FOR
SELECT
sp.sql_handle
FROM
sys.sysprocesses sp
WHERE
sp.sql_handle
!=
0x0000000000000000000000000000000000000000
--
AND sp.program_name = 'xxxx'
;
OPEN
sql_handle_cursor
FETCH
NEXT
FROM
sql_handle_cursor
INTO
@current_sql_handle
WHILE
@@FETCH_STATUS
=
0
BEGIN
INSERT
INTO
@sql_text_list
(
sql_handle
,
TEXT
)
SELECT
@current_sql_handle
,est.
TEXT
FROM
sys.dm_exec_query_stats qs
CROSS
APPLY sys.dm_exec_sql_text(
@current_sql_handle
) est;
FETCH
NEXT
FROM
sql_handle_cursor
INTO
@current_sql_handle
END
SELECT
DISTINCT
*
FROM
@sql_text_list
tl
WHERE
tl.
TEXT
NOT
LIKE
'
%statement_start_offset%
'
;
CLOSE
sql_handle_cursor
DEALLOCATE
sql_handle_cursor
查詢最近 60 秒平均執行時間超過 300 毫秒的 SQL 語句
SELECT
SUBSTRING
(st.
TEXT
, (qs.statement_start_offset
/
2
)
+
1
, (
(
(
CASE
statement_end_offset
WHEN
-
1
THEN
DATALENGTH
(st.
TEXT
)
ELSE
qs.statement_end_offset
END
)
-
qs.statement_start_offset
)
/
2
)
+
1
)
AS
statement_text
,last_execution_time
,total_elapsed_time
/
execution_count avg_elapsed_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
,execution_count
,total_worker_time
,total_elapsed_time
,creation_time
FROM
sys.dm_exec_query_stats
AS
qs
CROSS
APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
last_execution_time
>
DATEADD
(SECOND,
-
60
,
GETDATE
())
AND
st.
TEXT
NOT
LIKE
'
%statement_start_offset%
'
AND
total_elapsed_time
/
execution_count
>=
300
ORDER
BY
last_execution_time
DESC
;
查詢最近 60 秒平均執行時間超過 100 毫秒的非 SELECT 語句
SELECT
SUBSTRING
(st.
TEXT
, (qs.statement_start_offset
/
2
)
+
1
, (
(
(
CASE
statement_end_offset
WHEN
-
1
THEN
DATALENGTH
(st.
TEXT
)
ELSE
qs.statement_end_offset
END
)
-
qs.statement_start_offset
)
/
2
)
+
1
)
AS
statement_text
,last_execution_time
,total_elapsed_time
/
execution_count avg_elapsed_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
,execution_count
,total_worker_time
,total_elapsed_time
,creation_time
FROM
sys.dm_exec_query_stats
AS
qs
CROSS
APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
last_execution_time
>
DATEADD
(SECOND,
-
60
,
GETDATE
())
AND
st.
TEXT
NOT
LIKE
'
%statement_start_offset%
'
AND
execution_count
<
100
AND
total_elapsed_time
/
execution_count
>
100
AND
SUBSTRING
(st.
TEXT
, (qs.statement_start_offset
/
2
)
+
1
, (
(
(
CASE
statement_end_offset
WHEN
-
1
THEN
DATALENGTH
(st.
TEXT
)
ELSE
qs.statement_end_offset
END
)
-
qs.statement_start_offset
)
/
2
)
+
1
)
NOT
LIKE
'
SELECT%
'
ORDER
BY
last_execution_time
DESC
;
查詢最近 60 秒累計總執行次數大于 1000 次的 SQL 語句
SELECT
SUBSTRING
(st.
TEXT
, (qs.statement_start_offset
/
2
)
+
1
, (
(
(
CASE
statement_end_offset
WHEN
-
1
THEN
DATALENGTH
(st.
TEXT
)
ELSE
qs.statement_end_offset
END
)
-
qs.statement_start_offset
)
/
2
)
+
1
)
AS
statement_text
,total_elapsed_time
/
execution_count
/
1000
AS
avg_elapsed_time_by_ms
,last_execution_time
,total_elapsed_time
,execution_count
,total_worker_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
,creation_time
FROM
sys.dm_exec_query_stats
AS
qs
CROSS
APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
qs.execution_count
>
1000
AND
last_execution_time
>
DATEADD
(SECOND,
-
60
,
GETDATE
())
--
AND (
--
st.TEXT LIKE '%[[]AAA]%'
--
OR st.TEXT LIKE '%[[]BBB]%'
--
OR st.TEXT LIKE '%[[]CCC]%'
--
)
ORDER
BY
total_elapsed_time
/
execution_count
DESC
;
查詢前 10 個可能是性能最差的 SQL 語句
SELECT
TOP
10
TEXT
AS
'
SQL Statement
'
,last_execution_time
AS
'
Last Execution Time
'
,(total_logical_reads
+
total_physical_reads
+
total_logical_writes)
/
execution_count
AS
[
Average IO
]
,(total_worker_time
/
execution_count)
/
1000000.0
AS
[
Average CPU Time (sec)
]
,(total_elapsed_time
/
execution_count)
/
1000000.0
AS
[
Average Elapsed Time (sec)
]
,execution_count
AS
"Execution
Count
"
,qp.query_plan
AS
"Query
Plan
"
FROM
sys.dm_exec_query_stats qs
CROSS
APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS
APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER
BY
total_elapsed_time
/
execution_count
DESC
看看當前哪些查詢正在活躍著
Adam Machanic 發布了一個查詢活躍 SQL 的查詢腳本，篇幅極長，請到發布地址下載。
?
《人人都是 DBA》系列文章索引：
本系列文章《 人人都是 DBA 》由? Dennis Gao ?發表自 博客園 ，未經作者本人同意禁止任何形式的轉載，任何自動或人為的爬蟲轉載行為均為耍流氓。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
