Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
This question already has an answer here:
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
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
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).
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.
