November 10, 2013 at 10:55 pm
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2013 at 5:46 am
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.
November 11, 2013 at 10:54 am
Thank you for the link and for sharing your experience.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 13, 2014 at 3:10 pm
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 30, 2015 at 9:22 am
A better way of terminating all Service-Broker activity in a given database:
ALTER DATABASE [dbName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
See:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply