SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Duplicate Indexing Woes

By Steve Jones,

Duplicate Indexes

Monday Morning

I was doing my daily checks, looking over backups, maintenance, logs, etc. from the weekend when I saw that familiar error. On server DENSQL01, once again, the maintenance plan had failed. I'd learned, albeit slowly), that going to the job history in Enterprise Manager wasn't all that helpful. The standard "sqlmaint.exe failed" wasn't all that helpful.

I'd only done this a couple dozen times before I learned, but I now knew immediately to browse to the S:\MSSQL\LOG folder on the server where I had configured all my maintenance plans to write their reports. I've also learned over time that having a standard location for logs on all servers speeded up troubleshooting dramatically. Looking in this folder, I found the log file for the optimizations job from the previous night and opened it in Notepad. Doing a quick search for "Err" brought me to the error. A duplicate index existed on one of the tables. Why the maintenance plan shouldn't be able to handle this I'm not sure, but it's more work for today.

I open Enterprise Manager and look for the table (MyTable). A quick right click and managing the indexes brings me...

35 indexes

After I picked my jaw up off the floor I spent a few minutes cursing the company who had sold us this tool. No, it's not one of my databases, it's from a third party application, and one that's not that well written if I do saw so myself. While I can easily find the duplicate index for this table, well not easily, more like mind numbingly boring and tediously perhaps, I have the suspicion that there may be more tables like this and since the plan for this database bombed on this table, I don't want to be doing this again next week.

I decide to get smart and handle all tables at once. First I select the database in Enterprise Manager and right click it, click on "Generate SQL Script", and select all the tables on the first tab. Here's an example using Northwind.

I deselect everything on the second tab (Formatting) and then select the following options on the third tab: Script Indexes because I want to search indexes, MS-DOS Text because, well it's a nice format in the Windows world, and one file because it's easier.

From here I then load this script file into QA. It's really not much use to me in this format, but as you'll see, I want to run some transformations. I start some search and replace options as follows:

  • Search and replace, "go" with " "
  • Search and replace, " CREATE CLUSTERED " with " "
  • Search and replace, " WITH FILLFACTOR" with " "
  • Search and replace, " CREATE " with " "
  • Search and replace, " ON [PRIMARY]" with " "

I also removed a few of the statistics creation lines. At this point I had a fairly generic script that I could use. Next I created a table in a database that I could use. Northwind on a test server worked great for this.
CREATE TABLE [iqd_dev_indexes] (
[Col001] varchar (255) NULL,
 Tbl varchar( 100), IndexCol varchar( 100) )

The three columns are basically to hold the scripted data. The first column really doesn't matter, so I didn't bother naming it. From here I took my script and with a quick DTS transform, loaded it into this table. Actually, some of you will notice that if you use DTS to create the table (as I did), it will name the columns Col001 if there isn't a name, which there isn't in a .SQL file. From here I ran the following:

delete iqd_dev_indexes where col001 is null
delete iqd_dev_indexes where col001 = ''

This gives me (for "select * from iqd_dev_indexes"):
Col001                                                          Tbl    IndexCol
--------------------------------------------------------------  -----  -----------
INDEX [AddMemberInfo_IDX] ON [dbo].[AddMemberInfo]([Modified])  NULL   NULL	 
INDEX [Addr_NDX1] ON [dbo].[Addr]([CityID], [StateID])          NULL   NULL	
Now a quick parser. I know it's not efficient, but it works. First, a little cleanup:
update iqd_dev_indexes
 set col001 = replace( col001, 'Unique', ' ')
update iqd_dev_indexes
 set col001 = replace( col001, 'Clustered', ' ')
Now we find the table name:
update iqd_dev_indexes
 set tbl = 	substring( col001, charindex( 'dbo].[', col001) + 6 
		, charindex( '])', col001) - 6 - charindex( 'dbo].[', col001))
update iqd_dev_indexes
 set tbl = substring( tbl, 1, charindex( ']', tbl)-1)

update iqd_dev_indexes
 set indexcol = substring( col001, charindex( '(', col001) + 1, charindex( ')', col001) - charindex( '(', col001)-1)

update iqd_dev_indexes
 set indexcol = replace( indexcol, '[', '')
update iqd_dev_indexes
 set indexcol = replace( indexcol, ']', '')

declare cols cursor for 
 select tbl, indexcol
 from iqd_Dev_indexes
 where charindex( ',', indexcol) > 0

declare @tbl varchar( 100)
	, @col varchar( 200)
	, @i int
open cols

fetch next from cols into @tbl, @col
while @@fetch_status = 0
	select @i = 1
	while @i = 1
		if charindex( ',', @col) = 0
			select @i = 0
			select @tbl, @col, @col
			insert iqd_dev_indexes select ' ', @tbl, @col
			select @tbl, substring( @col, 1, charindex( ',', @col) - 1), @col
			insert iqd_dev_indexes select ' ', @tbl, substring( @col, 1, charindex( ',', @col) - 1)
			select @col = ltrim( substring( @col, charindex( ',', @col) + 1, 200))
	fetch next from cols into @tbl, @col

deallocate cols

select * from iqd_dev_indexes
 delete iqd_Dev_indexes
  where charindex( ',', indexcol) > 0
At this point, I have a table I can query with the table name and columns for each index. I should have saved the index name, but this is close enough to find problems. I can query as follows:
select tbl, indexcol, count(*)
 from iqd_dev_indexes
 group by tbl, indexcol
 having count(*) > 1
which will give me a list of those indexes that use the column more than once. Using this script, I quickly determined there were in fact two tables that had duplicate indexes. Since they were duplicate and I didn't have a high opinion of the vendor, I deleted the duplicates and went along my merry way.

A week later my job completed without any errors and I never even noticed. It was weeks later when I was reviewing these notes that I realized that I'd solved a problem. I'm not a huge fan of making changes and not testing them, but in this case, removing duplicate values didn't really seem to qualify. Since they were duplicates, there wasn't even a production impact.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also rate this article.

Steve Jones
┬ędkRanch.net November 2003
Return to Steve Jones Home


Total article views: 5100 | Views in the last 30 days: 2
Related Articles


What is the use of WITH indexcols? Is there anyone can explain with the example? Thanks a lot ...


Searching for URL contents using CHARINDEX

Providing CHARINDEX parameter from table


Index Vs Select criteria sequence

Index Vs Select criteria sequence


Col001 + Col002

When i do a select Col001 + Col002 as combine from table if Col002 is null it returns a null valu...


Disabling index by using select query

Disabling index by using select query