REINDEX and CALU STATISTICS

[Firecat1970]

Hello friends,

My database has daily update statistics. And I have never done any REINDEX for the last 2 years.

When I did some DBCC SHOWCONTIG on some major tables (~1 mil records per table), I saw a rather low Scan Density %, high logical scan fragmentation %, about 50% page density

I was wondering if a total REINDEXING would make things faster, I did and it of course shrink a lot of the indexes and make their average density higher. The DBCC SHOWCONTIG is showing better values... but when I execute some long running reports, it is not showing too much of an improvement.

Can I say that daily/weekly UPDATE STATISTICS is good enough, while REINDEXING can be done say twice a year?

Cheers!

 

[jgoodman00]

I am not sure if you have a subscription to SQL Server Magazine, but they had quite a good article back in October.

It might be worth running the indexing wizard to actually check that your indexes are optimally placed on the tables in question.





James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[Firecat1970]

Yes, I was reading Oct 2004 issue on "Automatic Reindexing", and that's why I did all these test.

I will try the Indexing Wizard next. Gotta gather some trace for that.

Many Thanks!

 

[jgoodman00]

Gotta gather some trace for that.

Not necessarily. If the reports are the only bottleneck, then execute them from QA & use the indexing wizard based on the contents of the QA window.



James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[hmckillop]

Havent read the article on automatic reindexing, but I have been through the pain of trying to ensure indexes are optimal and system performance is not degraded also.
I created a FAQ on it - have a look at

http://www.tek-tips.com/faqs.cfm?fid=5367

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Firecat1970]

Thank you guys, I am trying the QA+Indexing Wizard now, and thank you for the FAQ. This is an excellent starting point for my maintainence job.