May 14, 2014 at 9:17 am
My Index maintenance script goes through all the databases on the instance and ranks the indexes by most fragmented, then works it way through the list. This works fine for most of my instances. But I have a few that are large and the script will spend 5+ hours just compiling that list, before it starts to do any "work", then the work extends beyond the maintenance window. Have any of you with large databases run into this and solved it? Or can you give me a shove in the right direction towards a process to handle this situation.
Thank you
May 14, 2014 at 9:23 am
We've used Ola Hallengren's maintenance script or @SQLFool's script here. You might try theirs on your system and see if it works faster.
Ola: http://www.sqlservercentral.com/scripts/Backup/62380/
@sqlfool: http://sqlfool.com/2010/04/index-defrag-script-v4-0/
May 14, 2014 at 10:50 am
I'm assuming you're getting the fragmentation information from sys.dm_db_index_physical_stats. If so, are you using the DETAILED mode to gather your metrics? I've never seen SAMPLED or LIMITED take very much time at all. But DETAILED can be very time consuming, not to mention, subject to contention and even causing contention of it's own.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply