jump to navigation

The dangers of over indexing your SQL Server tables April 7, 2012

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

I have been teaching SQL Server recently in my capacity as MCT and to be totally honest with you I enjoy it very much.The topic that kept popping up all the time was indexes and how to use indexes to achieve high performance in SQL Server .Indexes is a vast subject and well documented elsewhere on the web.In most cases the answer is “it depends” when someone asks me if a column in a particular table needs to be indexed. It is a very challenging and iterative process.Not only you need to create the right indexes but also you need to analyse them and maintain them.In most cases the customer “lies” about the database access patterns and the numbers of users that will be using the database .Because of that we might choose the wrong indexes because we have made the wrong assumptions about workloads.We must always bear in mind the type and frequency of the queries.Data modifications (INSERT,UPDATE,DELETE statements ) are impacted by indexes.The main points for someone to keep in mind when considering index creation is that he must know his data,know his workload and finally know how SQL Server works.

I will beginning a series of small posts looking into the various aspects of indexes hoping to shed some light on their use and misuse.A lot of times I find out that every time a DBA or developer detects a performance degradation tends to create indexes on the tables of the backend (SQL Server in this case).If there is a problem with performance it does not mean that index creation is always the answer. Many reasons could cause performance bottlenecks (e.g the hardware cannot withstand the workload,excessive blocking or little reuse of the execution plans)

In this post I will show you why it is a bad idea to over index your SQL Server tables.Over indexing consumes unnecessary disk space and the query optimiser may use the less efficient index thus the less efficient execution plan.

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 to measure the time needed to insert the rows to the

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
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 a clustered and non clustered indexes on almost every column of the NewPerson table.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

ALTER TABLE [Person].[NewPerson]
ADD CONSTRAINT [PK_BusinessEntityID]
PRIMARY KEY CLUSTERED
([BusinessEntityID] ASC,
[LastName] ASC) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_NewPersonType]
ON [Person].[NewPerson]
([PersonType] ASC) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_NameStyle]
ON [Person].[NewPerson]
([NameStyle] ASC) ON [PRIMARY]
GO

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

CREATE NONCLUSTERED INDEX [IX_MiddleName]
ON [Person].[NewPerson]
([MiddleName] ASC) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_NSuffix]
ON [Person].[NewPerson]
([Suffix] ASC) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_EmailPromotion]
ON [Person].[NewPerson]
([EmailPromotion] ASC) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_rowguid]
ON [Person].[NewPerson]
([rowguid] ASC) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ModifiedDate]
ON [Person].[NewPerson]
([ModifiedDate] 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 (40% or higher).

In this case we see that over indexing does not help the performance of the system.We should drop the ones we do not need.   The NewPerson table has 19972 rows only. If it had 2-3 million rows the elapsed time in the second case it would be 10(1000%) times greater.

Hope it helps!!!!

Comments»

1. Dot Net Rules : The dangers of over indexing your SQL Server tables - April 7, 2012

[…] a column in a particular table needs to be indexed. It is a very challenging and iterative process.(read more) Share Posted: Σάββατο, 7 Απριλίου 2012 12:53 πμ από το μέλος […]

2. Applying UNIQUE indexes on columns with Multiple NULLS « DOT NET RULES - April 7, 2012

[…] a look in this post if you want to learn about the possible dangers of over indexing your […]

3. Dot Net Rules : Performance problems issued by duplicate indexes - April 10, 2012

[…] a look in this post if you want to learn about the possible dangers of over indexing your […]


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: