什么？有個(gè) SQL 執(zhí)行了 8 秒！

哪里出了問(wèn)題？臣妾不知道啊，得找 DBA 啊。

DBA 人呢？離職了！！擦！！！

程序員在無(wú)處尋求幫助時(shí)，就得想辦法自救，努力讓自己變成 "偽 DBA"。

索引

按頁(yè)編號(hào)查看數(shù)據(jù)表信息

獲取查詢 SELECT 語(yǔ)句的執(zhí)行次數(shù)排名

看看哪些 Ad-hoc Query 在浪費(fèi)資源

查看當(dāng)前處于等待狀態(tài)的 Task 在等什么

查詢誰(shuí)在占著 Session 連接

查詢程序占用的 SPID 信息

查詢所有執(zhí)行 SQL 對(duì)應(yīng)的 sql_handle

查詢最近 60 秒平均執(zhí)行時(shí)間超過(guò) 300 毫秒的 SQL 語(yǔ)句

查詢最近 60 秒平均執(zhí)行時(shí)間超過(guò) 100 毫秒的非 SELECT 語(yǔ)句

查詢最近 60 秒累計(jì)總執(zhí)行次數(shù)大于 1000 次的 SQL 語(yǔ)句

查詢前 10 個(gè)可能是性能最差的 SQL 語(yǔ)句

看看當(dāng)前哪些查詢正在活躍著

按頁(yè)編號(hào)查看數(shù)據(jù)表信息

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 語(yǔ)句的執(zhí)行次數(shù)排名

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 = - 1THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2ELSE qs.statement_end_offsetEND - 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 = - 1THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2ELSE qs.statement_end_offsetEND - 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 在浪費(fèi)資源

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);

查看當(dāng)前處于等待狀態(tài)的 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;

查詢誰(shuí)在占著 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_who2SELECT * 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

查詢所有執(zhí)行 SQL 對(duì)應(yīng)的 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_cursorFETCH NEXT FROM sql_handle_cursor INTO @current_sql_handleWHILE @@FETCH_STATUS = 0 BEGININSERT INTO @sql_text_list (sql_handle,TEXT)SELECT @current_sql_handle,est.TEXTFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(@current_sql_handle) est;FETCH NEXTFROM sql_handle_cursorINTO @current_sql_handle ENDSELECT DISTINCT * FROM @sql_text_list tl WHERE tl.TEXT NOT LIKE '%statement_start_offset%';CLOSE sql_handle_cursorDEALLOCATE sql_handle_cursor

查詢最近 60 秒平均執(zhí)行時(shí)間超過(guò) 300 毫秒的 SQL 語(yǔ)句

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (((CASE statement_end_offsetWHEN - 1THEN DATALENGTH(st.TEXT)ELSE qs.statement_end_offsetEND) - 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 秒平均執(zhí)行時(shí)間超過(guò) 100 毫秒的非 SELECT 語(yǔ)句

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (((CASE statement_end_offsetWHEN - 1THEN DATALENGTH(st.TEXT)ELSE qs.statement_end_offsetEND) - 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 < 100AND total_elapsed_time / execution_count > 100AND SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (((CASE statement_end_offsetWHEN - 1THEN DATALENGTH(st.TEXT)ELSE qs.statement_end_offsetEND) - qs.statement_start_offset) / 2) + 1) NOT LIKE 'SELECT%' ORDER BY last_execution_time DESC;

查詢最近 60 秒累計(jì)總執(zhí)行次數(shù)大于 1000 次的 SQL 語(yǔ)句

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (((CASE statement_end_offsetWHEN - 1THEN DATALENGTH(st.TEXT)ELSE qs.statement_end_offsetEND) - 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 > 1000AND 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 個(gè)可能是性能最差的 SQL 語(yǔ)句

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

看看當(dāng)前哪些查詢正在活躍著

Adam Machanic 發(fā)布了一個(gè)查詢活躍 SQL 的查詢腳本，篇幅極長(zhǎng)，請(qǐng)到發(fā)布地址下載。

• Who is Active v11.11

?

《人人都是 DBA》系列文章索引：

?序號(hào)?	?名稱?
1	?人人都是 DBA（I）SQL Server 體系結(jié)構(gòu)
2	?人人都是 DBA（II）SQL Server 元數(shù)據(jù)
3	?人人都是 DBA（III）SQL Server 調(diào)度器
4	?人人都是 DBA（IV）SQL Server 內(nèi)存管理
5	?人人都是 DBA（V）SQL Server 數(shù)據(jù)庫(kù)文件
6	?人人都是 DBA（VI）SQL Server 事務(wù)日志
7	?人人都是 DBA（VII）B 樹(shù)和 B+ 樹(shù)
8	?人人都是 DBA（VIII）SQL Server 頁(yè)存儲(chǔ)結(jié)構(gòu)
9	?人人都是 DBA（IX）服務(wù)器信息收集腳本匯編
10	?人人都是 DBA（X）資源信息收集腳本匯編
11	?人人都是 DBA（XI）I/O 信息收集腳本匯編
12	?人人都是 DBA（XII）查詢信息收集腳本匯編
13	?人人都是 DBA（XIII）索引信息收集腳本匯編
14	?人人都是 DBA（XIV）存儲(chǔ)過(guò)程信息收集腳本匯編?
15	?人人都是 DBA（XV）鎖信息收集腳本匯編

本系列文章《人人都是 DBA》由?Dennis Gao?發(fā)表自博客園，未經(jīng)作者本人同意禁止任何形式的轉(zhuǎn)載，任何自動(dòng)或人為的爬蟲(chóng)轉(zhuǎn)載行為均為耍流氓。

總結(jié)

以上是生活随笔為你收集整理的人人都是 DBA（XII）查询信息收集脚本汇编的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。