SQL Server clustered indexes are converted in functions and even fail (CASE ),

SQL Server clustered indexes are converted in functions and even fail (CASE ),

During the latest phase I optimization, the first N most time-consuming statements in the database are queried!

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

The first most time-consuming query is the statistical query.

There are two rows with similar statistics. The data on the day of query is the same table, for example:

SELECT ISNULL(SUM(VALUE),0) AS VALUE FROM Mytab WHERE Status=1 DATEDIFF(dd,InsertTime,GETDATE())=0

This table is the table with the largest data volume and the InsertTime field is the clustered index. However, the execution plan does not go through the index. Both subqueries perform a parallel clustered index scan!

Query takes nearly 20 seconds !~

This is because the index key column is converted. The date judgment is changed to the following:

And datediff (dd, InsertTime, GETDATE () = 0 -- changed to: AND InsertTime> = CONVERT (VARCHAR (10), GETDATE (), 120) AND InsertTime <DATEADD (DD, 1, CONVERT (DATE, CONVERT (VARCHAR (10), GETDATE (), 120 )))

Clustered index used successfully, less than 1 second !~