November 9, 2015 at 9:04 am
Good day,
Wondering why my sysjobhistory index on MSDB is staying at 96% even after I rebuilt it? My monitoring tool is giving us annoying warnings and I would like to prevent this from happening.
Thanks for the help
November 9, 2015 at 9:21 am
How big is the index? If the index is very small, you have very few chances of getting low fragmentation.
-- Gianluca Sartori
November 9, 2015 at 11:54 am
It says 500KB.. what is the recommended size?
November 9, 2015 at 1:45 pm
I tried to run rebuild on my system and it worked fine, listing steps so that you may see if you missed anything. Note that my sql version is sql 2012 let me know if yours is different as this should work fine with 2012 and above
1. check the fragmentation on the table
SELECT db_name(a.database_id) as DatabaseName, object_name(a.object_id) as ObjectName, a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'msdb'), OBJECT_ID(N'dbo.sysjobhistory '), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
2. Rebuild index (adjust fillfactor as per your requirement)
ALTER INDEX ALL ON dbo.sysjobhistory
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
3. Run query in step 1 to ensure that the fragmentation is reduced.
NOTE : rebuild index may not work if the table is being actively used, so you should try it in off business hrs.
November 10, 2015 at 2:52 pm
sgrimard (11/9/2015)
It says 500KB.. what is the recommended size?
This fairly old whitepaper on index maintenance recommends skipping maintenance for indexes smaller that 1000 pages.
1000 pages * 8Kb per page = 8 Mb.
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply