什么?有個 SQL 執(zhí)行了 8 秒!
哪里出了問題?臣妾不知道啊,得找 DBA 啊。
DBA 人呢?離職了!!擦!!!
程序員在無處尋求幫助時,就得想辦法自救,努力讓自己變成 "偽 DBA"。
索引
- 按頁編號查看數(shù)據(jù)表信息
- 獲取查詢 SELECT 語句的執(zhí)行次數(shù)排名
- 看看哪些 Ad-hoc Query 在浪費資源
- 查看當前處于等待狀態(tài)的 Task 在等什么
- 查詢誰在占著 Session 連接
- 查詢程序占用的 SPID 信息
- 查詢所有執(zhí)行 SQL 對應(yīng)的 sql_handle
- 查詢最近 60 秒平均執(zhí)行時間超過 300 毫秒的 SQL 語句
- 查詢最近 60 秒平均執(zhí)行時間超過 100 毫秒的非 SELECT 語句
- 查詢最近 60 秒累計總執(zhí)行次數(shù)大于 1000 次的 SQL 語句
- 查詢前 10 個可能是性能最差的 SQL 語句
- 看看當前哪些查詢正在活躍著
按頁編號查看數(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 語句的執(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 = - 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);
查看當前處于等待狀態(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 ;
查詢誰在占著 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
查詢所有執(zhí)行 SQL 對應(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_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 秒平均執(zhí)行時間超過 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 秒平均執(zhí)行時間超過 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 秒累計總執(zhí)行次數(shù)大于 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 發(fā)布了一個查詢活躍 SQL 的查詢腳本,篇幅極長,請到發(fā)布地址下載。
?
《人人都是 DBA》系列文章索引:
本系列文章《 人人都是 DBA 》由? Dennis Gao ?發(fā)表自 博客園 ,未經(jīng)作者本人同意禁止任何形式的轉(zhuǎn)載,任何自動或人為的爬蟲轉(zhuǎn)載行為均為耍流氓。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
