Grant Fritchey wrote:
Jeff Moden wrote:
I've not worked with Dynamics... is that one of those MS applications/databases that uses random GUIDs for keys?
With database designs that would make Codd break down weeping. They practically build them as a "how not to" manual. The negative example we should all avoid.
No lie, I once just went to the missing index tables and built all of them. It helped. I felt dirty for days.
I think I may be able to help. I have about 400 hours of testing I've done with Random GUIDs. The results of all that testing have been summarized in my “Black Arts” Index Maintenance #1: How the “Best Practice” Methods are Silently Killing Performance presentation and the fix is demonstrated in my “Black Arts” Index Maintenance #2: Better Methods that Can Actually Prevent Fragmentation for Weeks presentation.
The first session demonstrates how the use of REORGANIZE totally trashes the underlying page structure of all indexes that have a Random GUID as the leading column. Such trashing causes the indexes to go into a perpetual bad page-split mode. Ironically, just when the index starts to recover from that train wreck, the normal supposedly "Best Practice" index maintenance actually removes the critical free space required at and above the Fill Factor that is responsible for the very long term prevention of such page-splits. Using REORGANIZE on the fragmented indexes will cause MASSIVE amounts of blocking (a prime source of timeouts) and log file usage ESPECIALLY the day after such awful index maintenance is performed.
There is actually a way to make it so that such ALL page-splits (good or bad) are prevented for WEEKs and even MONTHs for Random GUID indexes. It goes against all current logic and supposed "Best Practices", which were only meant as a "starting point" (and is documented as such in Books Online).
Here's how to handle index maintenance for Random GUIDs and, I have to tell you, it bloody well works a treat.
- Step 1 is to first make sure that the SQL Server service hasn't been restarted for at least 3 weeks and that at least 1 month-end has occurred.
- Check all indexes using sys.dm_db_index_usage_stats for "User_Updates". REBUILD all indexes that show less than 5-10 User_Updates at 100% to mark them as "Static or Nearly Static" indexes.
- For all other Random GUID indexes in the system that don't qualify as "Static or Nearly Static" indexes, REBUILD them at 81% to mark them as "Evenly Distributed" indexes.
- Here comes the part that everyone disbelieves but I've proven works. Setup your index maintenance to REBUILD any index that has more than 1% logical fragmentation and run your index maintenance EVERY NIGHT! It will NOT rebuild all of your indexes every night. Because of the even distribution of Random GUIDs, the area of free space on all pages will slowly fill over time. Once the pages finally reach the point where they're full, they just start splitting and you MUST catch it just when that starts, which is at 1% logical fragmentation. While they're filling, the Non-Clustered indexes (which are generally narrow) will go for MONTHs with no page splits and absolutely ZERO fragmentation. The wider indexes and, certainly, Clustered Indexes (which are wider) will fragment more quickly but, depending on the insert rate of new rows, can easily go for several weeks with absolutely no page-splits and ZERO fragmentation. The three important things are NEVER use REORGANIZE on Random GUID indexes, do your index maintenance every night, and REBUILD as soon as an index goes over 1% logical fragmentation. If you wait any longer, you'll have the same massive page splits as if you did a REORGANIZE.
There are other issues that people have with all indexes whether they're based on Random GUIDs or not. One problem is with "ExpAnsive Updates" (you've got to find and fix those because even low Fill Factors might not be enough and low Fill Factors are almost totally useless for "ever-increasing keyed" indexes. The other is "in-row" LOBs, which can "trap" ultra low density pages (<30% full and even < 1% full) between wider rows caused by the in-row lobs. I have a presentation on that, as well. The fix is to change the table option to force storage of LOBs out of row and then do an "in-place Update" to force existing LOB data to go out of row. You also need to add a default of a single blank to such columns to prevent the GUID pointer from being "ExpAnsive" in nature.
You can get incredible performance gains when it comes to necessary or troublesome Clustered Index scans by doing that out of row thing. I've got almost a hundred hours of testing in that area, as well.
And, yeah... that's what I've personally implemented on some of my databases (along with much more because most of my databases don't even have a GUID column in them).
Ah... almost forgot. You also need a fairly aggressive stats maintenance plan. I went almost 3 years (as an extreme test) on one of my larger production systems without doing ANY index maintenance with only the occasional page density problem (a table that suffered a lot of DELETEs) with no real ill effects just by doing stats maintenance on an aggressive schedule.
I don't recommend NOT doing any index maintenance on Random GUID indexes because they'll settle out at a "natural Fill Factor" of about 68% and they WILL go into the "perpetual page split" mode. They MUST be maintained as I described or they'll continue to be a problem that you might not ever think of but will know the problem in the form of bloated log files and blocking.
is pronounced "ree-bar
" and is a "Modenism
" for R
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".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)