Is it good to create new indexes for better performance - SQL Server [duplicate]

Question

This question already has an answer here:

• How to determine if an Index is required or necessary 6 answers

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:

Then I right click that and select:

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

Answer 1

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).

Answer 2

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.