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 |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2002-07-22 : 18:49:56
|
| Hi, I am creating some plans for my production system to maintain indexes. I have read update stats and dbreindex on bol. I think i fairly clear about the concept but I need to confirm if my analysis is accurate.Issue:Recently, my collegue did update stats on the db (not knowing what to do) because a 20 min payoll process was taking more than an hr to run.it resolved the issue then but when i checked fragmentation level (SHOWCONTIG) for the tables used by the process, it was very high.Most of the figures looked bad when I compared to the ideal ones mentioned in BOL.Analysis:1. If I have had regular maintenance DEFRAG and REINDEX plans executed, could I have avoided doing update stats?2. Update stats is set to auto-on then why did the process take long? a. because there could have been extensive read/write and the query optimizer had determined the stats were out of date so it included update action which caused the delay.note: hence, update-stats worked but is not necessarily the correct way to fix it? b. because I never did indexdefrag and dbreindex.3. how often do you guys have to do update stats on your dbs?4. the book says: 'using dbcc indexdefrag statement doesnot improve performance when indexes are physically defragmented on disk. to physically defragment an index, rebuild the index'.a) which parameter of dbcc showcontig tells me the physical fragmentation level?b) is physical defragmentation of pages = clustering of pages?c) is physical defragmentation of pages means non-sequential order of leaf-level/data pages on disk?thanks,sarat. |
|
|
|
|
|
update stats and dbreindex - need to confirm