March 5, 2014 at 5:24 am
Hi,
I have verified one of the production database as below query
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = A.[OBJECT_ID]
AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
But result is
LEAF_INSERT_COUNT - 6885
LEAF_UPDDATE_COUNT-3926
LEAF_DELETE_COUNT -0
as per above indication index were used insert and update operation, but not SELECT statement, can i consider that unused index?
One more question. Index may be performance degrade of transaction and cause of Lock Waits/sec > 0 and Lock Wait Time (ms) > 0 those counter non zero values over the time period, it might be blocking issues on database.
I am going to capture the blocking text and duration at Profiler and choose blocked process event, before start profiler can change the value of blocked process threshold = 10 at instance leve, what will be impact after chage the value 10? default value 0.
Thanks
ananda
Thanks
ananda'
March 5, 2014 at 9:56 am
Based on that query you aren't looking at range_scan_count or singleton_lookup_count so it looks like you don't know if this index is used by selects. I prefer to look at sys.dm_db_index_usage_stats. I wouldn't consider dropping the index on the basis of the current information that you have provided.
I wouldn't recommend running profiler against a production server as it has a negative performance impact. You can define the trace using profiler, script it, and run it as a server-side trace, which has a much lower impact on the server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2014 at 11:09 am
To aid what Jack has already said, use the DMV to get a better idea of whether or not the index is actually used...and remmber to keep in mind that these statistics are kept as a running total of since the last time the SQL service was restarted. So if you recently restarted your server, your results may be skewed and you might want to wait a longer period of time before making any decisions to drop an index.
A script I like to use (and I believe it came from SSC) is:SELECT ROW_NUMBER( ) OVER ( ORDER BY b.user_lookups DESC ) RANKING ,
DB_NAME() DBName ,
OBJECT_SCHEMA_NAME(a.object_id) + '.' + OBJECT_NAME(a.object_id) AS objectName ,
a.name ,
CASE WHEN is_unique = 1 THEN 'UNIQUE '
ELSE ''
END + a.type_desc [IndexDesc] ,
b.user_seeks ,
b.user_scans ,
b.user_lookups ,
b.user_updates ,
b.system_seeks ,
b.system_scans ,
b.system_lookups ,
b.system_updates ,
b.last_user_seek ,
b.last_system_seek
FROM sys.indexes AS a
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS b ON ( a.object_id = b.object_id
AND a.index_id = b.index_id
AND b.database_id = DB_ID()
)
WHERE a.name IS NOT NULL
AND OBJECT_SCHEMA_NAME(a.object_id) <> 'sys'
AND ( b.user_seeks = 0
AND b.user_scans = 0
AND b.user_lookups = 0
)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 6, 2014 at 3:28 am
Hi,
I checked unused index, As per that script result return value only two column in NONCLUSTERED
total 4 NONCLUSTERED index values as below and Remaining all the column values are 0 (Zero),
user_updates = 36361 - it is used for UPDATE SQL statement, so this is not unused index.
system_scans = 12
total 23 UNIQUE CLUSTERED index values apperad in system_scans column and Remaining all the column values are 0 (Zero), so will it be unused index?
March 6, 2014 at 5:02 am
ananda.murugesan (3/6/2014)
Hi,I checked unused index, As per that script result return value only two column in NONCLUSTERED
total 4 NONCLUSTERED index values as below and Remaining all the column values are 0 (Zero),
user_updates = 36361 - it is used for UPDATE SQL statement, so this is not unused index.
system_scans = 12
total 23 UNIQUE CLUSTERED index values apperad in system_scans column and Remaining all the column values are 0 (Zero), so will it be unused index?
The user_updates column means the number of times a user action caused a modification of the index, not how many times the index was used to satisfy an update query. If there are more user updates by a significant amount than there are user seeks then the resources used to maintain the index structure may be greater than the benefit the index provides. Glenn Berry, Jason State, and Kendra Little all have scripts on their blogs that do a pretty good job of identifying indexes that may be candidates for dropping or modification.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 6, 2014 at 5:05 am
Unique indexes should never be considered unused as they are enforcing the uniqueness of the column(s).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2014 at 5:58 am
Jack Corbett Thank you for reply..
identifying indexes that may be candidates for dropping or modification? user_update values modified frequently.
Identified that NC INDEX on table,
SP inside, there is one SELECT complex query written by developer also Identified NC INDEX used this SELECT statement .
In SP side UNCOMMITTED Isolation level already mentioned,
Is the Chance for dropping or modification NC INDEX Due to Isolation level?
Thanks
ananda
March 6, 2014 at 6:25 am
Furthermore, check out Brent Ozar's sp_BlitzIndex (as Jack mentioned) - it does a good job of isolating potential indexes that are not used, and/or ones that are potentially duplicated: http://www.brentozar.com/blitzindex/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 8, 2014 at 12:37 am
Hi MyDoggieJessie, thank you for giving such a wonderfull information for finding potential index already exists in database. Mr.Brent Ozar's, sp_BlitzIndex - it is very useful for find idex issues.
Aggressive Indexes: Recent Contention Information, there is one PK clustered index took too high
Reads: 291,840 (256,852 seek 2,138 scan 32,850 lookup) Writes:13,936
Row lock waits: 3; total duration: 10 minutes; avg duration: 3 minutes; Lock escalation attempts: 1,172; Actual Escalations: 0.
Could you suggestion me, how to reduce the total duration 10 minutes?
Thanks
ananda
March 8, 2014 at 6:07 am
We would need to see the queries that are causing it. Could be UPDATE statements seeing that there's frequent rowlocks but just shooting in the dark here and haven't yet finished my first cup of coffee.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply