Index access method and INDEX OPTIMIZATION

To understand the index access method, you must first know the index structure.

1.Table and Index Structure 

Page

Page is the basic unit for SQL Server to store data. It is 8 kb in size and can store table data, index data, execution plan data, allocation bitmap, and available space information. The page is the smallest I/O unit that SQL server can read and write. Even if a row of data is read, it loads the entire page into the cache and reads data from the cache.

Zone

A partition is a unit of distribution composed of eight consecutive pages.

Heap

A heap is a table without clustered indexes. Its data is not stored in any order.

The unique structure of the data associated with a heap is a bitmap page called index allocation ing (IAM). When scanning an object, SQL server uses the iam page to traverse the data of the object.

Clustered index:

All data is maintained in its leaf-level table and stored in the leaf-level index in the order of index key columns. At the top layer of the index page level, the index also maintains other levels. Each level has an overview of the levels below it. Each row on a non-leaf level index points to the entire page at its next level.

Non-clustered index on the stack:

The only difference from clustered indexes is that the leaf-level pages of non-clustered indexes only contain index key columns and row delimiters pointing to specific data rows. After a specific data row is searched through an index, sqlserver must perform the RID lookup operation after the seek operation. This operation is used to read pages containing data rows.

Non-clustered indexes on Clustered tables:

The row locator pointing to a specific data row is the value of the clustering key, not the RID.

 

2.Index access method 

Table scan/unordered clustered index Scan

When the table does not have an index, all data pages in the table are continuously scanned. Based on the iam page of the table, SQL Server instructs the disk to scan the partitions of the table in the physical order.

When a table contains a clustered index, the unordered clustered index scan is used.

Example SQL: Select orderid, custid, empid, shipperid, orderdate, filler from DBO. Orders

Index: Create clustered index idx_cl_od on DBO. Orders (orderdate );

Table orders structure: orderid, custid, empid, shipperid, orderdate, filler

Overwrite non-clustered index Scan

SQL Server only accesses the index data to find all the data that meets the query needs, without accessing the complete data rows.

Example SQL: Select orderid from DBO. Orders

Index: alter table [DBO]. [orders] add constraint [pk_orders] primary key nonclustered

(

[Orderid] ASC

)

Sequential clustered index Scan

Perform a full scan on the leaf level of the clustered index according to the link list.

Example SQL: Select orderid, custid, empid, shipperid, orderdate, filler from DBO. Orders order by orderdate

Index:

Create clustered index idx_cl_od on DBO. Orders (orderdate );

Unlike unordered index scanning, the performance of ordered scanning depends on the index fragmentation level.

Sequential coverage of non-clustered index Scanning

Similar to an ordered clustered index scan, but when a non-clustered index scan is covered, because it involves fewer pages, its cost is definitely lower than that of clustered index scan.

Example SQL: Select orderid, orderdate from DBO. Orders order by orderid

 

Non-clustered index search + ordered local scan + lookups

It is usually used for small-scale queries, and the non-clustered index used does not cover this query.

Example SQL: Select orderid, custid, empid, shipperid, orderdate, filler from DBO. orders where orderid between 101 and 200

 

Unordered non-clustered index scan + lookups

The optimizer selects this access method when:

1. This query is highly selective.
2. The index most suitable for a query does not cover the query
3. The index does not maintain the queried key in order.

Example SQL: Select orderid, custid, empid, shipperid, orderdate, filler from DBO. orders where custid =''

 

Clustered index search + ordered local Scan

The optimizer typically uses this method to filter the range queries by the first key column of the clustered index.

Example SQL: Select orderid, custid, empid, shipperid, orderdate, filler from DBO. orders where orderdate = '201312'

The advantage of this method is that it does not involve lookups.

 

Overwrite non-clustered index search + ordered local Scan

The access method is similar to the previous one. The only difference is non-clustered index. Compared with the previous access method, this method has the advantage that the leaf-level pages of non-clustered indexes can accommodate more rows than the leaf-level pages of clustered indexes.

Example SQL: Select shipperid, orderdate, custid from DBO. Orders

Where shipperid = 'C' and orderdate> = '20160301' and orderdate <'20160301'

Create nonclustered index idx_nc_sid_od_cid

On DBO. Orders (shipperid, orderdate, custid );

 

3.Index optimization grade

SQL to be optimized: Select orderid, custid, empid, shipperid, orderdate, filler from DBO. orders where orderid> 999001

1. This table does not have any indexes: this plan will use table Scanning

2. Next we will optimize it to create a non-clustered overwriting index without using the Filter column (orderid) as the first filter column:

Create index idx_nc_od_ I _oid_cid_eid_sid

On performance. DBO. Orders (orderdate)

Include (orderid, custid, empid, shipperid );

The optimizer will use overwrite non-clustered index Scanning

3. Next optimization: create a non-clustered index that does not overwrite the query

Create nonclustered index idx_nc_od_ I _oid

On DBO. Orders (orderdate)

Include (orderid );

The optimizer will use non-clustered index scanning + lookup. This query depends on selection. The higher the selectivity, the higher the performance.

4. Continue optimization: create a non-clustered non-covered index on orderid,

Create unique nonclustered index idx_unc_oid

On DBO. Orders (orderid );

The optimizer will use non-clustered index search + Lookup

5. Continue optimization: Create a clustered index on orderid

Create unique clustered index idx_cl_oid on DBO. Orders (orderid );

This plan mainly does not involve lookup,

6. Continue optimization:

The best optimization should be to use orderid as the key column, and define other columns as non-clustered indexes that include non-key columns.

Create unique nonclustered index idx_unc_oid_ I _od_cid_eid_sid

On DBO. Orders (orderid)

Include (orderdate, custid, empid, shipperid );

The logic of this plan is similar to the previous one, except that non-clustered indexes cover less pages that are read by sequential local scans.

Index access method and INDEX OPTIMIZATION