using sort_in_tempdb when rebuilding indexes
-
Hello, I am trying to improve the performance of our weekly reindex job. It is currently taking 10 hours to complete using dbcc dbreindex and we were wanting to see if using the sort_in_tempdb option would help.
I am trying to issue it like so:
ALTER INDEX ALL on database.table REBUILD WITH(sort_in_tempdb = ON)
However I receive an error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INDEX'.
Ideally we want to have all the indexes in the entire database reindexed using this option. Is this the best way to go about this? I thought the statement would be simple enough, but I'm obviously missing something..
Thanks
-
Molly,
ALTER INDEX is a new statement in SQL 2005. Since you posted this in the 2000 group I assume that you're using SQL 2000 and that explains the error. Anyway the best way to reduce reindexing times is instead of rebuilding all indexes, just do those which are (heavily) fragmented.
Markus
[font="Verdana"]Markus Bohse[/font]
-
Thanks for pointing that out to me! Yes, we are using SQL2000. No wonder it wouldn't work
Unfortunately the indexes that are the most heavily fragmented are on the largest tables in the db. So they are what is taking the bulk of the 10 hours.
-
That is a 2005 syntax but can use sort_in_temp in SQL Server 2000 !
* Noel -
OK, Molly... I just gotta ask... you mean to tell me that you tried a command, that you got from somebody, on a production system without looking it up in Books Online?
I realize that you're caught between a rock and a hardspot, but that will seem small if you screw up the database or lock it up for 3 days... Sit down with Books Online and lookup the DBCC commands for reindexing and defragging before you run another "suggestion"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Uh Jeff, did you have a bad day? Nowhere did I say I was on a production database, and nowhere did I say I was stuck between a rock and a hard place. Currently the 10 hours is not interfering with day to day production. However this db will increase in size due to a merger that is slated to happen 2nd quarter next year. So I am being proactive in searching for a solution that will hopefully make this process quicker. Yes, I read books online, and yes I also look on the internet for ideas from others. This happened to be just one thing I tried where I didn't realize the information I read was specific to SQL 2005.
You made a lot of wrong assumptions in your unhelpful post. I hope that you have a better day today!
-
Heh... Not having nearly the bad day that you will if you keep trying unverified commands on your database (production or not)
That's the "help" you should have gotten from my post. 
Sorry I wasted your time.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply