Why some of your clustered indexes create performance problems and how to check that?

Many physical database implementation suffer performance because of wrong clustering key. If the clustering key is not appropriately chosen it can lead to
•high level of page split events
•high level of index fragmentation
•increasing the overall object size – the clustered and all nonclustered indexes of the table

The results are slow execution of insert statements, overall performance degradation when accessing specific tables, increasing the size of the table object and the database as a whole.

The goal of this post is to describe the possible damages of the wrong clustering keys, as well as to give the hints for finding problematic clustering keys in your current database. Last but not least I am giving you the main recommendations on choosing the clustering keys. The code and described principles in this post are valid for all latest SQL Server versions – 2008, 2008R2 and 2012