SQL Server SELECT statement Optimization Method

Microsoft provides three main methods for optimizing queries:

 

 

Use set statistics Io to check the read and write operations generated by the query;

Use set statistics time to check the query running time;

Use set showplan to analyze the query plan.

 

 

Set statistics Io

 

Command set statistics Io on to force SQL Server to report the actual I/O activity during transaction execution. It cannot be paired with the set noexec on option because it only makes sense to monitor the I/O activities that actually execute commands. Once this option is enabled, each query generates additional output including I/O statistics. To disable this option, run set statistics Io off.

 

Note: These commands can also be run in Sybase Adaptive Server, although the result set may look a little different.

 

For example, the following is the I/O statistics obtained by a simple query script for the ROW statistics on the employees table in the northwind database:

 

SET STATISTICS IO ON GO SELECT COUNT(*) FROM employees GO SET STATISTICS IO OFF GO Results: --------------- 2977 Table ‘Employees’ . Scan count 1, logical read 53, physical reads 0, readahead reads 0.

This scan statistics shows the number of scan executions. Logical read displays the number of pages read from the cache, and physical read displays the number of pages read from the disk, read-ahead read displays the number of pages placed in the cache for future read operations.

 

In addition, we execute a system stored procedure to obtain the table size statistics for our analysis:

 

sp_spaceused employees Results: name rows reserved data index_size unused -------------- -------- --------- ------- Employees 2977 2008KB 1504KB 448KB 56KB

 

What can we get by reading this information?

This query does not scan the entire table. The data volume in the table exceeds MB, and only 53 logical I/O operations are performed. This indicates that the query finds an index that can be used to calculate the results, and scanning the index takes less I/O operations than scanning all data pages.

Almost all index pages are stored in the data cache, so the physical read value is zero. This is because we recently executed other queries on the employees table, and the table and its indexes have been cached. Your query overhead may be different.

