SQL Server Index usage and Optimization

Index instructions

Digress:
Yesterday, we were depressed. When we maintained a customer's database system, we found that the system was processing data and there was a false death.
At first, Because of software problems, I was too busy to copy the file for a whole afternoon. Later I got it at 7 o'clock, and I suddenly remembered the index problem.
Then I used the following command:

DBCC Dbreindex ( ' Tablename ' , '' , 80 )

. I am depressed and dizzy. I made such a mistake myself.
This error is exactly why I want to write this post. I hope to solve some practical problems for you.

Question:
The important purpose of creating an appropriate index is to improve the query efficiency.
Here are two types of indexes:

Help description Aggregation
The clustered index sorts and stores the data rows based on the key values of data rows in the table. Because data rows are stored in the sorting order based on the clustered index key, clustered indexes are very effective for row search. Each table can have only one clustered index, because data rows can only be stored in one order. Data rows constitute the lowest level of clustered indexes.
Data rows in a table are stored in order only when the table contains a clustered index. If a table does not have clustered indexes, its data rows are stored in a stacked manner.
Non-aggregation
Non-clustered indexes have a completely independent structure of data rows. The lowest row of the non-clustered index contains the key value of the non-clustered index, and each key value item has a pointer to the data row containing the key value. Data rows are not stored in the order of non-clustered keys.
In a non-clustered index, the pointer from the index row to the data row is called the row locator. The structure of the row positioner depends on whether the data page is stored in a stack or aggregation mode. For a stack, the row locator is a pointer to a row. For tables with clustered indexes, the row locator is the clustered index key.

Examples show the benefits of creating an index.

-- Test Table Dtest (no index)
Select   Identity ( Int , 1 , 1 ) As   [ ID ] , B. [ ID ]   As Tkey, B. [ Name ]   As   [ Tname ]   Into Dtest
From Master .. syscolumns As A, Master... sysobjects As B
-- Test Table ttest (index creation)
Select   Identity ( Int , 1 , 1 ) As   [ ID ] , B. [ ID ]   As Tkey, B. [ Name ]   As   [ Tname ]   Into Ttest
From Master .. syscolumns As A, Master... sysobjects As B

In my tests, the Dtest and ttest tables have more than 5 million records, which can meet the test requirements.

Operation
1. Set the field ID of table ttest as the primary key.
 

2. Create a non-clustered index for the tname column in table ttest.

3. Test the query statement efficiency.
In the query analyzer, enter:

Select   *   From Dtest Where Tname = ' Sp_activedirectory_obj '

Press Ctrl + L on the keyboard to display the expected execution plan. You can view the execution efficiency.

The estimated cost is 31.8.

In the query analyzer, enter:Select * FromTtestWhereTname='Sp_activedirectory_obj'

Press Ctrl + L on the keyboard to display the expected execution plan. You can view the execution efficiency.

The estimated cost is 28.5.
From this figure, we can see that the query efficiency has increased by about 10 percentage points after the index is used.

Next, let's look at the actual query results:
When no index is available, the time is 26 seconds.

Adds an index to the tname of a column. The time is 0 seconds.

It is obvious that the efficiency is improved.

Note:
1. Write queries that update as many rows as possible into a single statement, instead of updating the same row using multiple queries. Only one statement can be used to maintain the optimized index.
2. Use integer keys for clustered indexes. In addition, you can create clustered indexes on unique, non-empty, or identity columns to achieve performance gains.
3. create non-clustered indexes on all columns frequently used in queries. This maximizes the use of hidden queries
4. Re-indexing can re-organize the storage of index data (for clustered indexes, including table data) and clear fragments. As I mentioned earlier

DBCC Dbreindex ( ' Tablename ' , '' , 80 )

To reconstruct the index.

In actual application, we will find many problems and analyze them in detail. Here is a simple example to illustrate the benefits of using indexes.