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

Index Operational stats? Expand / Collapse
Author
Message
Posted Wednesday, March 5, 2014 5:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:00 AM
Points: 1,054, Visits: 2,974
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'
Post #1547726
Posted Wednesday, March 5, 2014 9:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,235, Visits: 12,988
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1547907
Posted Wednesday, March 5, 2014 11:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,894, Visits: 7,138
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; They'll drag you down to their level and beat you with experience"
Post #1547944
Posted Thursday, March 6, 2014 3:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:00 AM
Points: 1,054, Visits: 2,974
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?
Post #1548139
Posted Thursday, March 6, 2014 5:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,235, Visits: 12,988
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1548195
Posted Thursday, March 6, 2014 5:05 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 @ 3:00 AM
Points: 42,774, Visits: 35,871
Unique indexes should never be considered unused as they are enforcing the uniqueness of the column(s).


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 #1548196
Posted Thursday, March 6, 2014 5:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:00 AM
Points: 1,054, Visits: 2,974
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




Post #1548226
Posted Thursday, March 6, 2014 6:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,894, Visits: 7,138
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; They'll drag you down to their level and beat you with experience"
Post #1548238
Posted Saturday, March 8, 2014 12:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:00 AM
Points: 1,054, Visits: 2,974

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
Post #1548951
Posted Saturday, March 8, 2014 6:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,894, Visits: 7,138
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; They'll drag you down to their level and beat you with experience"
Post #1548961
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse