Indexes – Pointers to Consider
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