I'm using SQL Server and I want to benefit from reusing query plan. I found this document, but it remains unclear for me whether the plan for my query is being reused or not.
declare @su dbo.IntCollection -- TABLE (Value int not null)
insert into @su values (1),(2),(3) --... about 500 values
update mt
set mt.MyField = getutcdate()
from MyTable mt
join @su vsu on mt.Id = vsu.Value -- Clustered PK, int
Technically the text of batch differs from run to run, as different values are being inserted in @su. But the text of update query remains the same. If I were using .NET I would basically pass a table variable to SQL command, but I'm using Python and it looks like there no way to pass table parameter from my program.
Question 1: does the plan for update query get reused? Or does optimizer look that text of batch is different and does not analyze single queries in batch? In other words, is it the same as
update MyTable
set MyField = getutcdate()
where Id in (1, 2, 3 ...)
Question 2: I can force SQL to remain the same between calls by introducing a stored procedure with table parameter, but will I benefit from it?
Question 3: how to identify for a given query whether its plan was reused or computed again?
Question 4: should I worry about all above in my specific case? After all it is just an update of table on bunch of IDs...
Just answers to your questions..
Question 1: does the plan for update query get reused? Or does optimizer look that text of batch is different and does not analyze single queries in batch? In other words, is it the same as
Your both update statements are treated as new queries,since SQL tries to calculate hash of the query and any simple change will not match with old hash
Question 2: I can force SQL to remain the same between calls by introducing a stored procedure with table parameter, but will I benefit from it?
this sounds like a good approach to me..rather than a bunch of IN's
Question 3: how to identify for a given query whether its plan was reused or computed again?
select usecounts from sys.dm_exec_cached_plans ec
cross apply
sys.dm_exec_sql_text(ec.plan_handle) txt
where txt.text like '%your query text%'
Question 4: should I worry about all above in my specific case? After all it is just an update of table on bunch of IDs...
it seems to me,you are worrying much..There are many rules which enforce query plan reuse behaviour as pointed out in the white paper you referred..so most of the times,query plan will be reused..
I would start worrying about plan re usability only when i see high SQL Compilations/sec coupled with Batch Requests/sec
Taken from Answer here :https://dba.stackexchange.com/questions/19544/how-badly-do-sql-compilations-impact-the-performance-of-sql-server
SQL Compilations/sec is a good metric, but only when coupled with Batch Requests/sec. By itself, compilations per sec doesn't really tell you much.
You are seeing 170. If batch req per sec is only 200 (a little exaggerated for effect) then yes, you need to get down to the bottom of the cause (most likely an overuse of ad hoc querying and single-use plans). But if your batch req per sec is measuring about 5000 then 170 compilations per sec is not bad at all. It's a general rule of thumb that Compilations/sec should be at 10% or less than total Batch Requests/sec.