Are your clustering keys good enough

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

Update with WRITE on varcharmax column leads to time-outs

The following interesting case came out from our SQL User Group member Ilian Yovchev who works for David Holding company.

They have updated their text filed types to varcharmax data type according to the SQL Server roadmap and deprecation plans. They use update with WRITE to insert data in those new fields.

But they faced time outs during some updates and on query executions against the table with varcharmax field type. They have traced those time outs and found that the reason behind them is sync stats update performing on varcharmax col. Strange, isn’t it? Well, not exactly as synch stats update (Asych Stats Update=OFF in db level) can potentially lead to query time out.

Let’s see the specifics of this case.

Find and tune inefficient query executions

Or a way to find cardinality errors in your cached execution plans

If SQL Server can’t accurately predict estimated number of rows for a query plan operator, then a discrepancy appears in the estimated v/s actual number of rows in the query plan. QP creates a query plan and choses a specific operator because it expects that the query returns some number of rows, but actually when the plan executes, a lot more (or far less) number of rows go trough the operator. This is called cardinality error, because SQL Server makes an error on estimating cardinality of an operator. The result of cardinality error in almost every case is that the query executes with suboptimal execution plan. Of course it means that query has performance problems and delays in some executions.

Hash and Sort Warnings – a common performance problem

Hash and Sort Warnings events are very common in a real production systems. The problem is that we usually don’t know that we have those events, and that they really hammer the performance. I am surprised that in almost every Performance Assesment that I make I find such events during the monitoring.

Having Hash and/or Sort warning means that the query/procedure has problems in the execution plan, generally the execution plan is not the optimal one. As a results more resources could be needed to execute the query than usual.