December 14, 2009 at 8:01 am
Alrighty - here's my situation.
I have a table which is accessed fairly heavily - I'd say, on any given day, a 150000+ accesses to the table. Each access is either updating or inserting a record, depending on whether it exists or not.
This table is very heavily fragmented as a result. Initially I had thought to rebuild the indexes twice a day, in order to keep everything running smoothly. Unfortunately, the rebuilding of the index takes around 10-15minutes, during which time the table is inaccessible.
What solution should I employ? For the time being, I've stepped it back to rebuilding the index three times a week, at midnight, but even with that setup, I still get around 1000-1500 timeouts when the indexes are being rebuilt.
I know with the Enterprise version (and others) you can rebuild indexes online, however, my version of SQL Server does not support this ability. As such, is there any other solution available, besides the one that I've employed already? Something which would either avoid having the index get fragmented so quickly, or ideally allow me to rebuild it more often without having the timeouts occurring?
December 14, 2009 at 9:15 am
Try ALTER INDEX ... REORGANISE
It does less that rebuild does, but it's an online operation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply