Something that isn't often monitored by DBA's but should be is the contents of the Procedure Cache. In SQL 2005, this can be done through the DMV's, and a lot of data is available for a DBA to analyze by querying a single DMV:
sys.dm_exec_cached_plans
For each row returned, there is a plan_handle that will allow you to view the specific query that the plan exists for by using a CROSS APPLY of the following system function:
sys.dm_exec_sql_text()
We can see some important information about the queries that are or are not being properly cached in the Procedure Cache by the database engine. Microsoft has provided some scripts on the Scripts site that allow you to look at how efficiently your queries are functioning.
http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/proc/default.mspx?mfr=true
A problem which will be addressed in a later posting is cache bloat caused by the execution of adhoc, non-parameterized queries.
No comments:
Post a Comment