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
begin
select @i = 1
while @i = 1
begin
if charindex( ',', @col) = 0
begin
select @i = 0
select @tbl, @col, @col
insert iqd_dev_indexes select ' ', @tbl, @col
end
else
begin
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))
end
end
fetch next from cols into @tbl, @col
end
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