Link to home
Create AccountLog in
Avatar of jbhat
jbhat

asked on

SQL Server Reindexing

Hello

We have a big database (50GB ) and everyday its growing by 200 to 300 MB and you can assume the number of transactions getting performed each day.

We are trying to index the tables nightly but reindexing entire database takes 1.5 hour and any transactions performed that time will have failures, errors, deadlocks etc. So we started reindexing only few transaction tables but still its taking 40-45 mins everyday to complete the reindexing and ofcourse the transactions performed that time will have failures.

So, Is there anyway to make the tables\performance faster?

Appreciate the advices.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You may need to do more than just re-indexing like UPDATE STATISTICS periodicaly and please see more details at:

https://www.experts-exchange.com/questions/27577626/Lost-with-Defrag-sproc.html

http://strictlysql.blogspot.ca/2010/06/rebuild-vs-reorg.html
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Or you can also go with reorganizing. Though it is slower process, but it will create minimal performance issue.
You are realy on sql2005 as your topic indicates?  If 2008 then you with filtered indexes you can taillor a set of indexes for different usage. Indexes for the recent history can then daily be reindexed and others on a slower period.

If 2005 and a full reindex takes 1,5h you can replace it by a smart reindex that only reindexes the indexes that have a large need to reïndex and can that reduce the duration.
http://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jbhat
jbhat

ASKER

Thanks for useful information shared