sys.syscacheobjects question
-
Hi everybody,
Something I don't understand here:
Let's say I run these 3 queries:
select * from mytable where id = 1;
select * from mytable where id = 3;
select * from mytable where id = 3;
When I query the sys.syscacheobjects
view it shows 3 rows, i.e., one for each of the above queries.
However, I thought that the cached plan would be the same since I'm just changing a literal value...at least that's what I read in BOL...
What am I not understanding?
-
Use parameters :). In the above you have three different statements, with different execution plans.
DECLARE @param intSET @param = 1EXEC sp_executesqlN'select * from mytable where id = @p1',N'@p1 INT',@paramIn the above code the select statement will result in a single cached plan, and you can reuse it by calling it with different parameters.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
You can read more on this on http://msdn.microsoft.com/en-us/library/ms175580.aspx
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Thanks for the link
OK, still a bit confused:
From this link : http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#E5D
It says "For example, the query can be submitted under "set showplan_xml on" mode. If the resulting showplan contains such placeholders as @p1 and @p2, then the query has been auto-parameterized;"
When I run showplan_xml on the query I get something like this:
...
....
Doesn't this mean the query was parameterized? I thought so, but then when I looked in the cachesql, the literal constant was still there.
I thought then the sql in the cache would agree with the showplan_xml and be like "select * from mytable where id = @0"
Maybe I'm misunderstanding the syscacheobjects view...the sql column contains the adhoc text sent down...maybe that's not the actual query plan...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply