Too many indexes!

Indexes are great. They speed up our queries. In fact, without them relational database systems wouldn’t work.

Different indexes work best for different queries. In a system with a lot of queries that means we could need a lot of indexes. Fortunately we can have up to 999 non-clustered indexes per table and one clustered for a total of 1000 indexes. That’s a huge amount. So should we have as many indexes as possible, just in case right?

Let’s just go with no. Indexes are awesome and make queries much faster. Read queries. Not write queries. In fact they slow down write queries. Not significantly, but it is enough to make a difference. This means that OLAP (online analytical processing, data warehouses for example) databases can have a lot more indexes than OLTP (online transaction processing) database. They do significantly less writing than reading. But what kind of impact are we talking about?

Discussion here, code below

First we are going to create a nice wide table. Say 100 columns wide. Then we are going to run three tests. The first with only a clustered index (the primary key in this case). The second with a single additional index and the third with 100 total indexes. Each time I’m going to insert 100,000 rows.

Times:

CI Only – 12130 ms.
CI + 1NCI – 14057 ms.
CI + 99NCI – 126876 ms.

So about 2 extra seconds for the first non-clustered index. Once we got up to 99 non-clustered it only averaged about 1.1 seconds per index. Still, in the world of databases that’s a lot of time.

Now I’m guessing at least a few of you are thinking “Well, that’s a lot of rows. If I’m only inserting one or two at a time it won’t make that big a difference.” Well, maybe true, maybe not. One insert at a time a hundred times a minute is going to really add up. One insert a day? Probably not a problem. Of course there are a couple of other considerations. First of all there is maintenance. The more indexes the longer your index maintenance will take. Then there is space. Indexes take up space (obviously). A lot of indexes will take up a lot of space and could really bloat your database.

Size

CI Only –
CI + 1NCI –
CI + 99NCI –

The single NCI added ~4MB while the 99 indexes added ~600MB and more than doubled the size of the table.

I want to make a couple of final points. I realize 99 indexes is a lot. It’s to emphasize the differences. However they were also fairly small indexes and this is a single table where a normal database might easily have hundreds. So take these results as an example. They aren’t going to match real life but will hopefully show you how all of this can play out.

Indexes are awesome but you want to be smart about adding them. My personal rule of thumb, with no scientific evidence behind it, is 5 indexes or less and I’m pretty easy. 5-10 indexes and you’ll have to convince me. I’m going to be reviewing the existing indexes and see what I can get rid of, or maybe I can combine something. Past 10 indexes and it had best be for a query that’s running a 100+ times a minute or something for the CEO.

Demo code

-- Create a table 100 columns wide with only a clustered primary key
CREATE TABLE TooManyIndexes (Id INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_TooManyIndexes PRIMARY KEY,
Col00 varchar(100), Col01 varchar(100), Col02 varchar(100), Col03 varchar(100), Col04 varchar(100), Col05 varchar(100), Col06 varchar(100), Col07 varchar(100), Col08 varchar(100), Col09 varchar(100),
Col10 varchar(100), Col11 varchar(100), Col12 varchar(100), Col13 varchar(100), Col14 varchar(100), Col15 varchar(100), Col16 varchar(100), Col17 varchar(100), Col18 varchar(100), Col19 varchar(100), 
Col20 varchar(100), Col21 varchar(100), Col22 varchar(100), Col23 varchar(100), Col24 varchar(100), Col25 varchar(100), Col26 varchar(100), Col27 varchar(100), Col28 varchar(100), Col29 varchar(100), 
Col30 varchar(100), Col31 varchar(100), Col32 varchar(100), Col33 varchar(100), Col34 varchar(100), Col35 varchar(100), Col36 varchar(100), Col37 varchar(100), Col38 varchar(100), Col39 varchar(100), 
Col40 varchar(100), Col41 varchar(100), Col42 varchar(100), Col43 varchar(100), Col44 varchar(100), Col45 varchar(100), Col46 varchar(100), Col47 varchar(100), Col48 varchar(100), Col49 varchar(100), 
Col50 varchar(100), Col51 varchar(100), Col52 varchar(100), Col53 varchar(100), Col54 varchar(100), Col55 varchar(100), Col56 varchar(100), Col57 varchar(100), Col58 varchar(100), Col59 varchar(100), 
Col60 varchar(100), Col61 varchar(100), Col62 varchar(100), Col63 varchar(100), Col64 varchar(100), Col65 varchar(100), Col66 varchar(100), Col67 varchar(100), Col68 varchar(100), Col69 varchar(100), 
Col70 varchar(100), Col71 varchar(100), Col72 varchar(100), Col73 varchar(100), Col74 varchar(100), Col75 varchar(100), Col76 varchar(100), Col77 varchar(100), Col78 varchar(100), Col79 varchar(100), 
Col80 varchar(100), Col81 varchar(100), Col82 varchar(100), Col83 varchar(100), Col84 varchar(100), Col85 varchar(100), Col86 varchar(100), Col87 varchar(100), Col88 varchar(100), Col89 varchar(100), 
Col90 varchar(100), Col91 varchar(100), Col92 varchar(100), Col93 varchar(100), Col94 varchar(100), Col95 varchar(100), Col96 varchar(100), Col97 varchar(100), Col98 varchar(100), Col99 varchar(100));
-- Turn on time statistics so we can see how long it takes.
SET STATISTICS TIME ON;
-- Insert 100,000 rows of semi random data into the table
INSERT INTO TooManyIndexes
SELECT TOP 100000
obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name,
obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, 
obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, 
obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, 
obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, 
obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, 
obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, 
obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, 
obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, 
obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name
FROM sys.all_objects obj1
CROSS JOIN sys.all_objects obj2
CROSS JOIN sys.all_objects obj3
CROSS JOIN sys.all_objects obj4
CROSS JOIN sys.all_objects obj5
CROSS JOIN sys.all_objects obj6
CROSS JOIN sys.all_objects obj7
CROSS JOIN sys.all_objects obj8
CROSS JOIN sys.all_objects obj9
CROSS JOIN sys.all_objects obj10;
/*
SQL Server parse and compile time: 
   CPU time = 47 ms, elapsed time = 97 ms.
 SQL Server Execution Times:
   CPU time = 4219 ms,  elapsed time = 12130 ms.
(100000 row(s) affected)
*/-- Turn off time statistics because I don't care how long the truncate & indexes take.
SET STATISTICS TIME OFF;
-- Clear out the table for the next test.
TRUNCATE TABLE TooManyIndexes;
-- Create a single index
CREATE INDEX ix00 ON TooManyIndexes (Col00)
-- Turn on time statistics so we can see how long it takes.
SET STATISTICS TIME ON;
-- Insert 100,000 rows of semi random data into the table
INSERT INTO TooManyIndexes
SELECT TOP 100000
obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name,
obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, 
obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, 
obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, 
obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, 
obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, 
obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, 
obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, 
obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, 
obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name
FROM sys.all_objects obj1
CROSS JOIN sys.all_objects obj2
CROSS JOIN sys.all_objects obj3
CROSS JOIN sys.all_objects obj4
CROSS JOIN sys.all_objects obj5
CROSS JOIN sys.all_objects obj6
CROSS JOIN sys.all_objects obj7
CROSS JOIN sys.all_objects obj8
CROSS JOIN sys.all_objects obj9
CROSS JOIN sys.all_objects obj10;
/*
SQL Server parse and compile time: 
   CPU time = 31 ms, elapsed time = 43 ms.
 SQL Server Execution Times:
   CPU time = 4547 ms,  elapsed time = 14057 ms.
(100000 row(s) affected)
*/-- Turn off time statistics because I don't care how long the truncate & indexes take.
SET STATISTICS TIME OFF;
-- Clear out the table for the next test.
TRUNCATE TABLE TooManyIndexes;
-- Drop the extra index (it makes my script below easier)
DROP INDEX TooManyIndexes.ix00;
-- Add 99 indexes.  
-- 1/3 with 1 column no include
-- 1/3 with 1 column and 1 include 
-- 1/3 with 2 columns
DECLARE @sql nvarchar(1000);
DECLARE @counter int = 0;
WHILE @counter < 99
BEGIN
SET @sql = 'CREATE INDEX ix_'+cast(@counter as varchar(3))+' ON TooManyIndexes ' + 
'(Col'+RIGHT('00'+cast(@counter as varchar(3)),2)+')';
EXEC sp_executesql @sql;
SET @sql = 'CREATE INDEX ix_'+cast(@counter+1 as varchar(3))+' ON TooManyIndexes ' + 
'(Col'+RIGHT('00'+cast(@counter as varchar(3)),2)+') INCLUDE (Col'+RIGHT('00'+cast(@counter+1 as varchar(3)),2)+')';
EXEC sp_executesql @sql;
SET @sql = 'CREATE INDEX ix_'+cast(@counter+2 as varchar(3))+' ON TooManyIndexes ' + 
'(Col'+RIGHT('00'+cast(@counter as varchar(3)),2)+', Col'+RIGHT('00'+cast(@counter+1 as varchar(3)),2)+')';
EXEC sp_executesql @sql;
SET @counter = @counter + 3;
END
-- Turn on time statistics so we can see how long it takes.
SET STATISTICS TIME ON;
-- Insert 100,000 rows of semi random data into the table
INSERT INTO TooManyIndexes
SELECT TOP 100000
obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name,
obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, 
obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, 
obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, 
obj5.name, obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, 
obj6.name, obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, 
obj7.name, obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, 
obj8.name, obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, 
obj9.name, obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, 
obj10.name, obj1.name, obj2.name, obj3.name, obj4.name, obj5.name, obj6.name, obj7.name, obj8.name, obj9.name
FROM sys.all_objects obj1
CROSS JOIN sys.all_objects obj2
CROSS JOIN sys.all_objects obj3
CROSS JOIN sys.all_objects obj4
CROSS JOIN sys.all_objects obj5
CROSS JOIN sys.all_objects obj6
CROSS JOIN sys.all_objects obj7
CROSS JOIN sys.all_objects obj8
CROSS JOIN sys.all_objects obj9
CROSS JOIN sys.all_objects obj10;
/*
SQL Server parse and compile time: 
   CPU time = 109 ms, elapsed time = 157 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 61984 ms,  elapsed time = 126876 ms.
(100000 row(s) affected)
*/

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: indexes, microsoft sql server, T-SQL

Discussion here, code below

Demo code

Rate

Share

Share

Rate

Too many indexes!

Discussion here, code below

Demo code

Rate

Share

Share

Rate

Related content

Book Review: Big Red - Voyage of a Trident Submarine

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Inserting Markup into a String with SQL

Networking - Part 4

Speaking at Community Events - More Thoughts