Indexes: how many can you have before you see problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gene. - 09 Jul 2008 19:02 GMT

Hi all
I know that this question if quite silly. However, here is what happens: i
run dta from time to time and get recommendations to create indexes on table.
This table has already 6 indexes and all of them are used today(user_seeks
and last_user_seek columns from dmw show that). So no chance I have unused
indexes.

And i know that if i create another 2 indexes, they will be used
intensively. Do you think how many indexes i have have before overhead of
their online maintenance will start showing up?

Reply to this Message

Roy Harvey (SQL Server MVP) - 09 Jul 2008 19:16 GMT

This is a classic case of It Depends. But I think you already knew
that! 8-)

A lot of what it depends on is that nature of the "online maintenance"
in relation to how heavily taxed the server is. If the table is
subject to heavy insert / update / delete activity - say a table of
every item rung up at a Walmart - then the price is high. If it is a
table of employees at a company where the average years of service is
in two digits, it is probably light. If the current transactions drag
the server to its knees the additional indexes could be an issue, but
if they come back instantaneously without any visible strain on the
server it probably won't matter at all.

Your best bet is to know your server load, and your application, so
you can make an educated estimate.

Roy Harvey
Beacon Falls, CT

>Hi all
>I know that this question if quite silly. However, here is what happens: i
[quoted text clipped - 6 lines]
>intensively. Do you think how many indexes i have have before overhead of
>their online maintenance will start showing up?

Reply to this Message