0

We have SQL Server 2008 R2 Standard on a Windows Server cluster. I want to defrag all indexes on a database.

What is the best approach to disable all connections and allow the rebuild of indexes?

I might not be able to shutdown web servers. I doubt single user mode would work as my connection might be kicked out. I also doubt restricted user mode would work as the application logs in as dbo.

One solution that comes to mind is disabling the SQL Server port but not quite sure. Any suggestions?

Edit: I think of setting the database as single_user\restricted_user and connect via DAC and run the defrag job from the command line. Do you think this would work?

  • 3
    You don't need to disable connections for defragmenting indexes, that's all online. For rebuilding, sure, you'll experience blocking for anyone trying to access those structures. But unless you plan on rebuilding all of your indexes in parallel from multiple sessions, it still seems overkill to take your database completely offline. Have you done any analysis that you need to defrag/rebuild? Are you sure you need it and that the resulting benefit will be worth it? What technique are you actually going to use to determine whether an index should be defragged or rebuilt? – Aaron Bertrand Oct 23 '15 at 13:05
  • Thanks Aaron. I definitely need to rebuild because fragmentation level of most indexes are above 50% - most of them even much higher ~90%. I believe you point out to "reorganising" when you say defragmenting - please correct me if not. I will use Ola's script for rebuild/reorganise with the default threshold of 30%. – Stackoverflowuser Oct 23 '15 at 13:53
  • And how many of these "highly fragmented" indexes are on small tables? How many rows/pages? – Aaron Bertrand Oct 23 '15 at 14:37
  • These highly fragmented indexes are on tables with more than 1000 pages. – Stackoverflowuser Oct 23 '15 at 14:50
5

Depending on your version of SQL Server, you can do index rebuilds as an ONLINE operation. This is an Enterprise feature.

Failing that (ie Standard Edition etc), look at re-organizing indexes - these will keep them online. Microsoft best practice says that you should generally not do a rebuild operation unless fragmentation is over 30%. You should always do some form of intelligent rebuilding - i.e check fragmentation and take an appropriate action. I can't recommend Ola Hallengren's maintenance solution enough for dealing with this.

https://ola.hallengren.com/

If you are worried about the blocking effect of index rebuilds, I would recommend this sort of approach.

| improve this answer | |
  • ...these will keep them online during the operations...ONLINE will have a lock at the beginning and at the end. – Racer SQL Oct 23 '15 at 13:19
  • 2
    In any case, from the question title: "on SQL Server Standard edition." – Aaron Bertrand Oct 23 '15 at 13:31
  • I will use Ola's script anyway but it doesn't work due to locking. – Stackoverflowuser Oct 23 '15 at 13:55
  • @Stackoverflowuser What do you mean "doesn't work due to locking"? – Aaron Bertrand Oct 23 '15 at 14:38
  • @AaronBertrand It just doesn't end. Maybe it's because standard edition is single threaded during index rebuild operations but I cut the operation in half last time I tried after several hours. It took ridiculously long for a ~130 GB database for ~100 indexes. – Stackoverflowuser Oct 23 '15 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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