select OBJECT_NAME(us.object_id) as tableName, i.name as indexName, (us.user_seeks+us.user_scans+us.user_LookUps) as TotalRead, us.user_updates as TotalWrite, us.user_updates*100/case (us.user_seeks+us.user_scans+us.user_LookUps) when 0 then 1 else (us.user_seeks+us.user_scans+us.user_LookUps) end as [Write/Read_Percentage], (us.user_seeks+us.user_scans+us.user_LookUps)*100/case us.user_updates when 0 then 1 else us.user_updates end as [Read/Write_Percentage], us.last_user_seek, us.user_seeks, CASE us.user_seeks WHEN 0 THEN 0 ELSE us.user_seeks*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS SeekPercentage, us.last_user_scan, us.user_scans, CASE us.user_scans WHEN 0 THEN 0 ELSE us.user_scans*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS ScanPercentage, us.last_user_LookUp, us.user_LookUps, us.last_user_update, us.user_updates --into Alper_index_usage_statistics FROM sys.dm_db_index_usage_stats us INNER JOIN sys.indexes i ON i.object_id=us.object_id and i.index_id = us.index_id AND i.type > 0 -- Ignore heaps AND i.is_disabled = 0 -- Disabled indexes aren't used anyway AND i.is_primary_key = 0 -- Exclude PK => FK constraints / part of business logic AND i.is_unique = 0 -- Exclude unique indexes => part of business logic -- asagidaki AND deki bolumu ben ekledim, read/write orani 50 den kucuk olanlari getir AND (us.user_seeks+us.user_scans+us.user_LookUps)*100/case us.user_updates when 0 then 1 else us.user_updates end < 50 AND i.name like '%DBA%' WHERE us.database_id = DB_ID('GURMEN') order by 6 desc
-- ISMAIL ADAR KITABINDAN SELECT TOP 250 DB_NAME (dm_ius.database_id) AS Dbname ,o.[name] AS ObjectName ,i.[name] AS IndexName ,i.index_id AS IndexID ,dm_ius.user_seeks AS UserSeeks ,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 i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.[name] LIKE '%dba%' ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
