Sql-server – How to remove a specific bad plan from the SQL Server query cache

cachequerysql serversql-server-2008

We have one particular SQL Server 2008 query (not a stored proc, but the same SQL string — executes every 5 minutes) that intermittently caches a very bad query plan.

This query normally runs in a few milliseconds, but with this bad query plan, it takes 30+ seconds.

How do I surgically remove just the one bad cached query plan from SQL Server 2008, without blowing away the entire query cache on the production database server?

Best Answer

I figured out a few things

select * from sys.dm_exec_query_stats

will show all the cached query plans. Unfortunately, no SQL text is shown there.

However, you can join the SQL text to the plans like so:

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 here it's pretty trivial to add a WHERE clause to find the SQL I know is in the query, and then I can execute:

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

to remove each query plan from the query plan cache. Not exactly easy or convenient, but it appears to work..

edit: dumping the entire query cache will also work, and is less dangerous than it sounds, at least in my experience:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Related Solutions

Sql-server – Update Statistics actually decreases performance

As KPWINC said, "It really depends".

There are several more items which influence the answer to your question that a general answer may not be appropriate.

Microsoft MSDN has entries for sp_updatestats (http://msdn.microsoft.com/en-us/library/aa260337(SQL.80).aspx) and "UPDATE STATISTICS" (http://msdn.microsoft.com/en-us/library/aa260645(SQL.80).aspx)

To answer your last questions first:

You need to re-read the SQLServerPedia entry. Specifically:

Your index rebuild will automatically update the statistics for the index (with 100% sample size which is usually better then what you end up with when using sp_updatestats). After the index rebuild is complete you can use sp_updatestats to update the other statistics that require attention (2005+).

Looking at the UPDATE STATISTICS (2000) documentation says the following for sampling:

Note The default behavior is to perform a sample scan on the target table or indexed view. SQL Server automatically computes the required sample size.

Kimberly Tripp has put together a few blog entries on statistics, indexes, and database maintenance plans (http://www.sqlskills.com/BLOGS/KIMBERLY/category/Statistics.aspx). While the information may or may not be directly usable by you, the guidance of each blog entry should be and the referenced sites will probably be helpful.

Sql-server – Slow INFORMATION_SCHEMA query

Left joins are notorious for generating massive recordsets if circumstances permit. I've occasionally run into this sort of problem and I don't have a quick and easy answer. I've found that playing around with the query, e.g. by building up the query one join at a time, is the best way to work out which join is casuing the problem.

Have you tried doing a select count(*) from the tables in the query to see if one of them has an unexpectedly high number off rows?

More thoughts: it isn't clear so far whether the problem lies with the server or if the query is pathological and would kill any server. I suppose you could copy the database to a different server and try the query again.

How about modifying the query to be something like:

SELECT t.TABLE_NAME, ISNULL(pk_ccu.COLUMN_NAME,'') PK, ISNULL(fk_ccu.COLUMN_NAME,'') FK 
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN 
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_ccu
  ON pk_ccu.CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME 
)
 ON pk_tc.TABLE_NAME = t.TABLE_NAME AND pk_tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
LEFT JOIN
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_ccu
  ON fk_ccu.CONSTRAINT_NAME = fk_tc.CONSTRAINT_NAME
)
 ON fk_tc.TABLE_NAME = t.TABLE_NAME AND fk_tc.CONSTRAINT_TYPE = 'FOREIGN KEY'

I think the inner joins ought to be safe since the tc and ccu tables ought to have matching records. Does doing the query this way improve the execution time?

Best Answer

Related Solutions

Sql-server – Update Statistics actually decreases performance

Sql-server – Slow INFORMATION_SCHEMA query

Related Topic