April 8, 2015 at 7:18 pm
Hi All,
I just did index defragmentation for some databases include MSDB . I notice there are 3 indexes from MSDB database that fragmented quickly ( I did rebuild last nite at 10 PM - > fragmentation level becomes zero but today at 9 am it become 80 % ).
The indexes are backupsetuuid, backupmediafamilyuuid, backupmediasetuuid.
I am thinking to set the fill factor for those indexes = 80 respectively.
Please kindly advise on this case. Much Appreciate it .
Cheers….
April 8, 2015 at 8:19 pm
How many pages do those indexes consist of? If it's less than 8 pages, you shouldn't even bother trying to defragment them at all because it usually won't work. If it's less than 100 pages, again, I wouldn't bother. The suggested number is 1000 pages or less shouldn't be defragmented.
Very small tables, especially those that fit on less than one extent (8 pages) fragment extremely quickly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2015 at 10:53 pm
Thanks for the reply , Appreciate it !
I just checked the number of pages is 500 something for two indexes and the other one is 1000 something .. So should I set fill factor = 80 for that particular index which above 1000 pages ?
Cheers
April 9, 2015 at 4:33 am
Fill factor is a matter of experimentation, not size. If it's fragmenting fast, you might want to set it to 70 and see how that goes. Just remember, the smaller the fill factor the more space you're using on disk. Also, fragmentation only slows you down if you're doing lots of scans. If you're doing point lookups, it's no big deal. Since these are msdn tables, I doubt they're getting accessed all that frequently and, they're probably only providing point lookups. I'm not sure I'd sweat fine tuning them that much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2015 at 9:38 am
I think those are also uniqueidentifier columns, so fragmentation is to be expected. Unless you retain lots of backup info in msdb -- and I would not recommend that -- I agree that you really shouldn't spend too much time on it. Lower the fillfactor and be done with it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2015 at 9:38 am
I think those are also uniqueidentifier columns, so fragmentation is to be expected. Unless you retain lots of backup info in msdb, and I would not recommend that, then I agree that you really shouldn't spend too much time on it. Lower the fillfactor and be done with it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2015 at 12:18 am
Guys thanks so muchhh for the explanation ?...
Btw i hv just posted a new topic about “ Database can’t be accessed “ but no one replied yet ? . I would really appreciate if any of you know about that area and kindly replied it please..
Thank you
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply