Reindexing

[DBAWinnipeg]

Good Morning

We have a table of approx 1 million rows that gets accessed by hundreds of users.

We reindex the table every 4 hours or so or querying against the table gets VERY SLOW.

Obviously when we reindex it locks the table which slows people down. So I'm in a lose/lose situation. If we don't reindex it's VERY SLOW and when we do reindex it blocks users.

This is a 24/7 operation so "off-hours" don't exist.

Any ideas?

I guess there is no wonderful way to reindex without locking?

Any help would be greatly appreciated


Thanks in advance!!!

Colin in da 'Peg

 

[JohnDTampaBay]

I assume you are using DBCC REINDEX. Check out DBCC INDEXDEFRAG in BOL. It says, "[DBCC INDEXDEFRAG] does not hold locks long term and thus will not block running queries or updates."

You would have to do some testing, but perhaps you could run DBCC INDEXDEFRAG every 3 hours, allowing you to run DBCC REINDEX less frequently. This is a common quandary. I am curious to hear your solution. Good luck!

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)