SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Turbocharge Your Database Maintenance With Service Broker: Part 2


Turbocharge Your Database Maintenance With Service Broker: Part 2

Author
Message
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12954 Visits: 3766
Comments posted to this topic are about the item Turbocharge Your Database Maintenance With Service Broker: Part 2

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Indianrock
Indianrock
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5275 Visits: 2460
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.



Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12954 Visits: 3766
Thank you for the link and for sharing your experience.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12954 Visits: 3766
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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12954 Visits: 3766
A better way of terminating all Service-Broker activity in a given database:

ALTER DATABASE [dbName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;



See:
http://stackoverflow.com/questions/22392341/alter-database-db-name-set-enable-broker-with-rollback-immediate
http://stackoverflow.com/questions/6931001/how-to-quickly-shut-down-all-elements-of-microsoft-sql-server-service-broker

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search