Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Turbocharge Your Database Maintenance With Service Broker: Part 2 Expand / Collapse
Author
Message
Posted Sunday, November 10, 2013 10:55 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:13 AM
Points: 1,860, Visits: 3,597
Comments posted to this topic are about the item Turbocharge Your Database Maintenance With Service Broker: Part 2

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1513003
Posted Monday, November 11, 2013 5:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:59 PM
Points: 596, Visits: 1,678
Just discovered this article and plan to read thoroughly. For several years we used a technique to update stats in parallel with as many as four threads, using an approach similar to this: http://www.sql-server-performance.com/2007/parallel-stats-update/2/

Over time we began to get the 15-second IO wait messages in the sql log, mostly in tempdb due to sorting very large indexes with a sample rate of 80%. Dropping back to just a single update stat process, no parallel processes, got rid of most but not all of those IO messages during update stats -- we've been using 80% sample for years.

So I began testing and found 1) update stats will run parallel threads only at 100% sample, unless other means are used such as your Service Broker approach. I suspect 100% will also get rid of our IO messages once adopted, and I presume this is due to removing the need to sort in tempdb at sample rates below 100%.

2) Until I lowered sample rate down below 30% it actually took less time to update stats at 100% Since a sample rate that low won't work in our database, we can't use sample rates that low -- many queries run too slowly.
3) Running a batch of 400 queries captured from production ( all of which took 5 seconds or more to run in production) after updating all statistics was generally fastest at 100% sample. For those tests I updated all stats ( not just those with modified rows ) at 100%, 80, 50 and 30 percent for each run of the query batch.
I ran the query batch both with a complete cache clear and then without.



Post #1513095
Posted Monday, November 11, 2013 10:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:13 AM
Points: 1,860, Visits: 3,597
Thank you for the link and for sharing your experience.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1513203
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse