SQL Server常用的效能診斷語句

標籤：

/*常規伺服器動態管理對象包括：dm_db_*：資料庫和資料庫物件dm_exec_*：執行使用者代碼和關聯的串連dm_os_*：記憶體、鎖定和時間安排dm_tran_*：事務和隔離dm_io_*：網路和磁碟的輸入/輸出*/--- 運行下面的 DMV 查詢以查看 CPU、排程器記憶體和緩衝池資訊。select     cpu_count,    hyperthread_ratio,    scheduler_count,    physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,    virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,    bpool_committed * 8 / 1024 as bpool_committed_mb,    bpool_commit_target * 8 / 1024 as bpool_target_mb,    bpool_visible * 8 / 1024 as bpool_visible_mbfrom sys.dm_os_sys_info--- 高I/O開銷的查詢  Identifying Most Costly Queries by I/O SELECT TOP 10       [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count    , [Total IO] = (total_logical_reads + total_logical_writes)    , [Execution count] = qs.execution_count    , [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,              (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)             ,[Parent Query] = qt.text    , DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Average IO] DESC;--- 高CPU開銷的查詢 Identifying Most Costly Queries by CPU SELECT TOP 10       [Average CPU used] = total_worker_time / qs.execution_count    , [Total CPU used] = total_worker_time    , [Execution count] = qs.execution_count    , [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,              (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)    , [Parent Query] = qt.text    , DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Average CPU used] DESC;--- 高開銷的缺失索引 Cost of Missing Indexes SELECT  TOP 10         [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)         , avg_user_impact        , TableName = statement        , [EqualityUsage] = equality_columns         , [InequalityUsage] = inequality_columns        , [Include Cloumns] = included_columnsFROM        sys.dm_db_missing_index_groups g INNER JOIN    sys.dm_db_missing_index_group_stats s        ON s.group_handle = g.index_group_handle INNER JOIN    sys.dm_db_missing_index_details d        ON d.index_handle = g.index_handleORDER BY [Total Cost] DESC;--- 最常執行的查詢 Identifying Queries that Execute Most Often SELECT TOP 10  [Execution count] = execution_count,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,          (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),[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Execution count] DESC;--- 重複編譯的查詢（plan_generation_num 指示該查詢已重新編譯的次數）select top 25      sql_text.text,      sql_handle,      plan_generation_num,      execution_count,      dbid,      objectid from sys.dm_exec_query_stats a      cross apply sys.dm_exec_sql_text(sql_handle) as sql_textwhere plan_generation_num > 1order by plan_generation_num desc--- 伺服器等待的原因 SQL Query Records Causes of Wait Times SELECT TOP 10 [Wait type] = wait_type, [Wait time (s)] = wait_time_ms / 1000, [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0                / SUM(wait_time_ms) OVER())FROM sys.dm_os_wait_statsWHERE wait_type NOT LIKE ‘%SLEEP%‘ ORDER BY wait_time_ms DESC;--- 讀和寫 Identifying the Most Reads and WritesSELECT TOP 10         [Total Reads] = SUM(total_logical_reads)        ,[Execution count] = SUM(qs.execution_count)        ,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtGROUP BY DB_NAME(qt.dbid)ORDER BY [Total Reads] DESC;SELECT TOP 10         [Total Writes] = SUM(total_logical_writes)        ,[Execution count] = SUM(qs.execution_count)        ,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtGROUP BY DB_NAME(qt.dbid)ORDER BY [Total Writes] DESC;--- 運行下面的 DMV 查詢以尋找 I/O 閂鎖等候統計資訊。select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_countfrom sys.dm_os_wait_stats  where wait_type like ‘PAGEIOLATCH%‘  and waiting_tasks_count > 0order by wait_type--- 查看資料庫的阻塞資訊select * from sysprocesses a where a.program_name = ‘.Net SqlClient Data Provider‘ and blocked != 0---查看所有會話的 找到活動事務對應的執行語句select  dc.session_id,        ds.login_name,        ds.login_time,                       dc.connect_time,        dc.net_transport,        dc.client_net_address,         ds.host_name,        ds.program_name,        case ds.status  when ‘sleeping‘ then ‘睡眠 - 當前沒有運行任何請求 ‘                        when ‘running‘  then ‘正在運行 - 當前正在運行一個或多個請求 ‘                        when ‘Dormancy‘ then ‘休眠 – 會話因串連池而被重設，並且現在處於登入前狀態‘                        when ‘Pre-connected‘ then ‘預串連 - 會話在資源管理員分類器中‘                        end as status ,        ds.cpu_time as cpu_time_ms,        ds.memory_usage*8 as memory_kb,        ds.total_elapsed_time as total_elapsed_time_ms,        case ds.transaction_isolation_level when 0 then ‘未指定‘                                            when 1 then ‘未提交讀取‘                                            when 2 then ‘已提交讀取‘                                            when 3 then ‘可重複‘                                            when 4 then ‘可序列化‘                                            when 5 then ‘快照‘                                        end ‘會話的交易隔離等級‘,         dt.text              from sys.dm_exec_connections  dc        --執行串連，最近執行的查詢資訊  cross apply sys.dm_exec_sql_text(dc.most_recent_sql_handle) dtjoin sys.dm_exec_sessions ds  on dc.session_id=ds.session_idwhere ds.login_name= ‘LCGS609999‘  --where ds.program_name = ‘.Net SqlClient Data Provider‘ORDER BY dt.text--kill 53;-- 檢查分析死結資訊1、使用sql profiler 抓取死結鏈及圖2、跟蹤分析SQL日誌dbcc traceon (1204, 3605, -1)godbcc tracestatus(-1)go參考資料：http://support.microsoft.com/zh-cn/kb/832524-- 檢查分析阻塞資訊WHILE 1=1BEGIN   EXEC master.dbo.sp_blocker_pss80        -- or sp_blocker_pss08   -- Or for fast mode    -- EXEC master.dbo.sp_blocker_pss80 @fast=1   -- Or for latch mode    -- EXEC master.dbo.sp_blocker_pss80 @latch=1   WAITFOR DELAY ‘00:00:15‘ENDGOcmd執行命令：osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000參考資料：http://support.microsoft.com/zh-cn/kb/271509揭開隱藏資料的面紗，最佳化應用程式效能https://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx

　　

SQL Server常用的效能診斷語句