Writing and tuning SQL code are two different things. There are many ways to write code so that it does the intended function. But there are also ways to make code more efficient. In most cases, we follow development techniques in repetition based on what the database administrator or developer knows. Sometimes, this might yield the best performing code. But in other cases, perhaps not.
To tune a query, you can review execution plans and identify where the problem is and then change to optimize the code. Another approach to tuning queries is to use the SQL Query Tuner add-on for SQL Diagnostic Manager. You can find what statements need to be addressed by looking at duration and wait stats and then use SQL Query Tuner to suggest different ways to rewrite the code along with metrics to show overall improvements. Below, we can view an aggregate view of statements run.
If we drill into a particular statement, we can then get suggestions on how to improve the code. Besides showing the improvements, the SQL Query Tuner also generates the code for you to use in your development. Below we can see several suggestions to improve the code along with metrics for each case, which includes elapsed time, reads, central processing unit, and more.
For more information, please refer to the solution brief “Keep your SQL Servers running smoothly with SQL Diagnostic Manager” by Greg Robidoux from MSSQLTips.