To Remove Unused cache entries, I first inspect cache and workload evidence. My original intervention followed other customer tuning work.
Table Usage in the Plan Cache: Count Queries That Touch a Table
Table usage in the plan cache shows how many times the cached queries of a database touched each table. This post builds a demo database, shows why a text search of the cache misleads, reads the table names from the plan XML, and lists the queries behind one table.
When was Stored Procedure Last Compiled? – Interview Question of the Week #292
Question: When was Stored Procedure Last Compiled?
Answer: Honestly, it is difficult to figure out when was the stored procedure last compiled.
Give Memory Back to the OS? Why SQL Server Doesn’t
This is a guest post by Brian Gale. It explains why SQL Server doesn’t give memory back to the OS, what the buffer pool keeps in that memory, and why max server memory needs a realistic value. Two queries show what is held right now.
SQL SERVER – SQL_NO_CACHE and OPTION RECOMPILE Target Different Caches
OPTION (RECOMPILE) and SQL_NO_CACHE concern different cached objects. A banking customer using MySQL and SQL Server prompted my comparison.
Batch Requests and Compilations per Second From T-SQL
Calculate compilations per second from cumulative SQL Server counters, compare batch rates, and investigate single-use plans with context.
What is the Difference Between sql_handle and plan_handle?- Interview Question of the Week #269
Question: What is the Difference Between sql_handle and plan_handle?
Answer: I was recently asked this question. Let us see the answer today.