Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The rebuild of indexes takes appr. the same 55 min. with both "Do not rebuild indexes and "Rebuild indexes offline" "For index types that do not support index rebuilds" with "Keep index online while reindexing" checked

enter image description here

How can I measure/estimate which index are being rebuilt offline and during which time duration?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Well you can see which indexes are not built online by looking at the requirements for online index rebuilds. It isn't that the maintenance plan or database engine will arbitrarily decide which are online and which are offline.

To see which index is rebuild at what point, you'd have to profile the activity with something like sp_whoisactive, extended events or SQL Trace - but you should make sure you don't trace too much or cause too much load from your checking.

Also I notice that you are sorting your results in TempDB - is there a reason you've selected that? Have you noticed any issues in TempDB size with that set?

I know I've said this before in other answers to you so I apologize if this sounds like I'm hammering you here - but I would strongly suggest you look at a script like the Ola Hallengren maintenance solution. There are others - SQL Server MVP Michelle Ufford has also written one. These scripts do a few things that maintenance plans just don't do and the benefits seem to be exactly what you want:

  • They log very detailed information - These scripts will log to a table a bunch of data. Including fragmentation level before rebuild, exact time that index was rebuilt or reorganized, exact duration for that one table.

  • They only touch the indexes that need touching - Rather than just rebuild all indexes whether they need it or not (the maintenance plan approach), these scripts intelligently analyze fragmentation, and then make an attack plan based on the settings you provide in parameters to either ignore, only reorganize or rebuild based on fragmentation levels. This avoids you having to rebuild an index with only 5% fragmentation time after time. This will undoubtedly give you a benefit in terms of duration and impact.

  • Ease of scheduling - Maintenance plans have their place. But some of the questions I've seen from you on this topic keep falling around scheduling, multi-server, interaction with jobs, a desire for more control and visibility. These scripts, or even rolling your own based on their examples gives you exactly what you are looking for here.

share|improve this answer
    
Thanks. Just to note that maintenance plans are being run at nights when I am not at work (and do not have ability to access servers from outside of the working place), so I can only check some logged info. Maintenance plans were already set before me and rather by my bosses, so I have to stick with them for a while –  Fulproof Nov 23 '13 at 15:02
1  
Well you can setup jobs to collect data, but really you are somewhat blind with them. It is worth having the conversation with the bosses if you see benefits in the other solutions. Seems like you have had a lot of questions here so I imagine you have been spending a lot of time trying to make them fit what you need and want. –  Mike Walsh Nov 23 '13 at 15:08
1  
Good answer. My first thought was an XE because they're light. I'll work one up when I get near a computer. –  swasheck Nov 23 '13 at 17:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.