SQL Server INDEX OPTIMIZATION practice (2)-index coverage

Continue with the previous article and SQL Server INDEX OPTIMIZATION practices. This article discusses index coverage-SQL Server mainly uses indexes to query the data you need. When the index contains all the fields you requested to query, SQL server does not need to query in the table. This concept is called "index coverage ".

The non-clustered index in sqlserver2005 adds the "included column" option. In SQL Server 2005, you can extend the non-clustered index function by adding non-key columns to the leaf level of the non-clustered index. By including non-key columns, you can create non-clustered indexes that overwrite more queries. When all the columns in the query are included as key columns or non-key columns in the index, indexes with inclusive non-key columns can significantly improve the query performance. This improves the performance, because the query optimizer can locate all column values in the index, and does not access the table or clustered index data, thus reducing disk I/O operations.

Avoid adding unnecessary columns. Adding too many index columns (key columns or non-key columns) will adversely affect the performance and should be used properly. It can be used in combination with clustered index or composite index to expand index coverage, but it is unlikely that all columns have index coverage, disk overhead and the time overhead of re-calculation of indexes when data is inserted into updat are huge. In short, a reasonable index design is based on the analysis and prediction of various queries.ProgramIn combination, the best optimization solution can be generated.

To continue, first create an experiment table Table1:

Create two indexes:

1. The primary key ID is clustered index.

2. Non-clustered index non-clustered index is created on the age column, including the column count.

Create nonclustered index [CNT] on [DBO]. [Table1]
(
[Age] ASC
)
Include ([count])
On [primary]

In our test SQL statement, four records are retrieved from 0.1 million records.

1. Select*From Table1 where age <100;
2. SelectCountFrom Table1 where age <100;

Let's see how efficient it is:

Disk Io and time:

Actual execution plan:

The performance is more than 20 times different. Why?

OriginalStatement 2 select countWithin the index coverage scope, the query optimizer can locate all column values in the index, and does not access the table or clustered index data, thus reducing disk I/O operations. WhileFirst select *All fields are selected. One of the fields name is not covered by the index (neither in the clustered index column nor in the non-clustered index overwrite column ), SQL Server can use multiple indexes for a table in the same query and merge Multiple indexes (using joinAlgorithm), So that the search keyword overwrites a query. The query analysis optimizer automatically selects the query analysis optimizer. The above Execution Plan is the result of optimization and is still 20 times slower than the second index seek.

Then I changed the SQL statement to get most of the data from the 0.1 million records (99900 records are returned), and changed the smaller number to the greater number:

1. Select*From Table1 where age> 100;
2. SelectCountFrom Table1 where age> 100;

Look at the results:

Disk Io and time:

Actual execution plan:

There is no doubt that the second index overwrites fast. However, the execution plan of the first sentence is different. the SQL Server query and analysis optimizer selects different policies and changes them to clustered index scanning. As mentioned above, SQL Server can use multiple indexes for a table in the same query and combine multiple indexes (using the join algorithm) so that the search keyword can overwrite one query. The query analysis optimizer automatically selects the query analysis optimizer. The preceding Execution Plan is the result of optimization.

Why are the indexing schemes selected by sqlserver when the result set is small and the result set is large?

(From yuanyou High Strength: When the where age is less than 100, because the number of results set records is small, SQL Server first index seek (to obtain a smaller result set) then nested loops with clustered index seek. When the where age is greater than 100, due to the large number of records, SQL Server considers that the result is faster by directly scanning the Page Based on the clustered index leaf-level page linked list. In any case, the ultimate goal is to minimize logical IO in the case of the same result set .)

I'm afraid I like it.Select *Students also need to exercise restraint in using SQL Server.Select *The cost is very high. Of course, there is no problem with such SQL statements (where exists (select * from...), because the SQL Server query analysis optimizer knows this intelligently.Select *Non-peerSelect *.

A database is a very complex system. Even if you are not a database expert or an application developer, it is helpful to know more about sqlserver, the rational index design is based on the analysis and prediction of various queries. Only by correctly combining the index with the program can the best optimization solution be produced.

Related reading:

• SQL Server INDEX OPTIMIZATION practices

You mayArticleInterested:

• Do you have any questions for programmers to start a website?
• CTO talks about the technical architecture change of Douban.com and Intranet
• Ajax delayed asynchronous loading sidebar + server-side cache Ajax output
• The level-2 drop-down menu is hidden, and CSS setting of Z-index does not work in IE. Solution
• Simple js to implement text with the drive lamp effect (jquery is not required)
• Timeout setting and event processing for jquery and extjs