Comments

12/24/2010 11:05:35 AM #

Absolutely LOVED it!

Ken

12/24/2010 11:10:48 AM #

I think my singing is definitely worse than yours...

paul

12/24/2010 11:11:42 AM #

Thanks Ken! We were in quite the mood this am!!

Happy Holidays!
kt

Kimberly L. Tripp

12/24/2010 12:08:02 PM #

Trackback from Codebix.com

Codebix.com - Your post is on Codebix.com

Codebix.com

12/24/2010 1:10:56 PM #

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?

Steve

12/24/2010 1:33:23 PM #

Happy holidays, both of you. What has been heard cannot be unheard ;)

Just kidding, 't was very funneh

Emil Friðriksson

12/24/2010 3:16:51 PM #

Ha... Emil - I'm sorry... we can't take it back now, eh?

Happy Holidays to you and your family!!

Cheers,
kt

Kimberly L. Tripp

1/11/2011 2:23:02 PM #

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

Blake Underwood

1/12/2011 5:08:54 AM #

That was so funny. Anyway thanks for sharing this and Happy Holidays.,

cursos de idiomas en el extranjero

2/13/2011 11:26:40 AM #

Regarding Blake's comment (I just un-hid it today but I had sent Blake an email when it was posted). Just adding this comment for completeness!

So... I ended up turning his question into a SQLMag Q&A post here: www.sqlmag.com/.../...e-nonclustered-indexes.aspx.

Enjoy!
kt

Kimberly L. Tripp