Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sunilts
Starting Member
3 Posts |
 Posted - 2008-11-26 : 12:48:04
|
| Yesterday our Database server was very very slow.. usually query that takes like 2-4 min; was running for more an hours without returning any results... I checked the fragmentation information for the data and indexes using DBCC CHECKCONTIG and everything seems to fine.Last night , I REBUILT all the indexes using DBCC DBREINDEX; everything was back to normal.DB size is really big (75GB) and I was doing some maintenance work last week (INDEXDEFRAG), will that cause any slowdown in the DB.or is this due to the corrupted index or something like that...Please let me know if anyone know what the problem is.. |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2008-11-26 : 12:59:18
|
| Indexdefrag (which I wrote) removes fragmentation - doesn't cause problems itself.Given that you rebuilt the indexes and things improved, and you said that fragmentation wasn't an issue, you most likely had out-of-date statistics. Doing an index rebuild will rebuild your statistics with the equivalent of a full-scan - allowing the query optimizer to choose more efficient query plans.Do you have auto-create-statistics and auto-update-statistics database options on? In the vast majority of cases they should be.ThanksPaul S. Randal, SQL Server MVPAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database courseManaging Director, SQLskills.com (www.SQLskills.com/blogs/paul) |
 |
|
|
sunilts
Starting Member
3 Posts |
Posted - 2008-11-26 : 13:17:22
|
| Thanks Paul.Auto Create Statistics & update statistics is turned on in the DB |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 13:43:59
|
| Also see this from Paul's Blog if you haven't. It is informative.http://www.sqlskills.com/BLOGS/PAUL/category/Indexes-From-Every-Angle.aspx |
|
|
|
|
|
|
DB very slow. bt workd like magic aftr Reindexing