Have you ever worked with a piece of software for like 15-20 years and ended up doing something with that product that you haven’t or can’t remember doing ever before?
This happened to me quite recently and although I wont have a bunch of sexy scripts for you to reproduce the problem I will go through what I learned/remembered you can do with statistics.
- Production database at a customer site is choosing an absolutely shitty execution plan (not using an index as expected).
- Can’t get a backup
- Don’t want to “cowboy” it up on a live 24/7 $$$$$$ production system
- Can’t reproduce in development with an old backup that we have on site.
Trying to determine “why” this is happening was proving to be a rat-hole until I came across this:
specifically the part that said showed that you can run this:
DBCC SHOW_STATISTICS ([TableName], [StatisticsName]) WITH STATS_STREAM
What this UNDOCUMENTED/UNSUPPORTED/BE VERY CAREFUL USING/GREAT POWER BLAH BLAH BLAH is doing is this is actually scripting out your statistics and the histogram
Now that you have the statistics from (in my case) the production database that I cannot get a backup of, or actively troubleshoot against, etc I can then run the following statement on my development server (which has an older backup of the database in question restored)
UPDATE STATISTICS [dbo].[TableName]([StatisticName]) WITH STATS_STREAM = 0x01000000010000000000000000000000B242E5900000000000038......etc., ROWCOUNT = 159543018, PAGECOUNT = 2904894
And BOOM… we were able to reproduce and establish the root-cause of the issue.
After googling around for blog posts about STATS_STREAM I did find the following as well:
Anyways this was just one of those moments where I got all excited and had that awesome feeling about trying something new and having it work.
Now that SQL Server 2016 is available to download for everyone I imagine I’ll have more “a-ha” moments
(If you don’t get the reference you’re young and I hate you and get off my lawn!!!)
Enjoy!! (Follow me on Twitter: @ColinStasiuk)