一.查詢思路
1.想要判斷數據庫查詢緩慢的問題,可以使用如下語句,可以列出查詢語句的平均時間,總時間,所用的CPU時間等信息
SELECT creation_time N'語句編譯時間' ,last_execution_time N'上次執行時間' ,total_physical_reads N'物理讀取總次數' ,total_logical_reads/execution_count N'每次邏輯讀次數' ,total_logical_reads N'邏輯讀取總次數' ,total_logical_writes N'邏輯寫入總次數' , execution_count N'執行次數' , total_worker_time/1000 N'所用的CPU總時間ms' , total_elapsed_time/1000 N'總花費時間ms' , (total_elapsed_time / execution_count)/1000 N'平均時間ms' ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2) + 1) N'執行語句' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2) + 1) not like'%fetch%' ORDER BY total_elapsed_time / execution_count DESC;
2.列出數據庫每個表的數據量,並且需要運維人員對業務足夠了解,知道大概哪些表是查詢量最多的,可以查看“排在前面的表的磁盤使用情況”:
3.查看表碎片的情況,可以使用命令
DBCC SHOWCONTIG
可以看到該表掃描密度只有33.52%(最佳狀態是100%,每個表頁都寫滿數據),遠遠低於最佳計數,也就是說這個表的利用率很低,本來掃描一頁 就能出結果,現在可能需要掃描三頁,增加了查詢時間;而邏輯碎片和區碎片都很多(一般認為超過30%就需要優化了),也就是說同樣一頁,數據很少而碎片很 多,占用了過多的數據庫資源。
4.根據你對業務的了解,找出查詢最多的表,對比他的數據,查詢時間,和碎片程度可以判斷出該表是否需要整理碎片,重建索引,以提高數據庫性能。
重建索引的語句為:
use[數據庫名]
ALTER INDEX ALL ON [表名稱] REBUILD;
重建後,同樣的一張表NWME_Company_Index,再次查詢表碎片情況的結果如下:
可以看到密度已經變為96.9%,而邏輯碎片幾乎沒有了。
5.現在可以看一下整理碎片後,是否真的對查詢性能優化了,再次運行第一點列出的命令查看可以發現,大部分查詢語句所用的平均時間都下降了接近一半:
現在可以到前台實際體驗優化後的效果了。