Post reply

msdb indexes heavily fragmented - blocking on msdb.dbo.sp_jobhistory_row_limiter

  • March 27, 2012 at 11:13 am

    #391886

    I have noticed blocking involving msdb procedure sp_jobhistory_row_limiter and when I ran an analysis using the following query I found several large indexes are heavily fragmented:

    select OBJECT_NAME(object_id) as tableName,

    index_id, index_type_desc, avg_fragmentation_in_percent, fragment_count, page_count

    from sys.dm_db_index_physical_stats( DB_ID(), NULL, NULL, NULL, null )

    where page_count > 100 and avg_fragmentation_in_percent > 10

    order by avg_fragmentation_in_percent desc

    Msdb is not among the databases we regularly defrag.

    I wonder if we should be adding it to our regular schedule, but that would require an outage to stop the SQL Server Agent.

    How are people handling this? I would be interested in some input.

    __________________________________________________________________________________
    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]

  • March 27, 2012 at 1:01 pm

    #1465290

    I include msdb it in my index maintenance job. It does not require downtime, it may just block other processes while the indexes are being rebuilt...a normal side effect of index maintenance that SQL Server locking mechanisms manage for us.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 27, 2012 at 5:44 pm

    #1465417

    opc.three (3/27/2012)

    I include msdb it in my index maintenance job. It does not require downtime, it may just block other processes while the indexes are being rebuilt...a normal side effect of index maintenance that SQL Server locking mechanisms manage for us.

    Thank you, I will look into adding msdb to my defrag routines.

    __________________________________________________________________________________
    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 3 posts - 1 through 2 (of 2 total)

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