January 30, 2013 at 1:42 pm
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
January 30, 2013 at 2:48 pm
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.
January 30, 2013 at 3:23 pm
Balance (1/30/2013)
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.
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.
January 31, 2013 at 1:33 am
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).
January 31, 2013 at 4:53 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply