House Keeping - Statistics

• Jeff Moden

  SSC Guru

  Points: 1004415

  More actions

  March 2, 2016 at 8:13 am

  #1862398

  NicHopper (3/2/2016)

  ---

  Hi,

  I've not forgotten about this, I've just been busy, I'll do my best to get an article together with scripts in the next week or so.

  Sorry for the delay.

  Nic

  Hi Nic,

  Understood. I frequently have the same problem. Would it be possible for you to post the code here just to round out this particular thread? You can still publish an article even though you post the code here. Thanks.

  --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)
  

• Jon.Morisi

  SSChampion

  Points: 12846

  More actions

  March 3, 2016 at 10:22 am

  #1862835

  Just to add to the discussion...

  If the OP has Enterprise edition, enables trace flag 2371 and auto update statistics asynchronously, does this become a non-issue?

• tmisganaw

  Old Hand

  Points: 340

  More actions

  March 4, 2016 at 8:32 am

  #1863150

  share it to me please, here or to my tmisganaw@gmail.com

• NicHopper

  SSCrazy Eights

  Points: 9090

  More actions

  March 5, 2016 at 6:58 am

  #1863323

  Hi,

  Just an update on where I am with this, in short I've developed a database which we use for housekeeping, in my estate it resides on each server but you could probably put it on a CMS (not tried that though).

  Anyway it assesses the indexes and stats on each table in the target database, what's more it's fully customisable so you can set the index scan mode, page count and fragmentation limits (when to reorg and when to rebuild) for each database.

  The stats can also be customised at either the table, schema or database level, so I can say do full scan resample of schema dbo but then have dbo.TableA sampled at 20% and TableB at 50 ROWS and then let SQL decide the sample rate for the other schema's. It also only picks up the stats older than a specified age.

  What's more the database can also do consistency checks and record waits.

  There for rather than just throw it out there I want to do a document on why I did it and how to configure it (its not rocket science though).

  Hang in there, its on its way.

  Thanks,

  Nic

  Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/