February 15, 2011 at 12:03 pm
Hi ALL,
I have a table with say columns A,B,C,D,E,F,G,H,I.
My Problem is that I have many indexes on the table which I think has redundant indexes.
Example: index 1 on columns A,B,C
INDEX2 on columns A, B, D
Index 3 on columns A, B, C, E, F and so no…..
How can I re master these indexes ? can anybody suggest which is the best way to start doing this.
February 15, 2011 at 2:08 pm
The best I can advise is to begin reading Gail Shaw's series of articles on Indexing the first one of which is at:
http://www.sqlservercentral.com/articles/Indexing/68439/
Then read
http://www.sqlservercentral.com/articles/Indexing/68563/
Go on to part 3 at:
February 15, 2011 at 2:32 pm
One risk to removing redundant indexes is they could be referenced as a hint in a stored procedure. You could use something like this to scan your SPs for the character string:
select name, crdate, CHARINDEX('IndexName', definition),substring(definition,CHARINDEX('IndexName', definition)-25,150) as 'Code Snippet', definition as 'Entire Code'
from sys.sql_modules com
join sysobjects obj on com.object_id = obj.id
where definition like '%IndexName%'
order by name
February 15, 2011 at 2:45 pm
Hi Thanks for your reply.
The problem is because of many indexes on the table i feel (they are )the insert/update/delete(there's a lot of insert and update on this table daily ) queries are running slow.
do i have to pull out each and every T-sql/stored procedure which uses the table and analyse the where clause and join to see if number of indexes on the table be reduced without out affecting the performance.
February 15, 2011 at 3:37 pm
In your example index 1 is redundant (the columns match exactly a left-based subset of another index), index 2 is not.
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
February 15, 2011 at 3:43 pm
GilaMonster (2/15/2011)
In your example index 1 is redundant (the columns match exactly a left-based subset of another index), index 2 is not.
Wouldn't index 1 be used if a query would only need columns A,B,C since this index would be more narrow? (not questioning, just wondering...)
February 15, 2011 at 3:46 pm
snoop123
Did you read the articles by Gale Shaw other wise known as GilaMonster?
If you have, try using this snippet of code to get a better idea of what indexes are being used for what purpose
CREATE PROCEDURE [dbo].[IndexUsage]
AS
SELECT o.name Object_Name,
SCHEMA_NAME(o.schema_id) Schema_name,
i.name Index_name,
i.Type_Desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
JOIN
sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE o.type = 'u'
-- Clustered and Non-Clustered indexes
AND i.type IN (1, 2)
-- Indexes that have been updated by not used
AND(s.user_seeks > 0 or s.user_scans > 0 or s.user_lookups > 0 );
February 15, 2011 at 3:58 pm
LutzM (2/15/2011)
GilaMonster (2/15/2011)
In your example index 1 is redundant (the columns match exactly a left-based subset of another index), index 2 is not.Wouldn't index 1 be used if a query would only need columns A,B,C since this index would be more narrow? (not questioning, just wondering...)
Yes, and I've used that technique before, but very, very sparingly. I personally would only recommend it when you're dealing with an ultra-large table but trying to pull out very thin columns (IE: VARCHAR(3000) or equivalent on the row and you only need the Surrogate PK ID and two FK_IDs).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2011 at 4:10 pm
Craig Farrell (2/15/2011)
LutzM (2/15/2011)
GilaMonster (2/15/2011)
In your example index 1 is redundant (the columns match exactly a left-based subset of another index), index 2 is not.Wouldn't index 1 be used if a query would only need columns A,B,C since this index would be more narrow? (not questioning, just wondering...)
Yes, and I've used that technique before, but very, very sparingly. I personally would only recommend it when you're dealing with an ultra-large table but trying to pull out very thin columns (IE: VARCHAR(3000) or equivalent on the row and you only need the Surrogate PK ID and two FK_IDs).
I use it if both indexes will be used frequently, disabling the smaller one would have a significant performance impact and I can live with the overhead introduced in insert/update/delete.
February 15, 2011 at 10:30 pm
LutzM (2/15/2011)
GilaMonster (2/15/2011)
In your example index 1 is redundant (the columns match exactly a left-based subset of another index), index 2 is not.Wouldn't index 1 be used if a query would only need columns A,B,C since this index would be more narrow? (not questioning, just wondering...)
Yes. Question is whether that makes a big performance difference and whether the extra space, reindex time, etc is worth it. Trade offs. Always.
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
February 16, 2011 at 11:44 am
GilaMonster (2/15/2011)
LutzM (2/15/2011)
GilaMonster (2/15/2011)
In your example index 1 is redundant (the columns match exactly a left-based subset of another index), index 2 is not.Wouldn't index 1 be used if a query would only need columns A,B,C since this index would be more narrow? (not questioning, just wondering...)
Yes. Question is whether that makes a big performance difference and whether the extra space, reindex time, etc is worth it. Trade offs. Always.
That's what I thought. Thanx for clarification.
As a side note: you decided to use the "real Gila Monster" as your avatar?
February 16, 2011 at 1:45 pm
LutzM (2/16/2011)
As a side note: you decided to use the "real Gila Monster" as your avatar?
Temporarily. Qui-gon will be back.
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
February 16, 2011 at 1:53 pm
Try this to help quickly find where the exact and partial duplicate indexes are.
-- Overlapping indxes
with indexpartdups as (
Select object_id As id, index_id As indid, name,
(Select case keyno when 0 then NULL else colid end As [data()]
From sys.sysindexkeys As k
Where k.id = i.object_id
And k.indid = i.index_id
Order By keyno, colid
For XML Path('')) As cols
From sys.indexes As i
), indexdups as (
Select object_id As id, index_id As indid, name,
(Select case keyno when 0 then NULL else colid end As [data()]
From sys.sysindexkeys As k
Where k.id = i.object_id
And k.indid = i.index_id
Order By keyno, colid
For XML Path('')) As cols,
(Select case keyno when 0 then colid else NULL end As [data()]
From sys.sysindexkeys As k
Where k.id = i.object_id
And k.indid = i.index_id
Order By colid
For XML Path('')) As inc
From sys.indexes As i
)
Select object_schema_name(c1.id) + '.' + object_name(c1.id) As IndexTable,
c1.name As FirstIndex,
c2.name As MatchedIndex,
'PartialDuplicate' as MatchType
From indexpartdups As c1
Inner Join indexpartdups As c2
on c1.id = c2.id
And c1.indid < c2.indid
And (c1.cols like c2.cols + '%' or c2.cols like c1.cols + '%')
Union All
Select object_schema_name(c1.id) + '.' + object_name(c1.id) As IndexTable,
c1.name As FirstIndex,
c2.name As MatchedIndex,
'ExactDuplicate' as MatchType
From indexdups As c1
Inner Join indexdups As c2
on c1.id = c2.id
And c1.indid < c2.indid
And c1.cols = c2.cols
And c1.inc = c2.inc;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 17, 2011 at 5:40 am
1) That's an interesting script for dupe index checking Jason. Haven't seen one like that before. I need to give it a try.
2) Duplicate indexes can indeed cause SIGNIFICANT issues with concurrency and DML response time. I had one client contact me after their system became almost unresponsive due to someone going hog-wild with DTA combined with a major data addition to the database. It took me a LONG to completely clean up that mess, but we eventually got to a great balance of fast SELECT performance AND fast/concurrent DML activity. Come to think of it, I have had to clean up quite a few DTA SNAFUs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 17, 2011 at 8:40 am
TheSQLGuru (2/17/2011) Come to think of it, I have had to clean up quite a few DTA SNAFUs.
Let's not forget theoretical indexes that DTA creates during testing that it fails to remove too.
They really need to rethink the whole DTA process...I'm sure everyone here has seen the havoc it brings (and probably have very, very few success stories).
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply