SQL Server clustered indexes are converted and invalidated in functions (case)

Recently in the first phase of optimization, the database of the most time-consuming first n statements to query out!

SELECT TOP 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 one is the most time-consuming, the statistical query

There are 2 rows of similar statistics, the query day data, are the same table, such as:

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 amount of data, and the field inserttime as a clustered index, but to see that the execution plan is not indexed, two subqueries have a parallel clustered index scan!

It takes nearly 20 seconds to query! ~

This is because the index key column is converted, and finally the date is changed to the following:

and DATEDIFF (Dd,inserttime,getdate ()) =0--changed to: and Inserttime>=convert (VARCHAR), GETDATE (), and Inserttime <dateadd (Dd,1,convert (Date,convert (VARCHAR), GETDATE (), 120))

Use clustered index successfully, less than 1 seconds! ~

SQL Server clustered indexes are converted and invalidated in functions (case)