Drawbacks of using too many indexes?

[Proqrammer]

Hi there

Are there any drawbacks of adding too many indexes in a table?!

How much is too much?!

 

[gmmastros]

YES!

Indexes will ONLY speed up selects. For everything is, it slows down performance. Think (Insert, update & delete).

Properly indexing a database is like a balancing act. You don't want too many indexes, or too few.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

As a General Rule I think the number is 5. What version of SQL are you using. I have a nice query for 2005 that will show you how many times an index has been used.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Proqrammer]

I'm using MS SQL 2005, is there any guide on indexing? I'm clueless about what columns to index.


Have a look at my website,

http://www.yahoo.com

 

[ptheriault]

>>I'm clueless about what columns to index.

It all depends on what columns your application is trying to access.

With these querries you can determine the cost of each index.

--- sys.dm_db_index_operational_stats
declare @dbid int
select @dbid = db_id()

select objectname=object_name(s.object_id), indexname=i.name, i.index_id
            , reads=range_scan_count + singleton_lookup_count
            , 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count
            , 'leaf_page_splits' = leaf_allocation_count
            , 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
            , 'nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) s,
                        sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by reads desc, leaf_writes, nonleaf_writes
--- sys.dm_db_index_usage_stats

select objectname=object_name(s.object_id), indexname=i.name, i.index_id
                        ,reads=user_seeks + user_scans + user_lookups
                        ,writes =  user_updates
from sys.dm_db_index_usage_stats s,
            sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and s.object_id = i.object_id
and i.index_id = s.index_id
and s.database_id = @dbid
order by reads desc

go


SELECT d.name as db_name, s.name as table_name,si.name as index_name,
       i.index_id, si.rowcnt, i.system_scans
FROM sys.dm_db_index_usage_stats i
     JOIN sys.sysobjects s
       ON i.object_id = s.id
     JOIN sys.sysdatabases d
       ON i.database_id = d.dbid
     JOIN sys.sysindexes si
       ON i.object_id = si.id
WHERE  i.index_id <> 1
  AND d.name not in ('tempdb')
ORDER BY rowcnt desc

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

HEre is one more. This one will show you all indexes that haven't been used by user querries.

SELECT d.name as db_name, s.name as table_name,si.name as index_name,
       i.index_id, si.rowcnt, i.system_scans
FROM sys.dm_db_index_usage_stats i
     JOIN sys.sysobjects s
       ON i.object_id = s.id
     JOIN sys.sysdatabases d
       ON i.database_id = d.dbid
     JOIN sys.sysindexes si
       ON i.object_id = si.id
WHERE user_seeks = 0
  AND user_scans = 0
  AND user_lookups = 0
  AND user_updates = 0
  AND i.index_id <> 1
  AND d.name not in ('tempdb')
ORDER BY rowcnt desc

- Paul

- If at first you don't succeed, find out if the loser gets anything.