The use of T-SQL commands in queries

The first thing to note is that the content of this article is not how to adjust SQL Server query performance (to write a book about this), but how to use set STATISTICS IO and set STATISTICS in the tuning of SQL Server query performance Time these two Transact-SQL commands that are often ignored.

On the surface, the adjustment of query performance is a very simple matter. Essentially, we want the query to run as fast as possible, either by reducing the query's running time from 10 minutes to 1 minutes or by shortening the run time from 2 seconds to 1 seconds, and our ultimate goal is to reduce the running time.

Although there are many reasons for the difficulty of query performance tuning, this article will only cover one of these areas, the most important reason is that whenever the use of the environment changes, you need to adjust performance, so it is difficult to figure out how to adjust the performance of the query.

If you perform a performance survey on a test server like most users, the effect is often not very satisfying, because the environment of the test server is not exactly the same as the server environment in which it is actually applied. As the resource requirements evolve, SQL Server automatically adjusts itself.

If you have questions about this, you can run the same query repeatedly on a heavily loaded server, and in most cases, the time used to execute the query is different. Of course, the gap is not big, but the change is enough to make performance adjustment more difficult than it should be.

What the hell is going on here? Is your idea wrong or is the server overloaded when you run the query? Is this the cause of the increase in running time? Although it can take an average of time to run a query repeatedly, it's a lot of work. We need to compare the performance of each test with a very scientific standard.

Measuring server resource is the key to solve query performance regulation problem

There are a number of server resources that are used when executing queries on the server. One of these resources is CPU time, assuming that the database has not changed any, repeatedly running the same query its CPU time will be very close. Here, I'm not talking about a query from run to finish, but rather the amount of CPU resources needed to run the query, and the time it takes to run a query is related to how busy the server is.

Another resource that SQL Server needs is IO. Whenever a query is run, SQL Server must read the data (logical reads) from the data buffer, and if the required data is not in the buffer, it needs to be read on disk (physical reading).

As you can see from the discussion, the more CPUs and IO Resources A query requires, the slower the query runs, so another way to describe the query performance tuning task is to rewrite the query command in a way that uses fewer CPUs, IO resources, and if you can complete the query in such a way, The performance of the query can be improved.

If the purpose of tuning query performance is to make it use as few server resources as possible, rather than the shortest time the query runs, it is easier to test whether you have improved the performance of the query or decreased the performance of the query. This is especially true for servers with ever-changing resource utilization. First, you need to figure out how to test the resource usage of our servers when tuning the query.

And I think of the set STATISTICS IO and set STATISTICS time

SQL Server has long supported the two Transact-SQL commands for set STATISTICS IO and set STATISTICS time, but for some other reason, many DBAs (data for system administrators) ignore them when tuning query performance. Maybe they are not attractive. But whatever the reason, we'll find that they're useful for tuning query performance.

There are three ways to use these two commands: using the Transact-SQL command-line, using Query Analyzer, setting the appropriate connection properties for the current connection in Query Analyzer. In this article, we will use the Transact-SQL command line to demonstrate their usage.

The set STATISTICS io and set STATISTICS Time act as switches to turn on or off the various reporting information for our queries using resources. By default, these settings are turned off. Let's first look at an example of how these commands are opened and see what information they will report.

Before starting our example, start query Analyzer and connect to a SQL Server. In this case, we'll use the Northwind database and make it the default database for this connection.

Then, run the following query:

SELECT * FROM [Order Details]

If you haven't changed the Order Details table, this query will return 2,155 records. This is a typical result, and I'm sure you've seen it several times in Query Analyzer.

Now we're going to run the same query, but we'll run the set STATISTICS IO and set STATISTICS time command first before we run the query. Remember that these two commands are open only to the current connection, and once you open one or two of them, and then close the current connection and open a new connection, you need to execute the appropriate command again. If you want to turn off the two commands in the current connection, you can do this once by switching on the on in the original command.

Before starting our example, run the following two commands (do not execute on the server you are using), and these two commands will clear the data and process buffers of SQL Server so that we can be on the same starting point each time we execute the query, otherwise, The results from each execution of the query are not comparable:

DBCC dropcleanbuffers

DBCC Freeproccache

Enter and run the following Transact-SQL command:

SET STATISTICS IO on

SET STATISTICS time on

Once the above preparation is complete, run the following query:

SELECT * FROM [Order Details]

If you run all of the above commands at the same time, the output you get will be different from mine, and it's hard to figure out what's going on.