Any recommendations on this - Manage Statistics ?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Any recommendations on this - Manage...

Any recommendations on this - Manage Statistics ?

Rate Topic

Display Mode

Topic Options

Author

Message

curious_sqldba

Posted Wednesday, January 30, 2013 1:42 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 1,178, Visits: 2,701

I would like to get some feedback on how other DBA's would manage db's in this situation:

We have databases (sql 2005 stnd version) being SAN replicated to a sql 2008 Server lets say server B. On server B there are ONLY select queries running against these DB', i have noticed that statistics are not being updated on server B( not sure why). The options on the db's on server B are Auto Update statistics,Auto Create Statistics and Auto Update Statistics Asynchronously are set to true. In this case i have few questions:

i) Are the above settings optimal for server B keeping in mind that ONLY select statements are run against it?

ii) Best way to update statistics (dbs/tables are huge)?

Thanks

Post #1413760

Balance

Posted Wednesday, January 30, 2013 2:48 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 463, Visits: 661

If the db on server b is selected from only then there is no reason why the stats would be updated so the auto settings are not going to make any difference.

You appear to be suggesting that your stats are incorrect (otherwise why do you need to update them) and that suggests the stats would be wrong on server A if you were to run the queries there so I would be asking about ensuring stats are correct on the source db and perhaps you are using the wrong sample sizes.

Post #1413774

curious_sqldba

Posted Wednesday, January 30, 2013 3:23 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 1,178, Visits: 2,701

Balance (1/30/2013)

My understanding is "sometime" statistics DO NOT get copied over to the target server when you do a backup/restore or some sort of replication at SAN level.

Post #1413780

Balance

Posted Thursday, January 31, 2013 1:33 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 463, Visits: 661

Interesting thought - if you are using SAN level replication then I can't see how the system would be able to differentiate stats data. But on backup / restore - I think it would be worth you finding a small table and testing that idea (and letting me know as I'm curious to know as it has implications for any recovery).

In my environment following the index rebuild/reorg process (where we specifically update stats on indexes reorganised) we also run sp_updatestats that goes through all the stats on a database and updates if necessary.

We still find though that some systems don't trigger enough updates so we have another routine that refreshes stats of more than a certain age.

Perhaps you just need to accept that updating the stats for these big tables is a necessary evil - working out the sample size for each statistic can make a big difference about how much time the process will take though and is worth spending time on (I'm comparing yourselves to my environment here where the developers aren't even aware of stats so have never taken the trouble to design them properly).

Post #1413895

opc.three

Posted Thursday, January 31, 2013 4:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 6,826, Visits: 11,951

A SQL Server Backup/Restore will absolutely restore whatever statistics were in place when the backup was taken from the source. A restore brings online a byte-for-byte representation of the database that was backed up, stats included, for better or for worse.

I would say the same for SAN replication but there are some creative SAN vendors out there so who knows, check your SAN documentation.

At the end of the day though you should consider updating stats at least one time after you bring the database online on server B. Executing sys.sp_updatestats might be all you need to run. If you have time go for a full scan:

EXEC sys.sp_MSforeachtable 
    @command1 = N'UPDATE STATISTICS ? WITH FULLSCAN;'

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1414000

« Prev Topic | Next Topic »

Permissions