jump to navigation

Performance problems issued by duplicate indexes April 10, 2012

Posted by fofo in general .net, Sql Server, Sql Server 2005, SQL Server 2008.
Tags:
trackback

In this post I would like to show you with a hands-on example why it is a bad idea to have duplicate indexes on SQL Server tables.

Some people believe that having duplicate indexes will improve the performance. There will be no performance gain for SELECT statements and there will be a performance degradation for INSERT,UPDATE,DELETE statements.

Have a look in this post if you want to learn about the possible dangers of over indexing your tables.

We will need a database to work with. I will use AdventureWorks2008R2. You can download it here .

We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.

I will create a new empty table in the AdventureWorks2008R2 .I will call it NewPerson and it will be based on the Person table and I will insert the same data that resides in the Person table to the NewPerson tables.There will be no indexes on the NewPerson table.I will use STATISTICS TIME ON,STATISTICS IO ON to measure the time needed to insert the rows to the table together with the IO operations.Then I will truncate the table. I will create multiple duplicate indexes on the FirstName column and I will insert the same data again in the table. I will then compare the time needed to insert rows in the table without any indexes and a table with multiple duplicate indexes.

Launch SQL Server Management Studio and log in the instance of SQL Server that AdventureWorks2008R2 is attached. In an new query window type


USE [AdventureWorks2008R2]
GO

SET NOCOUNT ON

SELECT *
INTO [Person].[NewPerson]
FROM [Person].[Person]
WHERE 3 = 4
GO

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO

INSERT INTO [AdventureWorks2008R2].[Person].[NewPerson]
([BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,[AdditionalContactInfo]
,[Demographics]
,[rowguid]
,[ModifiedDate])
SELECT    [BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,[AdditionalContactInfo]
,[Demographics]
,[rowguid]
,[ModifiedDate]
FROM [Person].[Person]
GO

SELECT * FROM Person.NewPerson

Have a look at the statistics output. In my case I got the results shown in the picture below

Now I will truncate the table and I will create non-clustered duplicate indexes on the NewPerson table for the FirstName column.Then I will insert the same data again and make a note of the statistics again.Type the following in the same query window.


TRUNCATE TABLE [Person].[NewPerson]
GO

CREATE NONCLUSTERED INDEX [IX_FirstName]
ON [Person].[NewPerson]
([FirstName] ASC) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_FirstName1]
ON [Person].[NewPerson]
([FirstName] ASC) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_FirstName2]
ON [Person].[NewPerson]
([FirstName] ASC) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_FirstName3]
ON [Person].[NewPerson]
([FirstName] ASC) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_FirstName4]
ON [Person].[NewPerson]
([FirstName] ASC) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_FirstName5]
ON [Person].[NewPerson]
([FirstName] ASC) ON [PRIMARY]
GO

INSERT INTO [AdventureWorks2008R2].[Person].[NewPerson]
([BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,[AdditionalContactInfo]
,[Demographics]
,[rowguid]
,[ModifiedDate])
SELECT    [BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,[AdditionalContactInfo]
,[Demographics]
,[rowguid]
,[ModifiedDate]
FROM [Person].[Person]
GO

Have a look again at the statistics output. In my case I got the results shown in the picture below.
They are significantly greater (140% or higher).

The NewPerson table has 19972 rows only. If it had 2-3 million rows the elapsed time in the second case it would be 20(2000%) times greater.In any case drop duplicate indexes.

Hope it helps!!!!

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: