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