January 26, 2015 at 4:41 am
Hi
I run a query on indxes . and this is 3 records of result :
TBL_1 --------- IX_1 user_seeks = 78 ------- User_Scans = 477 ------User_Lookup = 0 --------- User_Updates = 7707 -- Cost = (seek/update ) = 98
TBL_2 --------- IX_2 user_seeks = 6 ------- User_Scans = 2827 ------ User_Lookup = 0 --------- User_Updates = 314 -- Cost = (seek/update ) = 52
TBL_3 --------- IX_3 user_seeks = 296 --- User_Scans = 70329 ------ User_Lookup = 204635-- User_Updates = 13166 -- Cost = (seek/update ) = 44
TBL_4 --------- IX_4 user_seeks = 2443 --- User_Scans = 0 --- ------ User_Lookup = 0 --------- User_Updates =35000 -- Cost = (seek/update ) =14
part a : (nonclustered)
Ix_1 : Yes it should be deleted .
Ix_2 : has 2877 scan ? deleted ?
Ix_3 : huge lookup and update ? deleted?
part b : (clustered)
we have a record such as IX_3 on clustered index ? what do I do ?
and some times I have a clustered index with 0 seek and n.... update . deleted ? or change ?
January 26, 2015 at 4:56 am
No way to answer that. Maybe none of them. All of them are getting used, so unless you're very, very, very certain that there will be no performance degradation as a result, leave them all
IX_3 is a clustered index (only get lookups to a clustered index)
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
January 26, 2015 at 5:33 am
Then what is the best way to get Unused indexes?
January 26, 2015 at 5:46 am
sm_iransoftware (1/26/2015)
Then what is the best way to get Unused indexes?
A brief search suggested this[/url].
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 26, 2015 at 5:48 am
Which search? (select from dmv's or select from taht table)
January 26, 2015 at 5:58 am
sm_iransoftware (1/26/2015)
Which search? (select from dmv's or select from taht table)
I have no idea what you mean. Did you check the link?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 26, 2015 at 6:12 am
OK Thank you
I did not see link.
I use it Now.
January 26, 2015 at 6:30 am
Phil Parkin (1/26/2015)
sm_iransoftware (1/26/2015)
Then what is the best way to get Unused indexes?
And the last sentence of that article is really, really important.
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
January 26, 2015 at 7:00 am
darn i missed the link too!, here it is expanded out instead of aliased:
Phil Parkin (1/26/2015)
sm_iransoftware (1/26/2015)
Then what is the best way to get Unused indexes?A brief search suggested http://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/.
Lowell
January 28, 2015 at 11:57 pm
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
January 29, 2015 at 12:41 am
umarrizwan (1/28/2015)
DECLARE @dbid INTSELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
Some descriptive text would help.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 29, 2015 at 1:42 am
umarrizwan (1/28/2015)
DECLARE @dbid INTSELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
That will return only indexes which have never been used at all (select insert, update, delete) since the last server restart.
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
January 29, 2015 at 2:21 am
indexes which are never used by any plan and Gail is right that by last restart of server.
January 29, 2015 at 2:27 am
umarrizwan (1/29/2015)
indexes which are never used by any plan and Gail is right that by last restart of server.
By 'never used', you mean never queried and the underlying table never had a data modification.
It's not usually what people mean by 'unused indexes', they're usually looking for indexes that seldom/never get read but get updated a lot
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
January 29, 2015 at 2:49 am
if the data of underlying table changed and when we update statistics then plan should use that index.
Am I right?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply