Constant re-indexing of db

We have a database that on a daily basis suffers from poor performance.

Our IT folks found through trial and error that running a complete DBCC reindex job on the database at those times acts as a "release valve" and the server / database start performing normally again.

I don't think that the OLAP activity on this database is anything major so not a whole lot of data is being added. Plus page splitting activity is minimal at the time so I don't think the indexes need to be re-built.

I was thinking that maybe the re-index job is causing deadlocks and therefore eliminating blocked processes. But sp_locks show nothing is being blocked prior to running the re-index job.

Can you throw ideas about how re-indexing or its byproduct effects could be affecting database performance?

Also, what should I trace? What additional logging can SQL put into the errorlog? What are performance impacts in production of performing TRaces of that kind?

This wouldn't be a denial of service type attack would it?

Please send all your suggestions and I'll look into them.

Thanks

...Ray

See if this helps: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||A side-effect of creating an index is to create fullscan statistics. You may actually be suffering from statistics that are not getting updated often enough, and thus be getting bad query plans since the optimizer is making decisions with stale information. Be sure that auto-create and auto-update statistics are enabled. If that is not good enough, try running sp_updatestats nightly. If you can isolate just which tables have bad stats, then you can frequently update the statistics on those tables. If sampled statistics (the default) don't give you the desired results, then consider updating statistics with FULLSCAN.

See this white paper regarding statistics in SQL Server 2005.

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx