SQL Server 叢集索引在函數中進行轉換以至失效(案例),
最近在進行一期的最佳化,先把資料庫中最耗時的前N個語句查詢出來!
SELECT TOP 10 qp.query_plan,qt.text,total_worker_time from sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp where qp.query_plan.exist('declare namespace qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //qplan:RelOp[@LogicalOp="Index Scan" or @LogicalOp="Clustered Index Scan" or @LogicalOp="Table Scan"]')=1 order by total_worker_time DESC
第一條最耗時的,是統計的查詢
其中有2行類似的統計是這樣的,查詢當天的資料,都是同一張表,如:
SELECT ISNULL(SUM(VALUE),0) AS VALUE FROM Mytab WHERE Status=1 DATEDIFF(dd,InsertTime,GETDATE())=0
這個表是其中資料量最大的表,並且 欄位 InsertTime 為叢集索引,但是看執行計畫是不走索引的,兩個子查詢都進行了並行叢集索引掃描!
查詢時耗時將近20秒!~
這是因為索引鍵列被轉換的緣故,最後把日期的判斷改為如下:
AND DATEDIFF(dd,InsertTime,GETDATE())=0--改為:AND InsertTime>=CONVERT(VARCHAR(10),GETDATE(),120)AND InsertTime<DATEADD(DD,1,CONVERT(DATE,CONVERT(VARCHAR(10),GETDATE(),120)))
成功使用叢集索引,不到1秒!~