Purge orphan MDW query plans

The solution that Microsoft proposes to remove orphan query plans from the Data Collector was running too slowly.

After looking at the query plan, I created an index to help with the delete statements.  After this, it should run quickly.

CREATE NONCLUSTERED INDEX [ix_sql_handle]
ON [snapshots].[query_stats] ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time])

Using the second index, it runs very quickly.

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s