I have a stored procedure that takes around 2 minutes to execute. The execution plan suggested me to create a non clustered index on a table (which is a high traffic table with millions of records in it, plus it gets a constant stream of data every single second).

Please help me decide on whether I should create an index on that table or not.

Note: I tried creating a non clustered index on that table (on development server) and the time was reduced to 40 seconds from 2 minutes.

  • 5
    which RDBMS and version? – A-K Jan 23 '13 at 1:43
  • MS-SQL Server 2008 – Learner Jan 23 '13 at 15:20

You haven't really given enough information about the original execution plan, what it changed to or what columns you put in the new NCI. It is possible that the query performance can be improved even further. The cost of the new index is the initial build, the additional processing of entries on INSERT and UPDATE and potentially the fragmentation maintenance. Also, it's possible it may require some statistics maintenance if the data profile is likely to change.

It is possible that the index could improve overall system performance by reducing read access to the clustered index, but with a table changing so rapidly, the cost of maintaining the index might be very high. On the other hand, you have to look at the overall requirements. If this query only runs once a month, perhaps it is too high to bear. If it runs 10 times a hours, then running it in a few seconds, with inserts slightly slower might be a small price to pay.

A thing to look out for would be index fragmentation if there are a lot of updates because the NCI key is not static.

| improve this answer | |
  • The query is an on demand reporting query and the client is going to use it 4-5 times a day. – Learner Jan 22 '13 at 23:29
  • @Mihir Kind of assuming this is SQL Server, but you can actually capture a typical workload using the profiler and then feed it to the Database Engine Tuning adviser and see what kinds of things it sees. You may be able to get rid of some indexes. Generally indexes are a space overhead and processing/IO overhead on INSERT and UPDATE with a performance payoff on reads. – Cade Roux Jan 23 '13 at 3:47

Based on your note it sounds like adding a non-clustered index to that table is the right way to go. Play with the order of the columns in the development system, and add any needed columns as included columns and see if the query gets any faster.

The general rule of thumb is that is a query is a part of the application and the query is being used regularly then having the indexes for the query will be worth the cost.

| improve this answer | |
  • @medenny: Can you please shed some light on the cost I will have to pay for it? – Learner Jan 22 '13 at 23:09
  • 1
    The cost that I'm talking about here is the IO workload to maintain the data in the index. – mrdenny Jan 23 '13 at 0:00

As mentioned: Yes: Add the nonclustered index.

How many bytes wide will it be?

You do want to evaluate if it will negatively affect write operations. This is not an easy thing to assess from a distance, as there are many factors. The most grunt way is if you have a sense of your current write performance, add the new index and see if write performance, or overall db perf, is adversely affected.

This kind of db tuning is not easy to do here on stack exchange. It can be involved, and it is often a tradeoff of costs and benefits.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.