Thursday, September 22, 2016

Findind when the statistics have updated.


select si.name as index_name, STATS_DATE(si.OBJECT_ID,index_id) as statsupdatedate, OBJECT_NAME(si.object_id) as tablename,sch.name as scheemaname
from sys.indexes si, sys.schemas sch,sys.tables st where si.object_id=st.object_id and st.schema_id=sch.schema_id
update statistics owner.objectname

Tuesday, August 2, 2016

How to remove a specific execution plan from the SQL Server query cache

Below query will provide you the list of query plans available in the instance:

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
 
from the output select the appropriate plan_handle based on query text. and run below query
 
DBCC FREEPROCCACHE (plan_handle_id_goes_here)