SQL Server reads statement running statistics

SQL Server reads the statistical information of statement execution. In addition to the execution plan, there are other factors to consider, for example, the statement Compilation Time, execution time, and number of disk reads. If the DBA can test the problem statement separately, you can enable the following three switches before running to collect statistics about the statement running. These emails

SQL Server reads the statistical information of statement execution. In addition to the execution plan, there are other factors to consider, for example, the statement Compilation Time, execution time, and number of disk reads. If the DBA can test the problem statement separately, you can enable the following three switches before running to collect statistics about the statement running. These emails

SQL Server reads statement running statistics

In addition to the execution plan, there are other factors to consider when running a statement, such as the statement Compilation Time, execution time, and number of disk reads.

If the DBA can test the problem statement separately, you can enable the following three switches before running to collect statistics about the statement running.

This information is of great value for analysis.

1 SET STATISTICS TIME ON2 SET STATISTICS IO ON3 SET STATISTICS PROFILE ON

SET STATISTICS TIME ON

First, let's see what information will be returned by set statistics time on. Run the following statement first:

1 dbcc dropcleanbuffers 2 -- Clear All cached data in the buffer pool 3 DBCC freeproccache 4 GO 5 6 -- Clear All cached execution plans in the buffer pool 7 set statistics time on 8 GO 9 USE [AdventureWorks] 10 GO11 select distinct ([ProductID]), [UnitPrice] FROM [dbo]. [SalesOrderDetail_test] 12 WHERE [ProductID] = 77713 GO14 set statistics time OFF15 GO

In addition to the result set, SQLSERVER returns the following information:

1 SQL Server Analysis and Compilation Time: 2 CPU time = 15 ms, occupied time = 104 Ms. 3. SQL Server Analysis and Compilation Time: 4. CPU time = 0 ms, and time used = 0 ms. 5 6 (4 rows affected) 7 8 SQL Server execution time: 9 CPU time = 171 ms, occupied time = 1903 Ms. 10 SQL Server Analysis and Compilation Time: 11 CPU time = 0 ms, occupation time = 0 ms.

You know that SQL Server statements are executed in the following stages: Analysis-compile-Execute

Analyze the appropriate execution plan based on the statistical information of the table, compile the statement, and finally execute the statement.

What does the above output mean:

1. CPU time: The meaning of this value refers to the pure CPU time spent by SQLSERVER in this step. That is to say, the number of CPU resources consumed by the statement

2. Time used: This value indicates the total time used in this step. That is to say, this is the length of time for the statement to run. Some actions may have I/O operations, resulting in I/O waits,

Or a blocking wait occurs. In short, the time is used up, but no CPU resources are used. Therefore, it is normal that the CPU time is longer than the CPU time, but the CPU time is

The total time of the statement on all CPUs. If the statement uses multiple CPUs, and there is almost no other waiting time, it is normal that the CPU time exceeds the Occupied time.

3. Analysis and Compilation Time:This step is the statement Compilation Time. Since all execution plans are cleared before the statement is run, SQLSERVER must compile it.

The compilation time here is not 0. Since compilation mainly involves CPU operations, the CPU time is generally the same as the time used. If the difference is large,

It is necessary to check whether SQL Server has bottlenecks in system resources.

Here they are a 15 ms, one is 104 Ms

4. SQLSERVER execution time:The actual running time of the statement. Since the statement is run for the first time, SQLSERVER needs to read data from the disk to the memory. Here the statement

A long I/O wait occurs. Therefore, the difference between the CPU time and the Occupied time is very large, one is 171 milliseconds, and the other is 1903 milliseconds.

In general, this statement took 104 + 1903 + 186 = 2193 milliseconds, of which the CPU time was 15 + 171 = 186 milliseconds. The main time of the statement should be spent on I/O wait.

Statement again, but no cache is cleared

1 SET STATISTICS TIME ON2 GO3 4 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]5 WHERE [ProductID]=7776 7 GO8 SET STATISTICS TIME OFF9 GO

This is much faster than the previous one. The output time statistics are:

