Microsoft Recommendations for Update Stats?

Question

Post reply

Microsoft Recommendations for Update Stats?

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Brandie Tarvin wrote:

Oh, and can I get a favor? Can everyone to update their posts with the proper spelling of my name? Brandie.

Sorry and done.

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

Bumping my own post because of the 2nd page issue on this forum.

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

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

Brandie Tarvin wrote:

Wow. Just ... WOW. Watched the 1.2. Now going to watch 3.
Thanks, Jeff.

That's the same reaction I had when the size charts gave me a hint about what was happening (the "flats" especially) and then I made the little 10 frame "movie" of IndexDNA™ on the index that was being REORGANIZED. I've always wondered why GUIDs fragmented so quickly. It's because REORGANIZE removes free space below the Fill Factor and does NOT recreate any free space above the Fill Factor like so many people (including a younger me) thought.

I've established 7 different fragmentation patterns and numbered them, also thanks to IndexDNA™. Oddly enough, the numbering is the same thing that I set the Fill Factors to for easy identification. I also have code that is pretty good at determining what the fragmentation type is for all the RowStore indexes in a database is... I'm just not ready to release it yet.

And, no... I still don't rebuild indexes on a regular basis. I only do it when I can recover a couple hundred GB of disk space for some of the larger tables. I rebuild stats on some databases every night and others just once per week. I'm setting up to change most of those from FULL SCAN to just a 10 percent scan in most cases.

Also, the version of IndexDNA™ that I provide doesn't work on filtered row store indexes but it's a whole lot faster that the one that used DBCC IND. Also and contrary to belief, DBCC IND does do blocking on certain high use tables near the end of the run. The new function they came out that supposed does the same thing has a flow in it that it's missing many page numbers from the Leaf Level of larger indexes. I haven't made the workaround for that yet. Not sure it's worth it, either. It's just as slow as DBCC IND on larger tables.

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