November 1, 2012 at 12:26 pm
Question:
If I create a new index to more effectively support a query in a stored procedure, what is the most appropriate action to take (if any action is required) to make sure that index is considered in query plan creation the next time the stored procedure is executed?
November 1, 2012 at 1:03 pm
SQL will use the index if it deems it helpfull. In other words, "no action required"
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 1, 2012 at 1:10 pm
The query optimizer will determine whether or not index is useful when it evaluates potential execution plans. Aside from forcing an index hint in the query (which is almost always a bad idea) to make sure that the stored procedure uses the index, there is no action that you need to take.
November 1, 2012 at 1:14 pm
So, the Optimizer will ignore all existing query plans in the cache and possibly implement a new one because it will be aware that a new index has been created?
November 1, 2012 at 1:20 pm
Lee Crain (11/1/2012)
So, the Optimizer will ignore all existing query plans in the cache and possibly implement a new one because it will be aware that a new index has been created?
In a nutshell, "Yes"
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 1, 2012 at 1:22 pm
Thanks for all of your responses.
November 1, 2012 at 1:24 pm
USE TempDB;
GO
CREATE TABLE test (
id INT,
Filler CHAR(500)
);
INSERT INTO test
SELECT object_id, '' FROM sys.objects AS o;
GO
CREATE PROCEDURE TestIndex
AS
SELECT 1 FROM test WHERE id = 0;
GO
-- turn show exec plan on
EXEC TestIndex;
GO
CREATE INDEX idx_test ON test(id);
GO
EXEC TestIndex;
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2012 at 1:45 pm
Excellent, succinct example to prove it.
I played around with your example script by adding a third column on the "test" table, an IDENTITY, and searching on that column for a single row with a value of 4, both with and without a non-clustered index on the third column, to see how the actual query plan changed. As expected, a scan resulted without the non-clustered index, a seek resulted with the addition of the non-clustered index.
Thanks, Gail.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply