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: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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: Saturday, October 18, 2014 8:52 AM
Points: 599, Visits: 1,711
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: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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
Posted Wednesday, August 13, 2014 3:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
Here is a query that will give output to help kill broker-activated tasks:

SELECT
'KILL ' + cast (at.spid as varchar)
, at.spid
, DB_NAME(at.database_id) AS [dbName]
, at.queue_id
, at.[procedure_name]
, s.login_time
, s.[status]

FROM
sys.dm_broker_activated_tasks at
INNER JOIN
sys.dm_exec_sessions s
ON
at.spid = s.session_id;

Use this query when trying to kill conversations and unable to because you are being blocked by broker-activated sessions.


__________________________________________________________________________________

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 #1603024
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse