原文:
人人都是 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 发表自 博客园 ，未经作者本人同意禁止任何形式的转载，任何自动或人为的爬虫转载行为均为耍流氓。
