Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is it really unused index? Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 1:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 1,076, Visits: 3,054

Hi,

Can you confirm, Is it really unused index?

UserSeek - 16951
UserScans - 11803
UserLookup- 0
UserUpdates - 62992

rgds
ananda
Post #1439696
Posted Monday, April 8, 2013 4:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
Since there's a non-zero number of seeks and scans, no that is not unused (it's been used for seeks and scans)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1439742
Posted Monday, April 8, 2013 5:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 1,076, Visits: 3,054

Thanks for reply.. some of the index result showing as below, it is treated as used index?

Index A

UserSeek - 193121
UserScans - 0
UserLookup- 0
UserUpdates - 49183

Index B

UserSeek - 0
UserScans - 0
UserLookup- 0
UserUpdates - 128920
Post #1439754
Posted Monday, April 8, 2013 5:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:54 PM
Points: 184, Visits: 361
Hi,

Index A is indeed in use as Gila suggested earlier.
Index B not being used for query, but it may be important if it is clustered index or Primary Key.
I use below query to evaluate how usefull is my index.

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + 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] ASC;

Post #1439766
Posted Monday, April 8, 2013 6:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
Bear in mind that those views are only since the last time SQL was started, not all time.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1439796
Posted Monday, April 8, 2013 7:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:45 AM
Points: 1,104, Visits: 696
Also, be aware that some indexes exists for maintain constraints - unique indexes. And it could looks like unused...
Post #1439804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse