asked
Index rebuild Job running Long why?
Hi Index rebuild Job running Long why?
Impossible to say without more information. It could be because your indexes are big. It could be that your tempdb is too small to support the sort operations, so there's a delay while it cycles information in & out of disk. It could be you don't have enough memory to do more of the sort operations in memory. It could be that you have contention on resources from queries or whatever running at the same time. There are probably more things I'm not thinking of. Run a query against sys.dm_exec_requests to see which processes are currently blocked or blocking. You can also see what processes are currently waiting on in terms of resources.
If you are using a maintenance plan you are rebuilding all your indexes every time, that is probably not necessary and another reason why your job is running long. So you should only rebuild your fragmented indexes, at what level of fragmentation was discussed here:[
http://ask.sqlservercentral.com/questions/103048/index-fragmentation-level.html][1] Have a look at Grant's comment on when to rebuild based on number of pages. If my guess is right about the maintenance plan you should have a look at another solutions. My favorite is Ola Hallengrens: [Olas Solution][2] Another community favorite is: [Michelles Solution][3] [1]:
http://ask.sqlservercentral.com/questions/103048/index-fragmentation-level.html [2]:
http://ola.hallengren.com/ [3]:
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
15 People are following this question.
