查詢統計_ZenDei技術網路在線

資料庫引擎接收到一個新的查詢請求（Batch或SP），查詢優化器會生成執行計劃，並緩存到記憶體中；下次再次執行相同的查詢請求時，資料庫引擎從復用已經緩存的執行計劃，換句話，資料庫引擎為每一個查詢請求生成執行計劃，並把已經生成的執行計劃緩存起來，當接收到相同的查詢請求時，資料庫引擎復用已緩存的執行計劃。 ...

資料庫引擎接收到一個新的查詢請求（Batch或SP），查詢優化器會生成執行計劃，並緩存到記憶體中；下次再次執行相同的查詢請求時，資料庫引擎從復用已經緩存的執行計劃，換句話，資料庫引擎為每一個查詢請求生成執行計劃，並把已經生成的執行計劃緩存起來，當接收到相同的查詢請求時，資料庫引擎復用已緩存的執行計劃。查詢請求（Batch或SP）中的每一個查詢語句的執行計劃，都會被緩存到記憶體中，資料庫引擎統計執行計劃的性能參數，緩存在DMV：sys.dm_exec_query_stats中，在該視圖中，每一行數據都表示一個查詢語句的統計數據：

sql_handle：用以唯一標識一個TSQL文本（Batch或SP），TSQL文本存儲在SQL Manager Cache（SQLMGR）中；
plan_handle：用於唯一標識一個已編輯的查詢計劃，查詢計劃存儲在計劃緩存中；

一個sql_handle 能夠生成多個查詢計劃，對應多個plan_handle，但是每個plan_handle只能對應一個sql_handle 。

一，獲取查詢語句

視圖sys.dm_exec_query_stats 緩存的是單個查詢語句的執行計劃，而sql_handler引用的是整個TSQL文本（Batch或SP），為了獲得單個查詢語句的文本，必須通過語句的偏移欄位來抽取，偏移量是位元組，位元組數量從0開始：

statement_start_offset：語句開始偏移的位元組序號
statement_end_offset：語句結束偏移的位元組序號，-1 表示TSQL文本的末尾；

由於函數 sys.dm_exec_sql_text 返回的TSQL文本是以nvarchar(max)類型存儲的，一般情況下，位元組偏移量都是2的倍數，獲取查詢語句的腳本是：

select substring(st.text 
                ,qs.statement_start_offset/2+1,
                ( case when qs.statement_end_offset = -1 
                            then len(convert(nvarchar(max), st.text))
                        else (qs.statement_end_offset - qs.statement_start_offset)/2
                    end 
                )
        ) as individual_query
        ,st.text as entire_query
from sys.dm_exec_query_stats qs
outer apply sys.dm_exec_sql_text(qs.sql_handle) as st

View Code

二，查看統計數據的平均值

1，查看語句級別的統計數據

執行計劃的重編譯次數，執行查詢的總時間，邏輯讀和物理讀的次數等計數器，是觀察查詢執行情況的重要指標：

plan_generation_num：表示執行計劃產生的數量，表示同一個TSQL文本重新編譯的次數；
total_elapsed_time：單詞elapsed是指單個語句執行的總時間，包括 waiting的時間或 CPU工作（worker）的時間；
total_logical_reads：查詢計劃完成的邏輯讀的次數；
total_physical_reads：查詢計劃完成的物理讀的次數；

以下腳本用於查看語句級別的執行計劃的平均數據，並按照平均執行時間排序：

select top 111 
    qs.execution_count,
    qs.total_rows/qs.execution_count as avg_rows,
    qs.total_worker_time/qs.execution_count/1000 as avg_worker_ms,
    qs.total_elapsed_time/qs.execution_count/1000 as avg_elapsed_ms,
    qs.total_physical_reads/qs.execution_count as avg_physical_reads,
    qs.total_logical_reads/qs.execution_count as avg_logical_reads,
    qs.total_logical_writes/qs.execution_count as avg_logical_writes,
    qs.creation_time,
    qs.plan_generation_num,
    --st.text as entire_query,
    substring(st.text,
            qs.statement_start_offset/2 + 1,      
            ( case when qs.statement_end_offset = -1 
                        then len(convert(nvarchar(max), st.text))
                else (qs.statement_end_offset -qs.statement_start_offset)/2
              end)
            ) as individual_query
from sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 
order by avg_elapsed_ms desc

2，查看存儲過程級別的查詢統計

對於緩存的存儲過程，資料庫引擎把SP相關的統計數據緩存在視圖：sys.dm_exec_procedure_stats 中，每一行數據都表示一個SP的統計數據：

select top 111
    db_name(ps.database_id) as db_name
    ,ps.database_id
    ,object_schema_name(ps.object_id,ps.database_id)+'.'+object_name(ps.object_id,ps.database_id) as proc_name
    ,ps.type_desc as proc_type
    ,ps.cached_time
    ,ps.execution_count
    ,ps.total_worker_time/ps.execution_count/1000 as avg_worker_ms
    ,ps.total_elapsed_time/ps.execution_count/1000 as avg_elapsed_ms
    ,ps.total_physical_reads/ps.execution_count as avg_physical_reads
    ,ps.total_logical_reads/ps.execution_count as avg_logical_reads
    ,ps.total_logical_writes/ps.execution_count as avg_logical_writes
from sys.dm_exec_procedure_stats ps
where ps.database_id<32767
order by avg_elapsed_ms desc

View Code

對於database_id 為 32767，這個id是資源資料庫（Resource Database）預留的ID，一般情況下，用戶創建的資料庫ID都會小於該數值。

三，查看查詢計劃

