什么？有个 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》系列文章索引：
浙公网安备 33010602011771号