Search

Management Data Warehouse (MDW) purge job proc performance by Ben Seaman

Closed
as Duplicate Help for as Duplicate

2
0
Sign in
 to vote
Type: Suggestion
ID: 657148
Opened: 4/4/2011 4:11:14 AM
Access Restriction: Public
0
Workaround(s)
The standard job which gets created when you create a Management Data Warehouse repository (mdw_purge_data_[<database name>]) contains a single step to run a proc called

core.sp_purge_data

In my environment, which contains about 300 Gb of MDW data from 10 instances using only the default collection sets, the purge job runs for a very long duration (e.g. days) without doing anything - and usually ends up blocking inserts from the Data Collectors which means I have to kill the job and in turn means that no data is being purged. I analysed the execution plan of this proc and found that adding the index below makes the job run in one hour on my server.

I am on SQL Server 2008 R2 Enterprise with CU4: 10.50.1746
Details (expand)

Product Language

English

Category

SQL Engine

Proposed Solution

Perhaps this (or a similar index) could be included in the next patch to increase the performance of the purge job.

CREATE NONCLUSTERED INDEX [IX_query_stats_purge] ON [snapshots].[query_stats]
(
    [sql_handle] ASC,
    [statement_start_offset] ASC,
    [statement_end_offset] ASC,
    [plan_generation_num] ASC,
    [plan_handle] ASC,
    [creation_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Primary Benefit

Improved Performance

Other Benefits

 

Virtualization

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Sudarshan Narasimhan [MSFT] on 6/4/2012 at 1:48 PM
@Randy - SQL Server 2008 Service Pack 3 fixes this issue. I'm getting the KB 2584903, to add this information. Please apply just SP3 and that should solve this issue.

@wingman - Just applying SP1 for R2 will not fix this issue. Once SP1 is applied you will need to run the Configure Management Data Warehouse Wizard (step #2 in KB 2584903) from SSMS. Only when you do this, the MDW purge procedure code will change and you will find the new purge procedures.

@All - Please check on the KB in a week or so, as SQL 2008 will be part of the KB. Thanks.
Posted by wingman2468 on 3/15/2012 at 5:30 AM
Sudarshan, my server does have SQL Server 2008 R2 SP1 applied but I do not have the stored procedure you mention: "You will find that post-SP1 the purge procedure is called sp_purge_orphaned_notable_query_plan"

Please advise.
Posted by Randy in Marin on 8/5/2011 at 11:14 AM
I would like more info plaese. What patches do I need for SQL 2008?

This is from a recent trace. I don't normally see number like this. I wanted to list out the worst of the worst so that our staff could address poor query performance. I really expected to see our legacy code easily trump anything else running by a long shot. However, nothing we have comes close to this.

CPU: 405930 milliseconds
Reads: 192770303 (1.5 TB!)
Writes: 950817 (7 GB!)
Duration: 2010541996 microseconds

Thanks,
Randy
Posted by Randy in Marin on 8/4/2011 at 9:40 AM
Hi. If this is a duplicate, please provide a link to the original. I have this issue in production SQL Sevrer 2008 cluster. A trace on the job shows a whopping 42052159 logical reads (320 GB) and 123979 logical writes (~1 GB). I would like to know which fix I need to apply.
Posted by Sudarshan Narasimhan [MSFT] on 7/20/2011 at 4:38 PM
The issue has been fixed in SQL Server 2008 R2 Service Pack 1. The core.sp_purge_data procedure has been re-factored. You will find that post-SP1 the purge procedure is called sp_purge_orphaned_notable_query_plan.

Once you apply SQL 2008 R2 SP1, you need to further make the following modifications to the above mentioned SP as shown below,


SP1 Code
**********
        -- Deleting TOP N orphaned rows in query plan table by joining info from temp table variable
        -- This is done to speed up delete query.
        DELETE TOP (@delete_batch_size) snapshots.notable_query_plan
        FROM snapshots.notable_query_plan AS qp , #tmp_notable_query_plan AS tmp
        WHERE tmp.[sql_handle] = qp.[sql_handle]

Change this to following once you apply SP1
**************************************
-- Deleting TOP N orphaned rows in query text table by joining info from temp table
        -- This is done to speed up delete query.
        DELETE TOP (@delete_batch_size) snapshots.notable_query_text
         FROM snapshots.notable_query_text AS qt, #tmp_notable_query_text AS tmp
        WHERE tmp.[sql_handle] = qt.[sql_handle]
Posted by Microsoft on 4/19/2011 at 11:25 AM
Ben, Could you pleas etry the fix posted in our team blog http://blogs.msdn.com/b/sqlagent/archive/2011/04/12/data-collector-s-sp-purge-data-may-run-for-hours-if-there-are-many-orphaned-plans-to-purge.aspx

Thanks
Sethu
Posted by Microsoft on 4/12/2011 at 4:11 PM
Ben, Could you pleas etry the fix posted in our team blog http://blogs.msdn.com/b/sqlagent/archive/2011/04/12/data-collector-s-sp-purge-data-may-run-for-hours-if-there-are-many-orphaned-plans-to-purge.aspx

Please let us know if this addresses the problem you are facing


Thanks
Sethu Srinivasan
SQL Server
Posted by Microsoft on 4/11/2011 at 5:14 PM
Hello Ben,

Thank you for proposing this DCR - we are investigating and we will get back to you shortly.

Thanks,

Alex Grach
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement