Save off and put back stats in production

Question

Save off and put back stats in production

SQLBastian

SSC Journeyman

Points: 79
More actions
February 11, 2018 at 1:16 pm

#327414

First, I apologize if this is a duplicate post. I searched the forums and did not find what I was looking for. Google has also failed me.
I know in Oracle it is not uncommon to export statistics before updating stats so you can put stats back just in case the new stats have adverse effects.
I have a production SQL Server issue and both the vendor and Microsoft have asked me to update stats with a full scan. I don't believe the stats will fix the issue i'm having.... it's just something that flagged in the pssdiag/nexus report. I was hoping to follow what I learned in Oracle and export the existing stats so I can reapply them in needed.
I found how to export stats using the generate script wizard.
**all steps executed in POC environment**
1) I restored the database in a POC environment.
2) I generated a list of when stats for each object were updated.
3) I generated the stats script. This contained a multitude of "create statistics" statements.
4) I updated statistics with full scan. EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;'
5) As a user with SYSADMIN I attempted to execute the script generated by the wizard to put stats back. This resulted in several messages "There are already statistics on table '<table_name>' named '<stats_name>'.
6) I thought maybe I should drop the statistics first (with SYSADMIN privs). This resulted in several messages "Cannot drop the statistics '<table_name>', because it does not exist or you do not have permission."
7) I attempted to execute the script again to put stats back, but it resulted in the same message.
My google results have focused on moving stats from one machine to another, never back onto itself. Perhaps I'm missing a simple step? I was also wondering if maybe this is telling me the stats after the full scan are identical to the stats before the full scan was executed? Perhaps I need a step to force some change before attempting to run the script to put the original stats back? I will try that now, but will leave this post here in case someone has information that would be helpful.
I appreciate any thoughts/feedback.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 1

Have you tried this method? https://blog.sqlauthority.com/2011/01/05/sql-server-copy-statistics-from-one-server-to-another-server/

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 2

Unfortunately that is not officially supported by Microsoft - https://technet.microsoft.com/en-us/library/ms187348(v=sql.110).aspx
<update_stats_stream_option>

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

SQLBastian SSC Journeyman Points: 79 More actions · Answer 3

Evgeny Garaev - Sunday, February 11, 2018 1:41 PM
Have you tried this method? https://blog.sqlauthority.com/2011/01/05/sql-server-copy-statistics-from-one-server-to-another-server/

Thank you for the reply. Yep, I started with this post. Unfortunately it did not take be beyond exporting the stats (unless i missed something).

Thanks again 🙂

SQLBastian SSC Journeyman Points: 79 More actions · Answer 4

Evgeny Garaev - Sunday, February 11, 2018 1:45 PM
Unfortunately that is not officially supported by Microsoft - https://technet.microsoft.com/en-us/library/ms187348(v=sql.110).aspx
<update_stats_stream_option>
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Correct, the update statistics command has a few undocumented options. The update statistics command I'm using is documented. The create statistics command is also documented.

Just not sure what I'm doing wrong...

Thanks 🙂

SQLBastian SSC Journeyman Points: 79 More actions · Answer 5

OK, I see my error with the drop statistics command. The more I have been looking into this the more it seems setting statistics back to the original state is not a best practice for SQL Server. Having a difficult time finding more info around this topic. Lots of "how to" blogs on moving prod stats to a test environment, but nothing about putting them back into prod.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

SQLBastian - Monday, February 12, 2018 6:48 AM
The more I have been looking into this the more it seems setting statistics back to the original state is not a best practice for SQL Server.

Correct.

The general rule for statistics in SQL is 'as up to date and accurate as you can possibly get them'. Stale/inaccurate stats are a major cause of bad performance, that's why the support people will be wanting you to update them, to cross that possibility of their lists.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

SQLBastian SSC Journeyman Points: 79 More actions · Answer 7

GilaMonster - Monday, February 12, 2018 7:04 AM
SQLBastian - Monday, February 12, 2018 6:48 AM
The more I have been looking into this the more it seems setting statistics back to the original state is not a best practice for SQL Server.
Correct.
The general rule for statistics in SQL is 'as up to date and accurate as you can possibly get them'. Stale/inaccurate stats are a major cause of bad performance, that's why the support people will be wanting you to update them, to cross that possibility of their lists.

Thank you for the reply. This helps 🙂