Prettylib-tuoteratkaisut - Wiki
Turhat indeksit
Allaolevalla saa selville käyttämättömät indeksit SQL Serveristä.
The following statement lists all the indexes that have not been used. This also generates the DROP index statement which can come handy when deleting the indexes.
SELECT o.name, indexname=i.name, i.index_id , reads=user_seeks + user_scans + user_lookups , writes = user_updates , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) , CASE WHEN s.user_updates < 1 THEN 100 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates END AS reads_per_write , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement' FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id INNER JOIN sys.objects o on s.object_id = o.object_id INNER JOIN sys.schemas c on o.schema_id = c.schema_id WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 AND s.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000 ORDER BY reads