Happy Holidays (and, this is why we’re keeping our day jobs!)

Happy Holidays from everyone at SQLskills!

(although, only Kimberly and Paul were stupid enough to be recorded singing this)…

Listen here (audio only): https://www.sqlskills.com/blogs/paul/content/binary/MerryChristmas.wav

(but don't say I didn't warn you. And, it is completely office safe! ;-)

Enjoy,
kt

8 thoughts on “Happy Holidays (and, this is why we’re keeping our day jobs!)

  1. Thanks guys. You’ve been good to me this year, without even knowing it.

    Happy Holidays,
    Steve

    PS : No one can sing like a Scotsman eh? :-)

  2. Hi Kimberly and Paul, I wanted to post this in the clustering key area, but I missed my commenting window apparently. Maybe you can move it for me. : )

    After the great impression Kimberly made at DevCon, we are back in the office feverishly studying how to migrate away from GUID-as-clustering-key in our many, many GUID-based applications. I might need a bit of help!

    Our applications are SELECT-intensive, at least 5 SELECTS for every INSERT or DELETE statement. But we do enough INSERTING to make our GUID-based tables pretty fragmented. Here are some recent statistics from a production table:

    Fragmentation of base table (clustered index on GUID column): 76%
    Average fragmentation of non-clustered indexes on this table: 77%

    To preserve application architecture, we want to retain the GUID as a primary key, while clustering on a new INT column not used by the application. Of course, a non-clustered index will now be in place on the GUID column, both to enforce uniqueness of the primary key, and to optimize SELECT * FROM TABLE WHERE LEGACYID = <GUID>.

    I practiced doing this on a non-production container today. I also ran 50,000 INSERT statements after the fact, to measure the improvement in contiguous pages. As expected, fragmentation of the base table remains under 1%, even after all these INSERTS. However, the fragmentation of our new non-clustered index covering the GUID column is 99%. I guess this is not a surprise.

    My colleagues are going to ask me whether we are really better off, if the new non-clustered index is so fragmented. The SELECT statement above is a pretty common operation.

    Could you clarify how fragmentation of base table affects SELECT performance before we commit ourselves? You proved in your talk that INSERT performance goes down the drain, but I probably need to deliver some facts around SELECT performance in order to be convincing. Whatever you can add would be helpful.

    Thanks,
    Blake

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.