SQL Server Tuning Blog

Yesterday, at our 118th Israeli SQL Server User Group Meeting, Adi Cohn delivered a session on common performance pitfalls. One of the issues he mentioned is something every DBA and database designer should be aware of. Many applications use overly large size limit for their variable data types. The reasoning in many cases is simply "Why not?" If you have a variable size column and you made the choice to use one of the VAR data types anyway, why limit the size? Even if you expect to have up to 300 characters in the 'comments' column for example, what is wrong with defining it as 3,000? it's just metadata, right? Wrong...

Although physically, storage will be exactly the same if you define your VARCHAR as 300 or 3,000, it can still make a huge impact on performance. SQL Server needs to estimate the work space that is required for each query and allocate it before the query begins to execute. For variable type columns, the estimation is that the data size will be 1/2 of the max size defined for the column. So if you have a column that is defined as 300 bytes, the allocation estimation will be 150 but for 3,000 - the estimate will be 1,500. On systems that experience memory pressure, this can make a huge difference in performance as your queries will wait for memory allocations, potentially significantly hurting performance in addition to the fact that needless memory will be allocated that could serve SQL Server for much more useful purposes.

An interesting discussion came up lately on the MVP private forums regarding index rebuilds, recompilation and statistics updates. Although the debate spanned too many aspects for me to cover here in this short post, I think that one important point should be emphasized as it might be making you do redundant work or even worse - unknowingly degrade the performance of your workload.

When you rebuild an index, a full scan of all index keys is performed. SQL Server uses this opportunity not only to rebuild the index, but also to update the related statistics. What is unique about this statistics update is the fact that it is equivalent to an update with the full-scan option. The default statistics sampling uses only a small fraction of the keys in order not to load your production server. A statistics based on a full scan of the data has the potential to be more accurate, therefore provide the query optimizer with better information, allowing it in turn to produce better performing execution plans for your workload queries. Also remember that a statistics update flushes all plans that use it from the procedure cache forcing a recompilation of the related queries.