Update statistics after Index Rebuild ?

Question

Update statistics after Index Rebuild ?

Viewing 6 posts - 31 through 35 (of 35 total)

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

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

Jeff Moden (8/18/2016)
I'm toying with the idea of just dropping all the stats that have the minor nuance in the name that I spoke of and letting the system sort it out.

That's exactly what I thinking when I read your problem. Drop all the column stats, make sure auto_create_stats is on and let the optimiser recreate what it needs. You'll get a minor performance overhead initially (the stats creation), so maybe do it table by table over a few days if it's a performance-sensitive DB.

Edit: and if you're going to go for the 'nuke from orbit' approach, drop the system created stats as well and let the QO recreate them if it needs.

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

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

BLOB_EATER (8/18/2016)
Grant Fritchey (8/18/2016)
Jeff Moden (8/18/2016)
It also names the stats in a fashion almost identical to how SQL Server does it
BWA-HA-HA!
I was just having a conversation about statistics in India last week when naming conventions came up. Several of us joked about how we should follow the Microsoft standard. Who the hell knew we were being serious? That's perfectly marvelous (and totally messed up).
Does the WA in "WA_XXXX" stand for Washington?

Yes. State of Washington (where Redmond is), not Washington DC.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

GilaMonster (8/18/2016)
Jeff Moden (8/18/2016)
I'm toying with the idea of just dropping all the stats that have the minor nuance in the name that I spoke of and letting the system sort it out.
That's exactly what I thinking when I read your problem. Drop all the column stats, make sure auto_create_stats is on and let the optimiser recreate what it needs. You'll get a minor performance overhead initially (the stats creation), so maybe do it table by table over a few days if it's a performance-sensitive DB.
Edit: and if you're going to go for the 'nuke from orbit' approach, drop the system created stats as well and let the QO recreate them if it needs.

Thanks, Gail.

One of the things I'm concerned about is that, way back in SQL Server 2000, I used to see a fair number of "errors" in the actual execution plans stating that statistics were missing which, I assume, meant that SQL Server didn't actually build stats for some reason other than not having auto_create_stats enabled. Do you think there's any chance of that happening in 2012 with the nuke method I'm considering?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

I don't know why that warning sometimes appears. It might be the QO needs multi-column stats, which will not be manually created. I need to research that sometime.

I'd say nuke it and monitor carefully. Doing it a table at a time, while tedious, should ensure you can fix anything that breaks in the process.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

GilaMonster (8/19/2016)
I don't know why that warning sometimes appears. It might be the QO needs multi-column stats, which will not be manually created. I need to research that sometime.
I'd say nuke it and monitor carefully. Doing it a table at a time, while tedious, should ensure you can fix anything that breaks in the process.

Doing it a table at a time is going to be a whole lot less tedious than the method I had for examining execution plans over time and then nuking what didn't appear to be used over a month or two and possibly still getting it wrong. 😀

I'll start with a handful of the smaller (low column count, as well) but frequently used tables and see what happens.

I really appreciate the feedback, Gail. Thank you. I'll let you know how it turns out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

Please do Jeff (reporting back on how it went) - Have a mess of stats on our production reporting server as well and while I've entertained that idea...haven't had the guts to actually try it out in fear of the fall out 🙂

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience