Creating indexes

I need to improve the performance of a query that is scheduled to run
every 5 minutes but is now taking longer than 5 minutes to execute.
I want to add an index or 2 to the table but I'm not sure of the
impact that will have during production hours. Another question, does
an index get used based on the "select" list or the "where" condition.
Lastly I used the database index tuning wizard and would like to know
what people think of the recommedations that the wizard provide.1) Be wary of ITW.
2) Indexes can be used for both select (if they cover all columns in the
output) and where clauses.
3) index tuning is much more than just "I think i need an index or 2". It
is a very complex subject with lots of variables that come into play. Best
is to get a pro to give you a quick review to help you develop an
appropriate indexing strategy. Failing that, indexes do take up space and
have overhead associated with maintaining them for inserts, updates and
deletes. Sometimes they don't help out at all like you think they should
because the data is very unspecific.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:000e3e3a-936d-41df-95f0-5336b20289f6@.b32g2000hsa.googlegroups.com...
>I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||In addition to Kevin's reply: when you create a new index, the entire
table will be scanned, and you should expect that during this time the
table will be locked.
Obviously, it would be best to test both the index creation and the
index' effectiveness on a test machine.
And finally, to repeat the previous reply: each addition index adds to
the cost of each insert and delete, and to all relevant updates. This is
also something you might want to test before deploying.
Gert-Jan
NC3 wrote:
> I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||On Nov 20, 5:22 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> In addition to Kevin's reply: when you create a new index, the entire
> table will be scanned, and you should expect that during this time the
> table will be locked.
> Obviously, it would be best to test both the index creation and the
> index' effectiveness on a test machine.
> And finally, to repeat the previous reply: each addition index adds to
> the cost of each insert and delete, and to all relevant updates. This is
> also something you might want to test before deploying.
> --
> Gert-Jan
>
> NC3 wrote:
>
>
> - Show quoted text -
Thanks for the responses, I will look into getting the db copied to a
test server and try addding the indexes to see if they are useful at
all.