Index Internals Part 2

SQL uses four basic operations on Index.
It can Scan ,Seek ,Lookup and Update the Index.

Scans :

Scans are divided in to Index Scan, Clustered Index scan and Table Scan
Index Scans : Reading all the leaf pages of a non-clustered index using the next and previous page pointers to navigate. Because non-clustered indexes generally have fewer pages in the leaf than a clustered index, this operation is usually cheaper than a clustered index scan.
Table Scans : This operation occurs for a heap table(A table without clustered index).The first page in the heap is located based on info in the system tables, and then the pages are read one by one, using the next and previous pointers in the page headers. This is generally an expensive operation and should be avoided where ever possible.
Clustered Index Scans : Basically the same operation as for a table scan, just on a table that has clustered index. This operation reads the leaf pages of the clustered index, using the next and previous page pointers in the page headers.Like with the table scan, this can be an expensive operation and should, wherever possible be avoided.

Note : For any type of Scans, it wont use the index b-tree Structure to locate data.It is directly reading all the leaf pages.

Seeks :
Seeks are two types Clustered Index seek and Non-Clustered Index seek
Clustered Index Seek : This operation uses the clustered index’s b-tree structure. The seek starts at the root of the tree and navigates down the levels of the index until it reached the leaf page(s) with the desired data.
Non-Clustered Index Seek : Much the same as the clustered index seek, just using a non-clustered index.

LOOKUPS :
Lookups are two types Key Lookup and RID Lookup
Key Lookup : It indicates that the nonclustered index that was used to locate the rows affected by the query did not have all the columns required by the query. The missing columns must be looked up from the clustered index.
A Key Lookup can be a bottleneck because for each row returned by the index seek, the key lookup does a clustered index seek returning a single row. For a small number of rows, that’s not  a problem, for hundreds of rows it can be a major bottleneck in the query.
RID Lookup : This Look up occurs when you  performed against a heap.In this case there is no clustered index to fetch the rows it has to use Row identifier.

Updates : 

Anytime that a row is changed, those changes must be made not only in the base table (clustered index or heap) but also in any index that contains the columns that were affected by the change.  This applies to insert, update and delete operations.

Tools for Analyzing Indexes :

To Understand Index structures fully, there are some tools that we are going to use.

Sys.dm_db_index_physical_stats: This DMV is used to find the fragmentation level and number of clustered indexes and non-clustered indexed exists in a table.

The syntax of this DMV is :

select * from sys.dm_db_index_physical_stats (database_id,object_id,Index_id,Partition_number,mode)

for example I want to see the information of a table production.product in adventureworks database.

select * from sys.dm_db_index_physical_stats (6,1429580131,null,null,'limited')

here limited is the mode and we have 3 modes in the DMV.

Limited,Detailed,Sampled

Limited Mode : The default operating mode of the DMV is called LIMITED.The LIMITED mode can only return the logical fragmentation of the leaf level plus the page count. The idea behind this option is to allow you to find the fragmentation of an index by reading the minimum number of pages, i.e. in the smallest amount of time.This option faster than using the DETAILED mode scan.It scans all pages for a heap, but only the parent-level pages for an index, which are the pages above the leaf-level.

Detailed Mode :  The DETAILED mode does two things:
•    Calculate fragmentation by doing a LIMITED mode scan
•    Calculate all other statistics by reading all pages at every level of the index
And so it's obviously the slowest.  

Sampled Mode : It does two things

• LIMITED mode scan
• If the number of leaf level pages is < 10000, read all the pages, otherwise read every 100th pages (i.e. a 1% sample)
  
  

However, if the index has less than 10,000 pages total, SQL Server converts SAMPLED to DETAILED.  

The Next DMV is SYS.DM_DB_INDEX_USAGE_STATS :

It will show you number of seeks,scans,lookups,updates on index  and last user seek,scan,lookup,updates on index from user queries or system queries.

select * from sys.dm_db_index_usage_stats

there are no parameters required.

The index usage stats DMV is cleared when SQL Server is restarted as well as when you detach/attach the database or when you backup/restore the database.

One more DMV Sys.dm_db_index_operational stats :

This DMV is used for lock escalations,page splits,lock contention and I/O statistics.
I will explain you one by one.
 The following query demonstrates identifying the top 3 objects associated with waits on page locks:
SELECT      TOP 3

            OBJECT_NAME(o.object_id, o.database_id) object_nm,

            o.index_id,

            partition_number,

            page_lock_wait_count,

            page_lock_wait_in_ms,
            case when mid.database_id is null then 'N' else 'Y' end as missing_index_identified
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) 
LEFT OUTER JOIN (SELECT DISTINCT database_id, object_id
        FROM sys.dm_db_missing_index_details) as mid
     ON mid.database_id = o.database_id and mid.object_id = o.object_id
ORDER BY page_lock_wait_count DESC

 Lock Escalations: 
You can use sys.dm_db_index_operational_stats to track how many attempts were made to escalate to table locks (index_lock_promotion_attempt_count), as well as how many times escalations actually succeeded (index_lock_promotion_count).  The following query shows the top three objects with the highest number of escalations:
SELECT      TOP 3

            OBJECT_NAME(object_id, database_id) object_nm,

            index_id,

            partition_number,

            index_lock_promotion_attempt_count,

            index_lock_promotion_count

FROM sys.dm_db_index_operational_stats

      (db_id(), NULL, NULL, NULL)

ORDER BY index_lock_promotion_count DESC




Page Split Tracking:Excessive page splitting can have a significant effect on performance.  The following query identifies the top 10 objects involved with page splits (ordering by leaf_allocation_count and referencing both the leaf_allocation_count and nonleaf_allocation_count columns).  The leaf_allocation_count column represents page splits at the leaf and the nonleaf_allocation_count represents splits at the non-leaf levels of an index:
SELECT      TOP 10

            OBJECT_NAME(object_id, database_id) object_nm,

            index_id,

            partition_number,

            leaf_allocation_count,

            nonleaf_allocation_count

FROM sys.dm_db_index_operational_stats

      (db_id(), NULL, NULL, NULL)

ORDER BY leaf_allocation_count DESC
 
Note : To run DMVs you need permission of CONTROL,VIEWSERVERSTATE,VIEWDATABASESTATE.

 Sys.indexes:

Another way to view existing indexes defined for a given table is to use the system view called "sys.indexes"
select * from sys.indexes it will give the indexes list in a database.
or you can also query indexes on a single table in a database.Here I used the adventureworks database.


SELECT * FROM sys.indexes WHERE object_id = (
   SELECT object_id FROM sys.tables WHERE name = 'product')

SP_helpindex :

Return information about indexes on a table or view.
Example : USE AdventureWorks;
GO
EXEC sp_helpindex N'Sales.Customer';
GO