SELECT	OBJECT_NAME(id) ObjectName,
		name,
		STATS_DATE(id, indid) AS DateTaken,
		rowmodctr
FROM sys.sysindexes
WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE()) 
ORDER BY DateTaken;

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

quote:

---

Originally posted by tkizer

Yeah this isn't worth your time. Statistics are small. What you should be looking at instead is unused indexes, but you have to proceed with caution.

Here is a script that will show you index usage. What you are looking for here is reads=0 and high number of writes. I've dropped indexes that had low reads but not reads=0 because I knew there was another index that would takes its place for those few reads. The caution part of this is you need to run this script once an entire workload has been completed. This DMV data gets wiped out when SQL is restarted. So you definitely would not base your decisions when SQL was recently restarted. If you restarted SQL a month ago, but there's some quarterly report that hasn't yet run and might need the index you are considering to drop, you may end up in a world of hurt. This happened to me. There was a process that only ran monthly, and I ran the script 3 weeks after the restart as I thought we had completed an entire workload. Then that process ran. CPU was 90-100% and causing widespread issues. I was paged on a weekend to figure out what was going on. I saw the long-running query was doing a table scan and then remembered I had dropped an index on that table. I knew what the issue was. I killed the process, added the index back and then restarted that process. Everything was okay. This was an extremely critical database with extreme performance requirements (most queries were required to finish within 300 milliseconds). I learned my lesson.

Proceed with caution!

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

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---

What you are looking for here is reads=0 and high number of writes

UPDATE MyTable
SET column1=value1
WHERE column2=value2;

quote:

---

Originally posted by tkizer

The writes column is regarding keeping the index up to date due to DML (INSERT/UPDATE/DELETE). It is not in regards to reading the data pages due to a WHERE clause.

An index on column1 for your provided query would not be used. An index on column2 would. column2's index would be read, and column1's index would have to be maintained (writes) for your example. So column1's index is useless here and causing overhead.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---

quote:

---

Originally posted by tkizer

There is still risk as how do you know if an entire business cycle/workload has been run since SQL has been restarted. You could have a yearly process. At my last company, we rebooted the database servers almost every month as they had a policy of being up-to-date for the monthly Microsoft security patches. That doesn't give you enough info if you have processes or any workload that could run outside of those 30 days. What I did was waited 3-4 months, saving the output of this query each month and comparing. If the same index kept showing reads=0, then I made plans to have it dropped. I made sure that a load test was conducted in our performance lab.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT  
o.name as [Object Name],
	s.index_id as [Index ID],
   	i.name as [Index Name],     
	i.type_desc as [Index Type],
	s.user_seeks + s.user_scans + s.user_lookups as [Total Queries Which Read],        
	s.user_updates [Total Queries Which Wrote] ,       
	ps.row_count as [Row Count],	       
	CASE WHEN s.user_updates < 1 THEN 100
		ELSE ( s.user_seeks + s.user_scans + s.user_lookups ) / s.user_updates * 1.0      
	END AS [Reads Per Write] 
FROM    sys.dm_db_index_usage_stats s
JOIN sys.dm_db_partition_stats ps on s.object_id=ps.object_id 
	and s.index_id=ps.index_id
JOIN sys.indexes i ON i.index_id = s.index_id
	AND s.object_id = i.object_id
JOIN sys.objects o ON s.object_id = o.object_id

JOIN sys.schemas c ON o.schema_id = c.schema_id
WHERE o.name NOT LIKE 'sys%' 
	--AND i.name NOT LIKE 'PK_%'
AND i.type_desc ='NONCLUSTERED' 
ORDER BY [Total Queries Which Read] ASC, [Row Count] DESC;

quote:

---

Originally posted by tkizer

When I run that query, I don't see any "dupes". Not sure why you are seeing dupes, but I would think it's due to object names being the same but with different schema names. Or maybe you've got partitioned tables, which the query I provided handles that condition.

But why use a different query? Just use the one I provided. I didn't write it. I stole it from here: http://www.toadworld.com/platforms/sql-server/w/wiki/10062.find-indexes-not-in-use.aspx. The author is Brent Ozar.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---