Measure the performance of a TSQL statement

How do you know how your application performs? What tools can you use? Each developer needs to determine that each application's TSQL statement is optimal. By adjusting each query you can be sure that your application is running as efficiently as possible. You can make it easier to modify your application's code when you use the development environment. Once your code has become a product then it may take a long time, or even impossible, to make changes to optimize your code. That's why you need to periodically check the execution of your application code during application development. This article will cover some of the different ways to determine how to run a slower query and provide you with tips for monitoring your query performance when you repeatedly modify each query to try to improve performance.

　　How to determine which queries are running slowly

With SQL Server 2005 you have two different options. The first option is SQL Server Profiler, which is also available in other versions of SQL Server. With SQL Server 2005, you have the "sys.dm_exec_query_stats" DMV. Let's take a look at how these two choices can be used to determine which queries are running slower.

SQL Server Profiler is an easy-to-use GUI tool. Profiler can be used to display the duration of CPU, I/O, and SQL Server TSQL statements on a single instance of SQL Server. In order for the profiler to be able to find you running a slower query, you need to create and start a profiler trace. You can easily do this by using the new tracking item. Profiler has a "tsql duration" template that can be used to find queries that you run longer. But if you want to show the CPU and I/O for these long-running queries, you will need to create your own profiler traces, or modify the fields selected using the TSQL duration template. You can modify the TSQL duration template by clicking the "Event Selection" tab and then clicking "CPU", "read", or "check box under" Write to select CPU and I/O. To see more information on how to use SQL Server Profiler, refer to the online books.

If you have SQL Server 2005, you can also use the "sys.dm_exec_query_stats" DMV to determine long-running queries. This DMV can be used to return statistics for cached query plans. Here is an example of how to return some of the different performance measurements for each cache plan.

The following is a reference fragment: SELECT Creation_time , Last_execution_time , Total_physical_reads , Total_logical_reads , total_logical_writes , Execution_count , Total_worker_time , Total_elapsed_time , Total_elapsed_time/execution_count Avg_elapsed_time , SUBSTRING (St.text, (QS.STATEMENT_START_OFFSET/2) + 1, (Case Statement_end_offset WHEN-1 then datalength (st.text) ELSE Qs.statement_end_offset End -Qs.statement_start_offset)/2) + 1) as Statement_text From Sys.dm_exec_query_stats as Qs Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) St ORDER by Total_elapsed_time/execution_count DESC;

I use this query to calculate the average elapsed time and then make the output, so the query with the worst average elapsed time is shown in the first. Elapsed time is a good measure for determining poorly performing queries, because this is the actual clock time that is spent processing a query.

By using Profiler or DMV queries you can determine your poorly executed queries based on duration/elapsed time. Once you've identified a poorly executed query, then you're ready to optimize those queries. By optimizing poor queries you will get the best performance and the minimum cost to improve the overall performance of your application.

　　Tools to help optimize queries

When you refine your query, you need to check the performance information repeatedly after each change. Doing this check allows you to determine whether your changes have improved or made your query performance worse. There are three useful measurements in determining your query performance: elapsed time, I/O, and CPU. Let's take a look at the different methods for measuring each of these three in a given query.

　　Actual time-consuming room

The time it takes for a query to run is called the elapsed time, which is also known as the clock time. Many things can affect the actual elapsed time of the query. Elapsed time is an excellent benchmark measure of how a query executes, because it measures how long a user waits for a particular query to be processed by SQL Server. So what are the different ways you can measure the elapsed time of a TSQL statement?

The first method you can use is that "elapsed time" is displayed in the Query window of the SQL Server Management suite when executing a batch file. This is the time I will wait, as shown in the following:

Here I executed a loop 100 million times to add 1. Note that the elapsed time I wait is shown in red in the lower left corner. If you run your slower query in SSMs, you can use this elapsed time display as a measure of how long your query will run. Every time you change your query and rerun it, you can look at this time to determine if your query is running faster.

Another way to measure elapsed time now is to use a setstatistics command to display the elapsed time of a query. Here is a screenshot that shows the elapsed time statistics for each statement executed in my Query window in the query results panel:

Here I execute the "SET STATISTICS time on" statement before running my simple SELECT statement. Note that the "elapsed time" statistic is displayed in the results panel, along with "CPU time" and the output of my print statement. The "SET stastistics time on" statement shows how long it takes to parse, compile, and execute each statement. This statement is useful if you only execute a small number of statements. If you execute a large number of statements, similar to my first loop example, then the number of rows in the output will be much more difficult to read.

Now you sometimes need to measure the time spent executing different chunks of code in a batch file, not just a specified TSQL statement. This can be useful if you have a large script or stored procedure that runs for a long time, and you want to find out which part is the longest running time. To create the elapsed time for a code snippet, we can use the following method:

In this you can see that I have run two different simple loops. For Each loop, I calculate the time it takes to make this loop. I put the starting time of each code block into a variable called @start. Then, when the loop ends, I print out the difference between the @start DATETIME variable and the current time obtained with the GETDATE () function. In my case, the first loop takes 10,000 times, and the second loop 90,000 times takes 110 seconds.

　　CPU Time

Another important performance measurement is the number of CPUs your query needs to return a result set. In the previous section, the CPU time for each statement was displayed in a batch file when I was using the SET STATISTICS time on statement. So this is the first method you can use to measure the CPU consumption of a TSQL statement. But how do you measure the CPU time of multiple statements or different blocks of code?

SQL Server provides the @ @CPU_BUSY system variable, which is used to measure CPU consumption. This is the definition of this variable on the online book: Returns SQL Server from the last boot to the current working time. The result is on the cou time increment, or "tag", and is the accumulation of all CPU time, so it may exceed the actual elapsed time. Multiply it by the @ @TIMETICKS to convert to microseconds.

The following approach is that you can use the @ @CPU_BUSY variable to measure the amount of CPU used by SQL Server in each of my previous examples during the execution of a loop. Now remember that since the @ @CPU_BUSY contains the amount of CPU used since SQL Server started, it includes the amount of CPU used by all users and the queries that the system runs on the server. Therefore, this method measures the exact amount of CPU when only you run SQL Server commands on this system and no background system executes the query:

As you can see, I measured the CPU millisecond time used by the first and second loops. Just let you know I ran this query several times and get the actual difference in each loop execution. So be aware that using this method will be affected by other things running on SQL Server. What I'm trying to say is that this method is not always reliable for measuring actual CPU usage, but if you have a separate system with less activity, it's still a way to measure CPU consumption.

A more accurate way to measure CPU usage is to use SQL Server Profiler. It includes the CPU fields, which are displayed in different completion events. Here is an example of how I use SQL Server Profiler to capture the amount of CPU used by the loop batch above:

Here I use the Sql:batchcompleted event to capture the CPU. By default, SQL Profiler shows the duration in milliseconds. Using the Tools-Options dialog box, you can change this display to microseconds. Note that this method can only measure the full batch, not the duration of each cycle in my batch.

　　I/O Use

Like other performance measurements, there are ways to show the number of I/O used by a TSQL statement. There are two different types of I/O tracked in SQL Server: Logical IO and physical IO. Logical I/O considers data that is processed from the in-memory buffer pool and is therefore called logical I/O. Physical I/O is the I/O associated with accessing data directly from the physical disk that SQL Server uses to store the database. Physical I/O is more expensive I/O, meaning that they take longer to process. In general, I/O is the most expensive single operation, and it affects the entire performance of a TSQL statement. So when you adjust your query, you want to minimize the number of logical I/O and physical I/O operations that are executed to generate a result set.

One way to display the number of I/O associated with a TSQL statement or statement batch file is to use the SET STATISTICS IO on statement to open the I/O statistics collection process. When you execute this statement on the connection, you will output the number of I/O used to resolve your TSQL statement. In the following example, I use the "SET stastistics IO on" statement to display the number of I/OS needed to resolve a simple query to the AdventureWorks database:

In this you can see my SELECT statement execution 2 "logical reads", and 2 "physical reads". Now when I run this statement batch for the second time, I get the following output:

Here you will notice that the second time I execute my tsqlselect statement when it only requires 2 "logical reads" and 0 "physical reads", this is because the AdventureWorks page that keeps this query has been slowed down in the buffer pool. If you are doing repetitive testing to improve your query performance, you need to make sure that you remove the I/O count differences that occur when your query is already in the buffer cache and on page requests. To eliminate this counting problem, you can execute the "dbccdropcleanbuffer" command before running each test. This will make your query run with a clean buffer pool without having to stop and restart SQL Server.

Another way to track I/O is to use SQL Server Profiler. To do this, make sure that you include the I/O related fields when determining which events and fields you want to monitor for your tracker.

　　Summarize

Being able to track the resource consumption of your different queries is a key to monitoring your application. Knowing what TSQL statements are using the most resources can help you determine what to focus on when optimizing your application. The "sys.dm_exec_query_stats" DMV helps database administrators quickly identify these TSQL statements that use the most resources. Using various "SET" statements, System variables, and/or SQL Server Profiler events/fields can help you measure the CPU, I/O, and consumption time of your problem TSQL statements. Identifying, measuring, and improving your application query performance will help you optimize the code behind your application.

Article Source: http://database.ctocio.com.cn/tips/159/7771659.shtml

Measure the performance of a TSQL statement