How to find the queries in cached memory of SQL Server and finding the query which was utilised mostly:
Cached Memory of SQL Server will have the queries which were executed recently in the server and its respective execution plan.
It works in FIFO manner, First IN First Out. Size of the Cache depends on the size of the physical RAM present and assigned to the SQL Server Instance.
When the Cache is full, least accessed query plan and its detailed will be flused from it to accomodate the new query and its plan details.
If the plan already exist in Cache then the sqlserver will not compile it again, it will directly execute the same and return the output to the user.
We can use "Recompile" explicitly to recompile the plan in the cache and then it will execute the query. Use "Recompile" as query hint option if needed rather than flushing all the existing plans in the cache.
Query used to pull the details :
SELECT total_worker_time/execution_count AS AvgCPU ,
total_elapsed_time/execution_count AS AvgDuration ,
total_elapsed_time AS TotalDuration ,
(total_logical_reads+total_physical_reads)/execution_count AS AvgReads , (total_logical_reads+total_physical_reads) AS TotalReads , execution_count , qs.creation_time,qs.last_execution_time,
SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt ,
query_plan FROM
sys.dm_exec_query_stats AS qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st
cross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.last_execution_time desc
Hope the above information helps
Thanks
