SQL Server query performance optimization related articles

From:

SQL Server Query performance optimization-heap table, fragmentation, and index (i)

SQL Server Query performance optimization-heap table, fragmentation, and index (ii)

SQL Server Query performance optimization-overwrite index (i)

SQL Server Query performance optimization-overwrite index (ii)

SQL Server Query Performance optimization--Create INDEX principle (i)

SQL Server Query Performance optimization--Creating index Principles (ii)

SQL Server Query Performance optimization--Index and SARG (i)

SQL Server Query Performance optimization--Index and SARG (ii)

SQL Server Query Performance optimization--Index and SARG (iii)

SQL Server Query Performance optimization--Index and SARG (iv)

Précis-writers

SARG definition: Used to limit the search to an operation, because it usually refers to a specific match, a worthy range of matching or more than two conditions and connection. SARG contains constant descriptors (or variables that can be parsed into constants) to compare to fields in a data table. The format of the SARG is:

Column name Operators < constants or variables >

Or

< constants or variables > operator column names

Column names appear on one side of the operator, while constants or variables appear on the other side. If the column name appears simultaneously on both sides of the operation, it is not considered sarg.

The SARG contains the following operators =, >, <, >=, <=, between, and in some cases like. Like whether it conforms to Sarg, depends on where the wildcard% is located. For example: Like ' Hu ' is in line with Sarg, but '% Hu ' is not in line with Sarg. Because the number of SQL Server query records cannot be restricted at the beginning of a wildcard, the index is placed either small or large in order, and if the wildcard "%" does not begin with an ordered structure, the binary method is used to quickly find the data.

1, not to WHERE clauses do operations
By comparing Sarg and non-sarg with a simple example, you can managemenet studio to see if the query optimizer can parse the statement effectively.

2 Do not make negative queries

The format statement for a non-SARG statement is also included in the WHERE conditional clause, using a negative query operator, except that the field data should not be evaluated.

such as not,! =, <>,!>,!<, not EXISTS, not-in, and not-like, because through the sequential index structure, SQL SERVER can effectively use the binary method to find, quickly find the corresponding data, However, if the query condition is not what data, the rest of the (is negative query), you can not use the index for binary lookup, only a full table scan or clustered index scan.

The following are also examples of SARG condition queries and non-SARG conditional queries for negative operation health. It can be seen that the implementation costs are not the same, 10 times times the difference, but the number of records of the data query is not the same. Personal Understanding the query optimizer determines which execution plan to use based on the number of records queried by the data.

3 do not use functions on fields in the WHERE clause

Using a function on a field in a query statement is a calculation of the field data, so these are not considered sarg. After using the function, SQL SERVER needs to enter the relevant fields of all records in the data table into the function, and if there are 1 million records, it needs to call the function 1 million times, which will be the performance killer.

SQL Server query performance optimization related articles