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://qa.sqlservercentral.com/scripts/Backup/62380/
@sqlfool: http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply