We can look at its overall availability; that is: are our users able to get the information they need when they need it? We can consider its concurrency; how many users can query the database at one time? We can even watch the number of transactions that occur per second to gauge its level of activity. Often for our users however, it comes down to one very simple, but somewhat nebulous, measurement — perceived responsiveness.
Or put another way, when a user issues a query does he receive his results without having to wait too long? Of course, there are many factors that influence this.
There is the hardware itself and how its configured. There is the physical implementation of the database; the logical design of the database; and even how the queries themselves are written. In this article however, I will focus on one specific technique that can be used to improve performance — creating covering indexes.
Indexes allow SQL Server to quickly find rows in a table based on key values, much like the index of a book allows us to easily find the pages that contain the information we want. There are two types of indexes in SQL Server, clustered and nonclustered indexes. A clustered index is an index whose leaf nodes, that is the lowest level of the index, contain the actual data pages of the underlying table.
Hence the index and the table itself are, for all practical purposes, one and the same. Each table can have only one clustered index. Many database designs make prolific use of clustered indexes. Figure 1. Clustered index execution plan. Nonclustered indexes use a similar methodology to store indexed data for tables within SQL Server. However in a nonclustered index, the lowest level of the index does not contain the data page of the table.
Instead, it contains the information that allows SQL Server to navigate to the data pages it needs. For tables that have a clustered index, the leaf node of the nonclustered index contains the clustered index keys.
If the underlying table does not have a clustered index this data structure is known as a heap , the leaf node of the nonclustered index contains a row locator to the heap data pages. In the following example, a nonclustered composite index has been created on the Customers table as described in the following Transact-SQL code.
This can be seen in the Execution Plan in the following figure. Figure 2. Nonclustered index execution plan.
As illustrated in the preceding example, nonclustered indexes may be employed to provide SQL Server with an efficient way to retrieve data rows. However, under some circumstances, the overhead associated with nonclustered indexes may be deemed too great by the query optimizer and SQL Server will resort to a table scan to resolve the query.
SQL Server uses a Key Lookup to retrieve non-key data from the data page when a nonclustered index is used to resolve the query. That is, once SQL Server has used the nonclustered index to identify each row that matches the query criteria, it must then retrieve the column information for those rows from the data pages of the table. Since the leaf node of the nonclustered index contains the key value information for the row, SQL Server must navigate through the clustered index to retrieve the columnar information for each row of the result set.
In this example, SQL Server choose to do this using a nested loop join type. Digging a little deeper into the Key Lookup operation, we can see why. If the index has a good clustering factor —that is, if the respective rows are well clustered in a few table blocks—the advantage may be significantly lower. Besides the clustering factor, the number of selected rows limits the potential performance gain of an index-only scan. If you select a single row, for example, you can only save a single table access.
Considering that the tree traversal needs to fetch a few blocks as well, the saved table access might become negligible. The performance advantage of an index-only scans depends on the number of accessed rows and the index clustering factor. The index-only scan is an aggressive indexing strategy. Do not design an index for an index-only scan on suspicion only because it unnecessarily uses memory and increases the maintenance effort needed for update statements.
In practice, you should first index without considering the select clause and only extend the index if needed. Index-only scans can also cause unpleasant surprises, for example if we limit the query to recent sales:. Without looking at the execution plan, one could expect the query to run faster because it selects fewer rows.
The where clause, however, refers to a column that is not in the index so that the database must access the table to load this column. The table access increases the response time although the query selects fewer rows. The relevant factor is not how many rows the query delivers but how many rows the database must inspect to find them.
Check the execution plan before extending queries. If an index can no longer be used for an index-only scan, the optimizer will choose the next best execution plan.
That means the optimizer might select an entirely different execution plan or, as above, a similar execution plan with another index.
These estimations are, however, purely arbitrary because the query uses bind parameters. New rows are always appended to the end of the table as long as there are no rows deleted. The table order therefore corresponds to the index order because both are roughly sorted chronologically—the index has a good clustering factor.
When using an index with a good clustering factor, the selected tables rows are stored closely together so that the database only needs to read a few table blocks to get all the rows. Using this index, the query might be fast enough without an index-only scan.
In this case we should remove the unneeded columns from the other index again. Some indexes have a good clustering factor automatically so that the performance advantage of an index-only scan is minimal. In this particular example, there was a happy coincidence. The optimizer was therefore able to limit the performance impact of this change.
It is, however, also possible to prevent an index only scan by adding columns to other clauses. However adding a column to the select clause can never open a new access path which could limit the impact of losing the index-only scan. Add comments that remind you about an index-only scan and refer to that page so anyone can read about it. Generally speaking, building a covering index is more efficient than building a composite index.
When there are multiple conditional columns behind where, you can consider building a composite index on these columns. Of course, this is just a simple description, there are many factors that need to be considered when indexing. Can't tell you which one should be used, what type of index to build depends on the actual data and your needs. Of course, you can try according to the basic principles I mentioned above, and then see which index is more efficient.
Or publish your data and requirements when you need to build an index, and let the experts advise you. If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. No, that is not correct. A clustered index can also have a composite key.
And a clustered index is covering for all queries against the table. In difference to a non-clustered index which is only covering for a subset of possible queries against the table. With the exception of NC indexes that comprise all columns in the table.
Delete duplicated lines based on 2 primary key. Display current hour Production. Skip to main content.
0コメント