February 20, 2014 at 4:36 am
Hi
I haven't quite figured out how to prove or disprove this reliably so I'm hoping for a tip.
What I need to know is, if I drop and recreate a (NonClustered, Non-PK) index on a table, using the same name, will queries/procs that refer to that table recompile when executed? Ie will they "know" that the index is not the same one even if it's named identically (and structured identically)?
I'm assuming that they will recompile (because potentially the parameters of the index might have changed even if the name is the same, which could be a performance disaster), but I'm not proficient enough with Query Plans Caches, DMVs etc. to quite figure it out for myself 🙁
Thanks in advance.
February 20, 2014 at 6:03 am
Test it:
CREATE TABLE dbo.LetsRecompile
(
ID INT IDENTITY(1, 1),
MyVal VARCHAR(50)
);
GO
CREATE INDEX WhatHappens ON dbo.LetsRecompile (MyVal);
GO
INSERT INTO LetsRecompile
(MyVal)
VALUES ('Some'),
('Thing'),
('Has'),
('To'),
('Be'),
('Here'),
('For'),
('Statistics');
GO
SELECT MyVal
FROM dbo.LetsRecompile
WHERE MyVal = 'Thing';
DROP INDEX dbo.LetsRecompile.WhatHappens;
GO
CREATE INDEX WhatHappens ON dbo.LetsRecompile (MyVal);
GO
SELECT MyVal
FROM dbo.LetsRecompile
WHERE MyVal = 'Thing';
SELECT deqs.execution_count,
dest.text
FROM sys.dm_exec_query_stats AS deqs
OUTER APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.text LIKE '%MyVal%';
Now, this test is a little overly simplistic, and we're getting a parameterized query which could skew the results, but, you'll note that the execution count for the query stays at one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2014 at 7:18 am
SELECT deqs.execution_count,dest.text
FROM sys.dm_exec_query_stats AS deqs
OUTER APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.text LIKE '%MyVal%';
Now, this test is a little overly simplistic, and we're getting a parameterized query which could skew the results, but, you'll note that the execution count for the query stays at one.
No, this was perfect, just couldn't ID dm_exec_query_stats as my root (long time, no DBAing!). By further hooking to dm_exec_query_plan I was able to answer my questions and more besides.
Thank you very much 🙂
February 20, 2014 at 7:26 am
Happy to help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply