October 19, 2010 at 7:46 am
Hi All,
Lately I am seeing quite a bit of dead locks on the collection Set SQL 2008 provides. The stored proc that causes dead lock is EXEC [dbo].[sp_syscollector_purge_collection_logs] which is residing in MSDB.
-Roy
October 19, 2010 at 10:13 am
Roy,
What is it deadlocking?
What exactly is the question? @=)
October 19, 2010 at 10:53 am
There's a connect item on this. The workaround to this issue is to change the upload schedule - maybe that helps?
October 19, 2010 at 11:41 am
Winash, The connect item was put in long time back and the work around provided was by Bill if I am not mistaken. He was working with me when we first encountered this. Now it is back and the schedule is staggered just like he said we should set it so that it wont collide with each other. That stored proc uses cursor with a self join when deleting. It looks like it can be rewritten pretty easily. But it is part of system stored proc. That is why I am a bit vary of messing around with it.
Brandie, If you look at the jobs for the data collection, you will see that it consist of 5 jobs. The collection set2 that collects dead locks with the Collection set job that upload into the data warehouse.
-Roy
October 19, 2010 at 12:28 pm
Roy Ernest (10/19/2010)
Winash, The connect item was put in long time back and the work around provided was by Bill if I am not mistaken. He was working with me when we first encountered this. Now it is back and the schedule is staggered just like he said we should set it so that it wont collide with each other. That stored proc uses cursor with a self join when deleting. It looks like it can be rewritten pretty easily. But it is part of system stored proc. That is why I am a bit vary of messing around with it.Brandie, If you look at the jobs for the data collection, you will see that it consist of 5 jobs. The collection set2 that collects dead locks with the Collection set job that upload into the data warehouse.
The fix has to come from Microsoft and it doesn't seem likely for SQL 2008 considering that the connect item is still open and the comments there mention that the fix will come in a future version of SQL Server.
Maybe staggering the schedule even more will help? The amount of data being collected and uploaded could vary and perhaps sometimes deadlocks could occur for a schedule that works fine with lesser amount of data?
I would be wary of messing with system stored procs too - mainly because I don't know if that invalidates product support.
October 19, 2010 at 1:20 pm
Well, It does seem like that they are not giving much support for that part of the product.. 🙂 And the Sp does not seem to be such a complicated SP. The issue is that we have around 6000 batches per second and would like to collect all data I can on the performance matrix.
No one else has any other work around. I think I will try a rewrite of the sproc and test it in our QA with load testing and see if it will cause any dead locks.
-Roy
October 19, 2010 at 4:35 pm
Roy Ernest (10/19/2010)
I think I will try a rewrite of the sproc and test it in our QA with load testing and see if it will cause any dead locks.
If it works, send it to Microsoft for inclusion in the next service pack. Maybe they'll keep your name in it, and you'll become famous!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply