Reindex tables and update statistics

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Reindex tables and update statistics

Reindex tables and update statistics

Rate Topic

Display Mode

Topic Options

Author

Message

DVSQL

Posted Friday, January 18, 2013 6:39 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:27 PM
Points: 41, Visits: 157

I've been digging in the database that supports our software because it's always been known for having slow performance. I've looked through the daily maintenance plan, and I see that there is normal stuff such as shrinking the database, and reindexing the tables and such, but I can't find update statistics anywhere. I was under the impression that if you don't update statistics after reindex, then database does not know how to use the new indexes very well, and it does almost no good. Can anyone please explain how the whole process works, and what would be the downside of reindexing the tables with out updating statistics. The command they use for reindexing is "dbcc dbreindex"

Post #1409143

DVSQL

Posted Friday, January 18, 2013 6:58 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:27 PM
Points: 41, Visits: 157

Just did some more checking, and it looks like database has Auto Update Statistics set True, so this may not be an issue.

Post #1409147

Lynn Pettis

Posted Friday, January 18, 2013 7:11 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 7:21 PM
Points: 21,832, Visits: 27,853

Shrinking the database, bad idea, especially if it is done after the index rebuild. Index rebuild rebuilds the statistics for the index. You should have a process the rebuilds statistics on a periodic basis, preferably with a full scan.

If you are using a maintenance plan, you don't have the granularity you really need. You should check out the routines available here: http://ola.hallengren.com/.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1409149

GilaMonster

Posted Saturday, January 19, 2013 2:39 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

An index rebuild updates the stats associated with that index as part of the rebuild operation

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1409167

Sean Pearce

Posted Monday, January 21, 2013 5:12 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 385, Visits: 1,417

DVSQL (1/18/2013)

I see that there is normal stuff such as shrinking the database

Shrinking the database is not normal maintenance stuff.

The SQL Guy @ blogspot

About Me

Post #1409498

« Prev Topic | Next Topic »

Permissions