DMV:Indexes

To check of any primary key is a NOT a clustered index


select OBJECT_SCHEMA_NAME(so.[object_id]) as schemaname ,

so.name as TableName ,

si.name as IndexName,

si.type as IndexType,

si.is_primary_key

from sys.indexes si

inner join sys.tables so on

si.[object_id] = so.[object_id]

where si.type in (0,2)

and si.is_primary_key = 1

order by so.name

Guidelines for index-fragmetnation

Some starting points to keep in mind for index fragmentation

•  If an index has less than 1000 pages and is in memory, don't bother removing fragmentation
• if the index has:
  • less than 5% logical fragmentation, don't do anything
  • between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE)
  • more than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX ... REBUILD)

 The guidelines are taken from 
http://sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx

Find missing indexes

--missing indexes on a single table
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )AS [index_advantage] ,migs.last_user_seek ,mid.[statement] AS [Database.Schema.Table] ,mid.equality_columns ,mid.inequality_columns ,mid.included_columns ,migs.unique_compiles ,migs.user_seeks ,migs.avg_total_user_cost ,migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )ON mig.index_handle = mid.index_handleWHERE
AND mid.database_id = DB_ID() mid.[statement]= '[statusupdates].[dbo].[Referrals]'ORDER
Look at the last_user_seek column and see how frequently it is seeked. This is an important factor to decide whether the suggested index is needed or not


I created the following index based on the equality column and there was no include column NONCLUSTERED INDEX IDX_referrals_Account
CREATE
ON Referrals (Account,Hospitalcode,Deleted,uploaded )
GO
/* to see when was the last index was updated*/
DBCC SHOW_STATISTICS ('referrals', 'IDX_referrals_hospitalcode') GO

/* after adding the new index I updated the statistics of the table */

EXEC
GO sp_updatestats
GO

Finding the bad indexes

Indexes are considered bad if the total writes > total reads



--Find bad indexes for a single table (writes > reads)--find bad indexs SELECT
user_seeks OBJECT_NAME(s.object_id) AS 'Table Name', i.name AS 'Index Name', i.index_id, user_updates AS 'Total Writes', + user_scans + user_lookups AS 'Total Reads', user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference' FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 AND s.database_id = DB_ID() AND user_updates > ( user_seeks + user_scans + user_lookups ) AND i.index_id > 1 ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads'

--Find bad indexes for a single table (writes > reads)SELECT OBJECT_NAME(s.object_id) AS 'TableName',i.name AS 'IndexName',i.index_id,SUM(user_seeks) AS 'User Seeks',SUM(user_scans) AS 'User Scans',SUM(user_lookups) AS 'User Lookups',SUM(user_seeks + user_scans + user_lookups) AS 'Total Reads',SUM(user_updates) AS 'Total Writes'FROM
sys.dm_db_index_usage_stats AS sINNER JOIN sys.indexes AS i ON s.object_id = i.object_idAND i.index_id = s.index_idWHERE
OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1AND s.database_id = DB_ID()AND OBJECT_NAME(s.object_id) = 'referrals'GROUP BY OBJECT_NAME(s.object_id),i.name,i.index_idORDER BY 'Total Writes' DESC,'Total Reads' DESC ; ASC ;