1

Hi I have a SQL Stored Proc that used to take 45 seconds to execute.

Then I run the Execution Plan under SQL and saw some high cost steps:

enter image description here

Then I right click that and select:

enter image description here

And finally I create the indexes that the Execution Plan recommend me.

Now the Query takes 2 seconds that is acceptable.

Is that right what I did? Does it come with extra cost?

Any advice is well appreciated

| improve this question | |
  • 1
    Before adding any missing indexes as recommended, I recommend you to do a statistics update or index reorg/rebuild (in the test environment) to see whether the performance is improved. Too many indexes does have performance penalty on Insert/Update/Delete and index maintenance work, also without saying, more space needed for index itself. – jyao Jul 21 '16 at 17:25
2

I always take careful consideration when creating indexes on production systems, always try to create in DEV first. There are several reasons, but the top three for me are. First, there is a cost associated to indexes, essentially more overhead, so depending on your table the cost can be quite high. Second, I am never always privy to all the functionality that could be accessing that table/dataset, so I have seen negative results in other parts of an application due to adding indexes for other reasons. Third, if this is a data warehouse or a table that experiences frequent writes/loads, then you may actually negatively affect performance (unless you drop and add the indexes before and after).

| improve this answer | |
-1

AFAIK, this will (possibly) only affect INSERT/UPDATE tasks on the table, and rebuilding of indexes. I would confirm that any INSERT/UPDATE Tasks (Procedures) still run efficiently.

| improve this answer | |

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