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) )
DECLARE @pDATABASENAME AS SYSNAME;

DECLARE @pTABLENAME AS SYSNAME;

DECLARE @pDATABASEID AS INT;

DECLARE @pOBJECTID AS INT;

SET @pDATABASENAME = 'AdventureWorks2008';

SET @pTABLENAME = 'Production.Document';

SELECT @pDATABASEID = DB_ID(@pDATABASENAME);

SELECT @pOBJECTID = OBJECT_ID(@pTABLENAME);

IF (@pDATABASEID & @pOBJECTID) IS NOT NULL
    BEGIN
        SELECT *
        FROM   sys.dm_db_index_physical_stats (@pDATABASEID, @pOBJECTID, NULL, NULL, NULL);
        DBCC IND (@pDATABASEID, @pOBJECTID, 1);
    END
ELSE
    PRINT 'MAY BE INVALID DATABASE OR TABLE NAME PASSED ';

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:

PRINT 'Missing Indexes: ';
PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might ';
PRINT 'be seen if the index was created. This is a unitless number, and has meaning only relative ';
PRINT 'the same number for other indexes. The measure is a combination of the avg_total_user_cost, ';
PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.';
PRINT '';
PRINT '-- Missing Indexes --';

SELECT   CONVERT (VARCHAR, getdate(), 126) AS runtime,
         mig.index_group_handle,
         mid.index_handle,
         CONVERT (DECIMAL (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
         'CREATE INDEX missing_index_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE
WHEN mid.equality_columns IS NOT NULL  AND mid.inequality_columns IS NOT NULL THEN ',' ELSE ''
END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
         migs.*,
         mid.database_id,
         mid.[object_id]
FROM     sys.dm_db_missing_index_groups AS mig
         INNER JOIN
         sys.dm_db_missing_index_group_stats AS migs
         ON migs.group_handle = mig.index_group_handle
         INNER JOIN
         sys.dm_db_missing_index_details AS mid
         ON mig.index_handle = mid.index_handle
WHERE    CONVERT (DECIMAL (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

PRINT '';

Least Used Indexes:

SELECT TOP 10
        o.name AS ObjectName ,
        i.name AS IndexName ,
        i.index_id AS IndexID ,
        dm_ius.user_seeks AS UserSeek ,
        dm_ius.user_scans AS UserScans ,
        dm_ius.user_lookups AS UserLookups ,
        dm_ius.user_updates AS UserUpdates ,
        p.TableRows
FROM    sys.dm_db_index_usage_stats dm_ius
        INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
                                    AND dm_ius.object_id = i.object_id
        INNER JOIN sys.objects o ON dm_ius.object_id = o.object_id
        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
        INNER JOIN ( SELECT SUM(p.rows) TableRows ,
                            p.index_id ,
                            p.object_id
                     FROM   sys.partitions p
                     GROUP BY p.index_id ,
                            p.object_id
                   ) p ON p.index_id = dm_ius.index_id
                          AND dm_ius.object_id = p.object_id
WHERE   OBJECTPROPERTY(dm_ius.object_id, 'IsUserTable') = 1
        AND dm_ius.database_id = DB_ID()
        AND i.type_desc = 'nonclustered'
        AND i.is_primary_key = 0
        AND i.is_unique_constraint = 0
        AND p.TableRows > 1000
ORDER BY ( dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups ) ASC
GO

You may also like...