Query Tuning (Part 1 - Evaluating Query Performance)

Good afternoon everyone! Today's blog post is going to focus on Query Performance. It's all well and good creating queries that return the correct data. However, queries may need to be optimised if it's taking a long time to retrieve those results. As a rule of thumb, you really don't want your end users to be waiting more than 30 seconds for their data to appear. This blog post will mark the start of another series discussing ways you can improve the quality of your queries, and therefore also improving the performance. 

Measuring the performance of your database queries is one of the most important aspects of designing and creating your database. This is so that you can fine tune and tweak parts of it in order to make it run better, and to:

• Increase the scalability of the database. 
• Increase the speed at which data is returned to the application.

When measuring the performance of your database queries, you should consider the 3 main metrics:

• Query Cost
• Page Reads
• Query Execution Time

Query Cost

Query cost takes into account: 

• CPU Resources being used by the query.
• I/O (Input/Output) Resources being used by the query.

Generally speaking, the lower the query cost, the better the performance of the query. Sounds simple right?.. However! Query cost can only be used as an estimated guideline because:

1. It doesn't take into account any waiting time for locks.
2. It doesn't take into account any time for freeing up resources on the server. 
3. It doesn't take into account User Defined Functions or CLR routines. 

Therefore as a result, it means that the query cost could be predicted much lower compared to what the actual query cost is. Despite this, the query cost is a relatively reliable estimation.

Page Reads

Page Reads represent the number of 8KB data pages accessed by SQL Server when a query is being run. In order to retrieve this query, you can execute the following: 

SET STATISTICS IO ON 

However, are page reads a useful method of calculating your query's performance? Quite probably not because:

1. It doesn't take the amount of CPU resources used into consideration. 
2. It doesn't take into account User Defined Functions or CLR routines either. You will notice when you use SET STATISTICS IO ON, the returning result set will have no mention of any UDFs or CLR routines. 

Query Execution Time

The length of time it takes for a query to run can be impacted by both locking and the battle for using resources, which may produce some very varied results depending on the activity on the server when the transactions are executed.

If you want to see the execution time for each query, SET STATISTICS TIME ON returns it in milliseconds. 

Execution time is very important as (from experience) predefined time outs in the code behind have caused issues whereby data requested isn't being returned in time, and therefore producing a completely blank grid. Therefore, despite it being a potentially inconsistent method of investigating query performance, it is quite possibly one of the most reliable and shouldn't be forgotten.

Really, a combination of the 3 performance tests should be carried out to determine if a query is under performing.

I have discovered that my query is running much slower than expected, why?

Your queries could be running slow for a number of reasons, though the most common are listed below:

• Lack of useful statistics.
• Lack of useful indexes. 
• Lack of useful partitioning. 
• Slow network connection.
• Not enough memory on the server computer for SQL Server to run.

When a query runs slowly, you should really investigate why this is the case because issues can become bigger problems as the database and its contents increase in size. Keep a note of why you think your queries are too slow and investigate further using Graphical Execution Plans and SQL Server Profiler, which I will come onto in future blog posts. 

What Next...

Stay tuned for tomorrow as I will be talking more about the order in which your queries are executed, as it may surprise you to learn that they aren't executed as you read them (from left to right) and that certain conditions make your query execute in a different order. This is very important to know before you begin using Graphical Execution Plans so you know exactly how the execution of your queries works. 

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.