函數 sys.dm_exec_query_plan 以XML格式返回指定batch或SP的查詢計劃，參數是plan_handle，這意味著，函數返回的是整個語句（Batch或SP）的執行計劃，而視圖sys.dm_exec_query_stats 緩存的是Batch或SP中某一個查詢語句的統計信息，在query_plan欄位上會出現大量的冗餘：

select top 111 
    qs.execution_count,
    qs.total_rows/qs.execution_count as avg_rows,
    qs.total_worker_time/qs.execution_count/1000 as avg_worker_ms,
    qs.total_elapsed_time/qs.execution_count/1000 as avg_elapsed_ms,
    qs.total_physical_reads/qs.execution_count as avg_physical_reads,
    qs.total_logical_reads/qs.execution_count as avg_logical_reads,
    qs.total_logical_writes/qs.execution_count as avg_logical_writes,
    qs.creation_time,
    qs.plan_generation_num,
    st.text as entire_query,
    substring(st.text,
            qs.statement_start_offset/2 + 1,      
            ( case when qs.statement_end_offset = -1 
                        then len(convert(nvarchar(max), st.text))
                else (qs.statement_end_offset -qs.statement_start_offset)/2
              end)
            ) as individual_query,
    qp.query_plan
from sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 
outer apply sys.dm_exec_query_plan(qs.plan_handle) as qp
order by avg_elapsed_ms desc

View Code

緩存的查詢計劃，被資料庫引擎緩存在視圖：sys.dm_exec_cached_plans，每一個查詢計劃都存儲一行，從該視圖中能夠查看緩存的查詢計劃及其文本，計劃占用的記憶體大小，以及查詢計劃被重用的次數等數據：

select cp.refcounts
    ,cp.usecounts
    ,cp.size_in_bytes
    ,cp.cacheobjtype
    ,cp.objtype
    ,st.text as entire_sql
    --,cp.plan_handle
from sys.dm_exec_cached_plans cp
outer apply sys.dm_exec_sql_text(cp.plan_handle) st

四，記憶體授予

記憶體授予只出現在特定的查詢語句中，如果一個查詢包含排序，Hash等操作，那麼該語句在執行之前，必須申請到必需的記憶體，這意味著，如果資料庫引擎不能分配足夠的授予記憶體，那麼查詢請求將不會執行。

視圖sys.dm_exec_query_memory_grants 返回當前資料庫中申請記憶體授予的狀態：

select mg.session_id
    --,mg.request_id
    ,mg.resource_semaphore_id
    ,mg.wait_time_ms
    ,mg.dop
    ,mg.requested_memory_kb
    ,mg.required_memory_kb
    ,mg.used_memory_kb
    ,mg.max_used_memory_kb
    ,mg.ideal_memory_kb
    ,st.text as entire_sql
from sys.dm_exec_query_memory_grants mg
outer apply sys.dm_exec_sql_text(mg.sql_handle) as st
order by mg.wait_time_ms desc

View Code

在申請記憶體授予時，資料庫引擎會發送資源信號（resource semaphore），視圖 sys.dm_exec_query_resource_semaphores 返回當前資料庫中查詢-執行的記憶體狀態，用於檢測當前資料庫是否有足夠的記憶體，用於所有的查詢計劃。

當記憶體緊張時，查詢請求申請不到足夠的授予記憶體，處於RESOURCE_SEMAPHORE等待狀態。此時，資料庫引擎發送資源信號（RESOURCE SEMAPHORE）申請授予記憶體（Requested Memory）。

當SQL Server實例接收到用戶的查詢請求時，查詢優化器首先創建編譯計劃（Complied Plan），根據編譯計劃再創建執行計劃（Execution Plan）。查詢優化器在創建編譯計劃時，需要計算查詢語句在執行時需要消耗的記憶體。用於執行查詢語句的記憶體分為必需記憶體（Required Memory）和額外記憶體（Additional Memory），必需記憶體是指SQL Server實例執行Sort或Hash操作必須分配的最小記憶體，如果沒有分配必需記憶體，查詢請求不會執行；額外記憶體是查詢語句用於存儲臨時的中間數據的記憶體，如果SQL Server沒有足夠的記憶體，資料庫引擎把臨時數據存儲在硬碟中，這會降低查詢語句執行的性能。

SQL Server 要授予多少記憶體，查詢請求才能真正開始執行呢？

Step1，計算需要的記憶體（Needed Memory）：SQL Server計算每個查詢需要多少記憶體才能執行，這通常是必需記憶體和額外記憶體之和，當查詢請求以併發方式執行時，需要的記憶體公式是：（Required Memory*DOP）+額外記憶體。
Step2，計算請求的記憶體（Requested Memory）：SQL Server檢查每個查詢請求需要的記憶體數量是否超出系統的限制，SQL Server減少額外記憶體的數量，以致於不會超出系統的上限，這個最終的記憶體數量是查詢語句得以執行的請求記憶體。
Step3，為查詢分配請求記憶體：SQL Server實例發送資源信號（RESOURCE SEMAPHORE），為查詢（Query）語句授予/分配請求的物理記憶體。

當資源信號發送之後，如果SQL Server實例不能被授予查詢的請求記憶體，那麼查詢請求處於RESOURCE_SEMAPHORE 等待狀態。SQL Server維護一個先入先出（ first-come-first-served）的等待隊列，當新的查詢請求處於RESOURCE_SEMAPHORE 等待狀態，SQL Server將該查詢放入隊列的末尾。一旦SQL Server實例找到足夠的空閑記憶體，那麼資料庫引擎取出RESOURCE_SEMAPHORE 等待隊列頂端的第一個查詢，立即授予其申請的請求記憶體（Requested Memory）；該查詢獲得請求記憶體之後，開始執行查詢任務。如果SQL Server實例長時間有查詢處於RESOURCE_SEMAPHORE等待狀態，說明SQL Server 面臨記憶體壓力。

參考文檔：