Joe,Thank you for the excellant article.When I wrote a year ago my article about fragmentation, this is exactly what I had in mind - but since I am not an SQL expert, I could not do it myself.Please read my article at http://www.sqlservercentral.com/columnists/kbiller/performancemonitoringbyinternalfragmentationmeasur.aspand look at www.disklace.com where I present the fragmentation measurement model. This model is working for more than 100,000 users, and proves to be accurate. If you want to join forces and give the audience of this forum a solution - I am ready.Koby BillerKoby@disklace.com

Thanks for the article cant wait for the next instalment. One thing that has confused me over the years is how sql stores and organises text data. You do not include this type of data field in your table,is their a reason for this? if you can just explain about text data as i find you explinations so far very easy to understand.

I really enjoyed this article. I am not a SQL expert in any way, and found the step by easy step approach excellent. I really feel I have learned something and time spent here was very worthwhile. I look forward to the next article - particularly with a view towards indexes.

I have come across this knowledge through evaluation of a legacy database which is massive for apparently no reason. I could not shrink its size no matter what I did. Only after much reading did I come across the "Heap Gem" of knowledge and was able to cut the DB down to 40% of its original size by adding, yes adding, clustered indexes to the top 30 largest tables. There were hundreds of tables, dunno what would have happened by adding a CI to all appropriate tables.

Great article Joe. Thanks a bunch. Your take on extent switching was new to me. I'm in hopes that the rest of your series will help clear up a mystery for us.

Billy, I agree with you. This goes into our mystery. One table with less than 100 thousand rows had sporadic inserts take MINUTES to complete. The majority of inserts take fractions of seconds. All DBCC indications were clean. Re-index had been done and still it happened. A specific insert would hang. If you drop the row and re-run that specific insert would take long.

In frustration I advised the guy working the problem to drop all the indexes and recreate them. Most are non-clustered and the clustered is PK on the identity column. Dropping the clustered index took 20 minutes. Re-creating the indexes seems to have fixed the problem.