Turbocharge Your Database Maintenance With Service Broker: Part 2

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    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]

  • Indianrock

    SSC-Insane

    Points: 20333

    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

    SSC Guru

    Points: 57030

    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]

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    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]

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    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[/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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply