Index Pointers

Indexes  – Pointers to Consider

Max index size – 16 columns or 900 bytes whichever comes first
Page Size – 8 KB – 8192 Bytes 
Storage Available = 8192 Bytes – 96 Bytes for non-data storage = 8096 bytes / Page for Data.
Useful DMV:
select * from sys.dm_db_index_physical_stats( NULL , NULL , NULL , NULL , NULL )
select * from sys.dm_db_index_physical_stats( DB_ID , OBJECT_ID , INDEX_ID , PARTITION_ID , (LIMITED,SAMPLED,DETAILED) )

DBCC IND 

DBCC IND command provides the list of pages used by the table or index. The command provides the page numbers used by the table along with previous page number,next page number. The command takes three parameters.

DBCC ind( <database_name>, <table_name>, non clustered index_id*) 

The third parameter can either be a Non Clustered index id( provided by sys.indexes ) or 1,0,-1,-2.

DBCCPAGE: 

Next undocumented command we would be seeing is DBCC PAGE:

DBCC PAGE takes the page number as the input parameter and displays the content of the page.Its almost like opening database page with your hands and viewing the contents ofthe page.

Syntax: 

DBCC page(<database_name>, <fileid>, <pagenumber>, <viewing_type>) 

 

DBCC PAGE takes 4 parameters. They are database_name, fileid, pagenumber, viewing_type.Viewing_type parameter when passed a value 3 and displays the results in tabular format.If you are viewing a data page then the results are always in text format. For Index pages, when we pass the value 3 as parameter we get the results in a tabular format.DBCC PAGE command requires the trace flag 3604 to be turned on before its execution.

CLUSTERED INDEX:

SHORT SWEET(UNIQUE) STATIC( STABLE , NON-VOLATILE) JUST LIKE THE DREAM GIRLFRIEND

Needs to be UNIQUE.

If we build a clustered index without specifying UNIQUE Keyword, SQL Server guarantees uniqueness by adding 4 byte integer as hidden uniquifier column to the rows when necessary.

IF USING GUID, USE NEWSEQUENTIALID() RATHER THAN TAKING VALUE FROM APP TO AVOID FRAGMENTATION.

NON-CLUSTERED INDEX

RID ON HEAP – FileID : PageID : SlotNo ( 8 Byte RID )

Leaf level contains the pointer to the data ( RID or clustering Index Key )

INCLUDE COLUMNS

NON-KEY Columns included in the leaf level of data pages to cover the query.

Create Missing Indexes:

Least Used Indexes: