January 30, 2021 at 5:40 am
I am trying to understand how to pin plan , I see a single query hash value however I see multiple query_id for the same query hash value. Below is the sample SQL. It looks like sometimes it is able to use the plan and sometimes it is creating a new plan leading to possible dead locks. Is it possible to get consistent query_id for a given query hash value? Instead of executing it through sp_executesql, would it better if i just do exec (@sql)? TIA
declare @s datetime
,@full datetime
,@did char(42)
,@sql nvarchar(2000)
declare @f table (ftnoteid binary(20), dataid binary(20), primary key (ftnoteid, dataid))
select @did =0x68900FE359A28495E3E
select @s = getdate()
,@sql =
N'
select fn.fo, fn.dat
from dbo.conc
join dbo.dadt on dt.coxtid = c.coid
join dbo.fo_t fnd on fd.daid = dt.daid
where c.did = ' + @did + '
group by fd.foid, fd.dataid
order by fd.foid, fd.dataid'
insert @f (foei, did)
exec sp_executesql @sql
January 31, 2021 at 6:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 31, 2021 at 4:26 pm
I see nothing in that query that requires dynamic SQL - nor is there anything done with that table variable. Assuming there is much more to the code than shown here - which would be much more important to your question than just this snippet.
But for this - don't need dynamic SQL so don't use it. Just makes it more difficult to manage and maintain and doesn't help with performance. Instead, declare @did to the same data type as c.did and just use the variable in the query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply