什么？有个 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》系列文章索引：
