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.
I deliver regularly level 400+ Performance and Tuning Workshops and trainings as part of my SQL Master Academy. All of those very intensive and incredibly useful workshops focused my attention on topics that are trully importat at the same time not well kown. I made an experiment presenting a session named Detecting Problems in Query Plans in our SQL User Group Meeting. The people in the audience were very happy to clarify some topics, and the feedback I received was very positive.
When you find out (trought tracing the event) the statements where the sort/hash warnings occur try to perform the following optimizations:
- make sure statistics are maintained properly on database and object level
- use compile hint, sometimes it is cheaper to recompile on every execution than to pay for sort/hash warnings, you can use plan guides if you don’t want to change your code. This is the most common way to avoid and fix the problem. This provides the generation of the optimal plan for every execution
- Don’t confuse parameters and variables and make sure you have the right values at the compile time provided to the QP (using hint for example). Using variables leads to generating a general plan – best for nothing but good for everything. This is not a guarantee to achieve a good performance.
- Make sure to eliminate/reduce cardinality error cases. Most of them need rewriting the query
- Reduce ODRER BY if you can
I blogged the Inside Hash and Sort Wranings long time ago, and I found that it is still useful and actual to clarify the topic. That is why I posted the link again here.
Good luck and thanks for reading! 🙂