Query Tuning (Part 5 - Which Queries Should I Tune?)

Hi everyone! Hope you all had a great weekend! Today's blog post marks the end of my Query Tuning series.

You can find previous parts of the series here:

Query Tuning (Part 1 - Evaluating Query Performance)
Query Tuning (Part 2 - Query Execution Order)

Query Tuning (Part 3 - Graphical Execution Plans)
Query Tuning (Part 4 - Ways To Improve Your Query Performance)

Now you have determined possible "problem" query parts, you should now investigate this one step further by analysing the query as it runs and using the Database Tuning Advisor.

Firstly, in order to effectively determine which queries need tuning, you should use the SQL Server Profiler tool. This will listen out for all queries run against that particular database instance, though you will probably want to look out specifically for SQL:BatchCompleted and RPC:Completed. You can also choose which columns to retrieve and the following are most useful when it comes to database tuning:

• Duration - Returns the speed of the event in milliseconds.
• Reads - Returns the number of page reads during execution.
• Writes - Returns the number of page writes during execution.
• CPU - Returns the amount of time used by the CPU. 

If any of these columns have particularly high values in them, that's when you should investigate further. You can create trace files using the SQL Server Profiler and then query against them.

The Database Engine Tuning Advisor

You can use the Database Engine Tuning Advisor to give hints as to what could benefit from being tuned. Note that these are only a recommendation however, and that some instances can actually make your data run slower. Therefore it's imperative that you do your investigations first.

In order to run the Tuning Advisor:

Open SQL Server Management Studio and click on Tools > Database Engine Tuning Advisor:

Next, select the database instance you are working on and click connect. This will give you a list of all of the databases found on that server instance.

You will need to choose a work file in order to tune your database tables. For this example, I am going to use a very simple SELECT statement against my Asset Table:

I saved this query onto my Desktop to make it easier to find. Under Workload (and making sure that the radio button is set to File) I selected my script to be analysed and set the database for workload analysis to my database "DaniellePractice".

Next, select the tables that you wish to run your query against. For this example, I want to select my Asset table:














Once you are happy with your selection, take a look at the Tuning Options by clicking on the appropriate tab at the top of the screen. I am going to use the default settings, however it's useful to get familiarised with what they are and what can be changed in order to influence your results:

When you're happy with your settings, click on the Start Analysis button:

You will see the following screen appear:



















This indicates that your tuning analysis has started. Once all 5 stages are complete, you will see a list of recommendations on ways that you can tweak your query and database in order to make them both run faster.

This is just a simple example to show how to use the Tuning Advisor, but I have used it previously in a larger project. A refresh cache button appeared to be timing out. So I used SQL Server Profiler to capture the refresh query, saved the script and then ran the script in the Tuning Advisor against the tables which the refresh cache would have impacted. I discovered that I was right; the query was taking longer than the 30 second time out cap. In order to rectify this, I experimented by adding in the recommendations one by one to find the optimum speed. Once all relevant indexes and statistics were added, the button worked perfectly! It's all about practice.

What Next...

So this nicely moves us along to my next blog post on indexes and partitioning, which will be a rather large topic to cover fully, so this will be broken down into parts as well. Indexes and partitioning are very important as they can help speed up your slow database queries.

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.