Rotating Header Image

Do You Even Blog Bro? (Stats, Execution Plans, Indexes, and crazyness)

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?

joygasm

 

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.

Scenario:

  • 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.

SadPanda

Trying to determine “why” this is happening was proving to be a rat-hole until I came across this:

http://blog.kejser.org/the-ascending-column-problem-in-fact-tables-part-two-stat-job/

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

dsjkfjdsoks

 

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
GO

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

Aha

(If you don’t get the reference you’re young and I hate you and get off my lawn!!!)

Thanks!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

One Comment

  1. The selfie camera also got better in the iPhone 7. Resolution increases to 7 megapixels, from 5 megapixels, so the selfie camera no longer feels inferior.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.