Post reply

MSDB.dbo.sysjobhistory - smart to swap clustered/non clustered indexes?

  • August 12, 2011 at 3:38 pm

    #242138

    I have been tuning a few user tables and figured I'd check the MSDB tables since there is so much writelog, job, dbmail, and backup activity. I was curious if anyone ever swapped the clustered and non-clustered indexes on Sysjobhistory and saw a notable improvement. The clustered index seems very low use.

    databasetableindextype_descuser_seeksuser_scansuser_lookupsuser_updates system_Scans

    msdbsysjobhistoryclustCLUSTERED004814248593 190

    msdbsysjobhistorync1NONCLUSTERED3765341254660248593 19

  • August 15, 2011 at 1:35 am

    #1368463

    someone else may be able to clarify this but i think swapping the indexes may in fact invalidate support from Microsoft..........

    If performance of this table is an issue i'd look to either keep the history to a minimum or write the data to an archive table somewhere if you need to keep it.

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

  • August 22, 2011 at 3:42 am

    #1371305

    Personally, I would not touch any of the indexes supplied by Microsoft. This is to avoid any issues with them if you need their support for any issues within MSDB. They would be totally within their rights to insist you could reproduce the problem you are having using their supplied indexes before spending much time helping you.

    However, I have no issues in adding new indexes to system tables to help improve their performance. Many people run the following script to improve the performance of finding backup history when building a Restore specification.

    USE MSDB

    GO

    CREATE INDEX [ui_media_set_id] ON [dbo].[backupset]([media_set_id])

    CREATE INDEX [ui_restore_history_id] ON [dbo].[restorefile]([restore_history_id])

    CREATE INDEX [ui_restore_history_id] ON [dbo].[restorefilegroup]([restore_history_id])

    GO

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • August 6, 2015 at 9:25 am

    #1817902

    What about indexes on MSDB that are highly fragmented? Like sysjobhistory, index "clust". Do I need to manually reorg or rebuild? I'm not really finding anything on whether or not the syspolicy_purge_history job does the index cleanup?

    Changinagain

Viewing 4 posts - 1 through 3 (of 3 total)

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