1. SQL Server Analysis and Compilation Time: 2. CPU time = 0 ms, and time used = 0 ms. 3. SQL Server Analysis and Compilation Time: 4. CPU time = 0 ms, and time used = 0 ms. 5 6 (4 rows affected) 7 8 SQL Server execution time: 9 CPU time = 156 ms, occupied time = 169 Ms. 10 SQL Server Analysis and Compilation Time: 11 CPU time = 0 ms, occupation time = 0 ms.

Because the execution plan is reused, the "SQL analysis and compilation time" CPU time is 0, and the Occupied time is 0.

Because the data is already cached in the memory and does not need to be read from the disk, the SQL Execution time is 156 CPU time, which is very close to the CPU time, which is 169.

The running time saved here is 1903-169 = 1734 milliseconds. From here, we can see that the cache plays a crucial role in statement execution performance.

To avoid affecting other tests, run the following statement to disable set statistics time on.

1 SET STATISTICS TIME OFF2 GO

SET STATISTICS IO ON

This switch can output the number of physical reads and logical reads performed by the statement. It plays an important role in analyzing the complexity of statements.

Take the query as an example.

1 DBCC DROPCLEANBUFFERS2 GO3 SET STATISTICS IO ON4 GO5 6 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]7 WHERE [ProductID]=7778 GO

The returned result is:

1 (4 rows affected) 2 tables 'salesorderdetail _ test '. 5 scans, 15064 logical reads, 0 physical reads, 15064 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads.

The meaning of each output is:

Table: The name of the table. The table here is SalesOrderDetail_test.

Scan count: Number of scans performed. According to the execution plan, the table was scanned several times. Generally, the more times a large table is scanned, the worse it is. The only exception is that if the execution plan selects concurrent operation,

Multiple Threads read a table at the same time. Each thread reads a part of the table, but the number of all threads is displayed here. That is to say, several threads are working concurrently,

There will be several scans. In this case, the number is too large.

Logical read: The number of pages read from the data cache. The more pages, the larger the data size to be accessed, the larger the memory consumption, and the more expensive the query.

Check whether the index should be adjusted to reduce the number of scans and narrow the scan scope.

By the way, the statistical principle of this logical read is as follows: why the Unit of the displayed result is neither Page nor K or KB. SQLSERVER

When reading and writing, it will run to a specific piece of code. Every time this code is called, Reads/Write will add 1. Therefore, this value is relatively large.

The statement must have a lot of I/O operations, but it cannot be used to calculate the absolute number of I/O operations. This value reflects that the logical read/write volume is not the physical read/write volume.

1 logical read 15064 times

Physical read: Number of pages read from the disk

Pre-read: Number of pages that are pre-read into the cache for query

Physical read + pre-read: The number of pages that SQLSERVER reads from the disk to complete this query. If the value is not 0, the data is not cached in the memory. The running speed will be affected.

LOB logic read: Number of pages of the text, ntext, image, Volume type (varchar (max), nvarchar (max), and varbinary (max) read from the data cache

LOB physical read: Number of pages of the text, ntext, image, and category types read from the disk

LOB pre-read: Number of cached text, ntext, image, and plain pages for query

Run it again without clearing the cache.

1 SET STATISTICS IO ON2 GO3 4 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]5 WHERE [ProductID]=7776 GO

Result set return:

1 Table 'salesorderdetail _ test '. Scan count 5, logical read 15064, physical read 0, pre-read 0, lob logical read 0, 2 lob physical read 0, lob pre-read 0.

The logic read remains unchanged for this time, and the page is still 15064. However, the physical read and pre-read operations are both 0. Indicates that the data has been cached in the memory.
The second operation does not need to be read from the disk, saving time.

To avoid affecting other tests, run the following statement to disable set statistics io on.

1 SET STATISTICS IO OFF2 GO

SET STATISTICS PROFILE ON

This is the most complex one returned from the three settings. It returns the statement execution plan and the actual number of returned rows when the statement runs in each step.

Through this result, you can not only get the execution plan, understand the statement execution process, analyze the statement optimization direction, but also determine whether SQL Server is

Select a correct execution plan.

1 SET STATISTICS PROFILE ON2 GO3 SELECT COUNT(b.[SalesOrderID])4 FROM [dbo].[SalesOrderHeader_test] a5 INNER JOIN [dbo].[SalesOrderDetail_test] b6 ON a.[SalesOrderID]=b.[SalesOrderID]7 WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]<536608 GO

The returned result set is very long. The following describes important fields.

Note: This is from the bottom to the top, that is, it is executed from the bottom until the result set is obtained (Row 1) the value displayed in the rows field is the result set returned by this query.

The number of rows indicates the number of steps performed by SQLSERVER. The number of rows indicates that SQLSRVER has performed 6 steps !!

Rows: The actual number of rows returned by each step of the Execution Plan

Executes: Number of times the execution plan is run in each step

StmtText: The specific content of the execution plan. The execution plan is displayed as a tree. Each row is a running step, and a result set is returned and its own cost is also available.

EstimateRows: SQLSERVER estimates the number of rows returned for each step based on the statistical information in the table. When analyzing the execution plan,

We often compare the Rows and EstimateRows columns. First, check whether SQL Server estimates are correct to determine whether statistics are updated.

EstimateIO: SQLSERVER estimates the I/O cost generated by each step based on the length of the field recorded in EstimateRows and statistics.

EstimateCPU: SQLSERVR estimates the CPU cost generated by each step based on the length of the field recorded in EstimateRows and statistics, as well as the complexity of the task to be done.

TotalSubtreeCost: SQLSERVER calculates the cost of the child tree of each execution plan based on some calculation formula of EstimateIO and EstimateCPU.

(Including the total cost of the current cost step and all its lower-level steps ),The cost described below is about this field value.

Warnings: The warning that SQLSERVER encounters when running each step. For example, if no statistical information is provided for a certain step, cost estimation is supported.

Parallel: Is parallel execution plan used in this step of execution plan?

The above results show that the execution plan is divided into four steps, and the first step is divided into two parallel sub-steps.

Step a1 (Row 1 ):Find all values of a. [SalesOrderID]> 43659 AND a. [SalesOrderID] <53660 in the [SalesOrderHeader_test] table.

Because the table has a clustered index on this field, SQL can directly use the seek of this index.

SQL prediction returns 10000 records, and 10000 records .. This prediction is accurate. The cost in this step is 0.202 (totalsubtreecost)

Step a2 (Row 2 ):Find all values of a. [SalesOrderID]> 43659 AND a. [SalesOrderID] <53660 in the [SalesOrderDetail_test] table.

Because the table has a non-clustered index on this field, SQL can directly use the seek of this index.

Here we can see that SQL is clever. Although the query statement only defines a. [SalesOrderID]> 43659 AND a. [SalesOrderID] <53660 filter conditions on the [SalesOrderHeader_test] table,

However, according to semantic analysis, SQL knows that this condition is true on [SalesOrderDetail_test. Therefore, SQL selects to filter the condition and then join. This greatly reduces the cost of join Operations.

In this step, the SQL statement returns an estimated 50561 records, and the actual return is 50577. Cost is 0.127, not high

Step B (Row 3 ):Join the result set obtained in steps a1 and a2. Because SQL knows through estimation that these two result sets are relatively large, he chooses the join method of Hash Match directly.

SQL estimates that the join operation can return 50313 rows, and the actual return is 50577 rows. Because SQL has statistical information on the two tables [SalesOrderID], the estimation here is very accurate.

Cost in this step equals totalsubtreecost minus his sub-step, 0.715-0.202-0.127 = 0.386. Since the estimated value is very accurate, we can believe that the cost here is the actual cost of each step.

Step c (Row 3 ):Calculating the value of count (*) Based on the result set returned by join is relatively simple. The result of count (*) is always 1, so the predicted value is correct.

In fact, the cost in this step is estimated based on the result set returned by the previous (B) join Operation. We know that the estimated return value of Step B is very accurate, so the estimated cost in this step will not have any major problems.

The cost of this subtree is 0.745, minus the cost of its child node, and his own cost is 0.745-0.715 = 0.03. Is a very small step

Step B (Row 3 ):Convert the value returned by Step c to the int type and return the result.

This step continues from the previous step, which is simpler. The cost required for the Data Type of a convert value is almost negligible. So the cost of this subtree is equal to that of its subnode, both of which are 0.745.

That is to say, his own cost is 0.

By using this method, you can understand the statement execution plan, SQL Server estimation accuracy, and cost distribution.

Finally, the cost of different machines may be different in different SQLSERVER versions, such as SQL2005 and SQL2008.