Microsoft reports no read-ahead (pre-read) activity. In this case, data and index pages have been cached. When scanning a very large table, read-ahead may be inserted halfway and cache the required pages before your queries are used. When SQL Server determines that your transaction reads database pages sequentially and determines that it can predict the page that will be used in the next step, real-ahead will automatically open. In fact, an independent SQL server connection has started running before your process and cached data pages for it. (Setting and optimizing the read-ahead parameter is beyond the scope of this article.

In this example, the query has been executed as efficiently as possible without further optimization.

Set statistics time

 

The actual consumed time of a transaction is an unstable measurement because it is related to the activity of other users on the server. However, compared to the data page numbers that do not make any sense to your users, he provides some practical measurements. They care about the time consumption of waiting for the query to return, not about the data cache and valid read-ahead. The Set statistics time on Command reports the actual query time and CPU usage. Execute set statistics time off to disable this option.

 

SET STATISTICS TIME ON GO SELECT COUNT（*） FROM titleauthers GO SET STATISTICS TIME OFF GO Results: SQL Server Execution Times; Cup time=0 ms. Elapsed time=8672 ms. SQL Server Parse and Compile Time: Cpu time=10 ms ---------------- 25 (1 row(s) affected) SQL Servre Execution Times: Cpu time=0 ms.? Elapsed time=10 ms. SQL Server Parse and Compile Time: Cup time=0 ms

The First Information Reports The amount of confusing (Real-time consumption) time, which is 8672 seconds. This data is irrelevant to our script, this shows the time elapsed since the execution of the previous command. You can ignore this information. SQL Server only takes 10 milliseconds to analyze and compile the query. It takes 0 milliseconds to execute the task (as shown in the query results ). The real meaning is that the query takes too short to be metered. The final information reports that it takes 0 ms to analyze and compile the set statistics time off command. You can ignore this information. The most important information to highlight the font.

 

Note that the actual consumption time and CPU time are displayed in milliseconds. This number may change on your computer (but do not try to compare the performance of your computer with our laptop, because it is not a representative indicator ). In addition, every time you execute this script, the statistics you get may be a little different considering that your SQL Server is still processing some other transactions.

 

If you need to measure the duration of a series of queries or stored procedures, a better way is to adopt programming (as shown below ). When you run multiple commands, You have to manually aggregate them because statistics time only reports the duration of a single query. Imagine that when you time a script that executes thousands of queries in a loop, it will face a lot of output and a lot of manual work.

 

On the contrary, consider the following script capturing time before and after the transaction separately and reporting the total duration in seconds (you can also use milliseconds ):

 

DECLARE @start_time DATETIME SELECT @start_time=GETDATE() <any query or a script that you want to time, without a GO> SELECT ’Elapsed Time,sec’ =DATEDIFF(second, @start_time,GETDATE()) GO

If your script is divided into several steps by go, you cannot use local variables to save the start time. The variable is destroyed after the go command is executed. However, you can save the start time in the temporary table as follows.

 

CREATE TABLE #save_time (start_time DATETIME NOT NULL) INSERT #save_time VALUES ( GETDATE()) GO < any script that you want to time (may include GO) > GO SELECT ‘Elapsed Time, sec’ = DATEDIFF ( second, start_time, GETDATE()) FROM TABLE #save_time DROP TABLE #save_time GO

Note that the SQL server's datetime data type is stored in increments of 3 ms. It is impossible to obtain a finer time granularity than the datetime data type.

 

 

Showplan output and Analysis

 

This article explains the meaning and use of the content output by Microsoft SQL Server 2000 using set showplan_text on through the explain Plan. An explain Plan (also called a query plan, execution plan, or optimization plan) provides a very detailed step for the database query engine to execute SQL transactions. Knowing how to read the explain Plan helps improve the high-end query adjustment and optimization capabilities.

 

Note: most examples are either based on the pubs database or the SQL server system table. for these instances, we have added tens of thousands of records to many tables to reflect the actual functions of the query optimizer when evaluating the query plan.

 

 

Showplan output:

 

One of our favorite functions of the query optimizer is to provide feedback in the form of query execution plans. At present, we can explain the statement execution in detail and describe the messages you may encounter in the query plan. Understanding this output can bring your optimization level to a new height. You can no longer regard the optimizer as a magic "black box" that can process your query statements ",

 

The following command instructs SQL Server to display the execution plan for each query in the same connection (or process) or to disable this option.

 

Set showplan_text {on | off}

 

By default, showplan_text on prevents the code you are reviewing from being executed. Instead, SQL Server compiles the code and displays the execution plan of the query. It does not stop until you issue the set. showplan_text off command.

 

 

Other useful set commands

 

There are a variety of set commands that are useful for tuning and debugging. In this document, we mentioned the set statistics command. In some cases, you can find the use of other set commands:

 

 

 

Set noexec {on | off}: Check the syntax of your Transact-SQL code, including compiling the Code but not executing it. When delayed name resolution is used, this is very useful for checking the syntax of a query statement. That is, when a table has not been created, you can check the syntax of the query statement based on the table.

Set fmtonly {on | off}: returns the queried metadata to the client only. For select statements, only the column header is usually returned.

Set parseonly {on | off}: Check the syntax of your Transact-SQL code, but do not compile or execute the code.

 

Once set to on, these commands will remain valid until you manually close them. These settings do not take effect immediately, but they will take effect from the next step. In other words, you must issue the go command before the settings such as showplan or noexec take effect.

 

The typical T-SQL code is as follows to get an execution plan for a query without actually executing it.

 

SET SHOWPLAN_TEXT ON GO <query> GO SET SHOWPLAN_TEXT OFF GO

We will show several examples of showplan_text output. To avoid redundancy, I will not repeat the display of the above set command. All the queries provided in this section will replace the labels in this script and all will be like the same "packaging" shown above ".

 

In fact, showplan has two versions: showplan_all and showplan_text. The information they provide is basically the same. However, the showplan_all output result is prepared for the image query tool rather than for the audience. We will use showplan_text throughout this article to provide more readable output formats. In the following simple query, select all rows in the authors table. Because we didn't provide the WHERE clause, we had no choice but to scan the entire table: Select * Form authors

 

The output result of showplan_text in the following table is not formatted. we have to sort out more readable information from the output of showplan_all:

 

SHOWPLAN_TEXT SHOWPLAN_ALL StmtTextStmtText --------------------------------- |--Clustered Index Scan |--Clustered Index Scan (OBJECT:([pubs].[dbo]. (OBJECT:([pubs].[dbo]. [authors].[UPKCL_auidind])) [authors].[UPKCL_auidind])) StmtID NodeID Parent --------- -------- ------- 2 2 1 PhysicalOp LogicalOp ------------ ---------------- NULL NULL Clustered Index scan Clustered Index scan Argument --------------------------------------------- 1 OBJECT:([pubs].[dbo]. ].[UPKCL_auidind]) DefindedValues --------------------------------------- 23 _ <all columns in table>_ EstimatedRows EstimateIO EstimatedCPU ------------------ ------------- -------- 23 NULL NULL 23 0.01878925 5.1899999E-5 AvgRowSizeotalSubtreeCost ------------------------------------ NULL 3.7682299E-2 1113.7682299E-2 OutputList ----------------------------------------- NULL _ <all columns in table>_ Warnings TypeParallel EstimateExecutions -------- ------------------------- NULL SELECT 0NULL NULPLAN_ROW01.0

 

The important difference here is that the showplan_all statement returns a lot of useful tuning information, but these are very difficult to understand and apply.

 

 

Showplan operation

 

The showplan operation is sometimes called a "tag". Some of the operations clearly describe the SQL Server practice, while other operations will make it difficult. These operations are divided into physical operations and logical operations. The physical operation description is a physical algorithm used to process queries. For example, you can perform an index search. The relational algebra operations used in logical operation description statements, such as aggregation operations. The showplan result is subdivided into several non-specific steps. The physical operations of each query represent an independent step. A step is usually accompanied by a logical operation, but not all steps include logical operations. In addition, most steps have one operation (logical operation or physical operation) and one parameter. Parameters are the query components affected by the operation. There are a wide range of discussions on all Execution Plan steps.