November 28, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/duplicateindexingwoes.asp
December 14, 2003 at 11:23 pm
I admire your tenacity and ingenuity Steve. 😉 I just took the lazy man's way out and did a search for "duplicate index" on the SQL Server Central search. It turns out there's a script from Clinton Herring that appears to do the same thing. http://www.sqlservercentral.com/scripts/contributions/595.asp
Turns out that I have a couple of tables with duplicate indexes myself. (In my earlier days I think I didn't realize that unique constraints also created indexes)
David
December 15, 2003 at 4:22 am
How about this script:
declare @indexes table (
id int,
TableName sysname,
indid smallint,
IndexName sysname,
colid smallint null,
ColumnName sysname null,
keyno smallint null,
AutoStats bit null
)
declare @sysindexkeys table (
id int,
indid smallint,
colid smallint,
keyno smallint,
PRIMARY KEY (id, indid, colid),
UNIQUE (id, indid, keyno)
)
set nocount on
insert into @sysindexkeys select * from sysindexkeys
insert into @indexes
select si.id, so.name as TableName, si.indid, si.name as IndexName,
sik.colid, sc.name as ColumnName, sik.keyno
, INDEXPROPERTY(si.id, si.name, 'IsAutoStatistics') as AutoStats
from sysobjects so inner join sysindexes si on so.id=si.id
left join sysindexkeys sik on si.id=sik.id
and si.indid=sik.indid
left join syscolumns sc on sik.id=sc.id
and sik.colid=sc.colid
where objectproperty(si.id,'IsMSShipped')=0 and so.xtype='U' and si.indid<>255
SELECT DISTINCT 'DROP INDEX ['+A.TableName+'].'+A.IndexName /*, B.IndexName*/
FROM @indexes A
INNER JOIN @indexes B ON A.id=B.id
AND A.indid<B.indid
WHERE A.AutoStats=0 and B.AutoStats=0
(SELECT COUNT(*) FROM @sysindexkeys sik
WHERE sik.id=A.id and sik.indid=A.indid)
= (SELECT COUNT(*)
FROM @sysindexkeys sik1 INNER JOIN @sysindexkeys sik2
ON sik1.id=sik2.id and sik1.colid=sik2.colid
and sik1.keyno=sik2.keyno
and (SELECT COUNT(*) FROM @sysindexkeys sik
WHERE sik.id=A.id and sik.indid=A.indid)
=(SELECT COUNT(*) FROM @sysindexkeys sik
WHERE sik.id=B.id and sik.indid=B.indid)
WHERE sik1.id=A.id and sik1.indid=A.indid
AND sik2.id=B.id and sik2.indid=B.indid)
SELECT DISTINCT 'DROP STATISTICS ['+A.TableName+'].'+A.IndexName /*, B.IndexName*/ FROM @indexes A
INNER JOIN @indexes B ON A.id=B.id
AND A.indid<>B.indid
WHERE A.AutoStats=1 and
(SELECT COUNT(*) FROM @sysindexkeys sik
WHERE sik.id=A.id and sik.indid=A.indid)
=(SELECT COUNT(*) FROM @sysindexkeys sik1 INNER JOIN @sysindexkeys sik2
ON sik1.id=sik2.id and sik1.colid=sik2.colid and
sik1.keyno=sik2.keyno
WHERE sik1.id=A.id and sik1.indid=A.indid
AND sik2.id=B.id and sik2.indid=B.indid)
It could be done without the temporary tables, but I saw that it quicker this way because sysindexkeys has no indexes.
It could also be done to erase the indexes directly, using the xp_execresultset (undocumented).
Razvan
Edited by - rsocol on 12/15/2003 04:24:39 AM
December 15, 2003 at 4:51 am
Surely the best option is not to use sysmaint <grin> ? It's an interesting point however, does it apply where there are indexes on single columns that also appear in compound/covered indexes ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 15, 2003 at 10:05 am
Perhaps the best plan is not to use Sysmaint, but it's a quick and easy and relatively foolproof way to ensure a server is setup quickly. Especially when we have multiple people setting up servers.
I don't think it applies to compound indexes since I've got some that include columns that are the basis for other indexes.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 15, 2003 at 12:49 pm
To try to help this problem, (I'm fortunate to not have 3rd party databases), I have a naming convention for the indexes. I#first_column#second_column or IC#first_column#... if clustered. It causes more work on my end instead of using SQL generated names, but it helps me spot duplicate indexes quickly before moving a database from development to production.
Thanks for the script, Steve!
Michelle
Michelle
December 15, 2003 at 7:30 pm
I wrote the script below and it works beautifully for me. This is looking for duplicates in the sysindexes.keys column, which according to BOL is the "List of the column IDs of the columns that make up the index key." So, this always finds duplicates...
select
seed.TableName
,si.[Name] as IndexName
From
(select si.[ID] as TableID, so.Name as TableName, count(*) as IndexCount
Fromsysindexes si (nolock)
JOIN sysobjects so (nolock) on si.[ID] = so.[ID]
where si.Keys is not null
Group by si.ID, so.Name, si.Keys) seed
Join sysindexes si (nolock) on seed.TableID = si.[ID]
where IndexCount > 1
Signature is NULL
December 15, 2003 at 7:42 pm
Correction to the script below:
select
seed.TableName
,si.[Name] as IndexName
,si.indid
From
(select si.[ID] as TableID, so.Name as TableName, si.Keys, count(*) as IndexCount
Fromsysindexes si (nolock)
JOIN sysobjects so (nolock) on si.[ID] = so.[ID]
where si.Keys is not null and si.Name not like '%_sys_%'
Group by si.ID, so.Name, si.Keys) seed
Join sysindexes si (nolock) on seed.TableID = si.[ID] and seed.keys = si.Keys
where IndexCount > 1
order by 1, 2
Signature is NULL
December 15, 2003 at 7:42 pm
Correction to the script above:
select
seed.TableName
,si.[Name] as IndexName
,si.indid
From
(select si.[ID] as TableID, so.Name as TableName, si.Keys, count(*) as IndexCount
Fromsysindexes si (nolock)
JOIN sysobjects so (nolock) on si.[ID] = so.[ID]
where si.Keys is not null and si.Name not like '%_sys_%'
Group by si.ID, so.Name, si.Keys) seed
Join sysindexes si (nolock) on seed.TableID = si.[ID] and seed.keys = si.Keys
where IndexCount > 1
order by 1, 2
Signature is NULL
December 16, 2003 at 12:18 am
Hi there
Calvin - interesting script.
So the script for example tells me this:
CREATE STATISTICS [hind_119723529_2A_9A] ON [dbo].[Address] ([address_addresstype_id], [address_org_id])
is a duplicate with:
CREATE INDEX [addrtype_orgid_ix] ON [dbo].[Address]([address_addresstype_id], [address_org_id]) WITH FILLFACTOR = 90, PAD_INDEX ON [CORPSYS_INDEX]
Perhaps something to factor in.. but then again, you can create multiple statistics over the same columns without error - yet another item to deal with.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 15, 2004 at 2:28 am
Sorry Steve, this time the award goes to Razvan Socol, far and away a much quicker, easier and simpler solution
December 15, 2004 at 11:29 am
This seems a little more compact too me. The only problem might be the undocumented sp_msforeachtable. If the database has a lot of tables and idexes could be a little slow.
create table iqd_dev_indexes (
index_name sysname,
index_description varchar(210),
index_keys nvarchar(2078)
)
exec sp_msforeachtable 'insert into iqd_dev_indexes exec sp_helpindex ''?'''
alter table iqd_dev_indexes add table_name sysname
go
update iqd_dev_indexes set table_name=(select distinct object_name(id) from sysindexes s where i.index_name=s.name)
from iqd_dev_indexes i
select table_name, index_keys, count(*) from iqd_dev_indexes
group by table_name, index_keys having count(*)>1
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply