Plan cache

[simon]

If I execute ADhoc query with different values:

SELECT * FROM dbo.tbl WHERE prod_id='ABC001'
SELECT * FROM dbo.tbl WHERE prod_id='XXX333'
SELECT * FROM dbo.tbl WHERE prod_id='ZUIJKL'

I have in fact 3 different queries and I should have stored 3
different execution plans in cache.
That was what I thought.
But not. All selects use same plan from cache and only one plan is
stored in cache.
And also EXEC and sp_executesql works the same - only one plan.

Was there some change in SQL2008 R2 version?

br,
Simon

[Erland Sommarskog]

Post by simon
SELECT * FROM dbo.tbl WHERE prod_id='ABC001'
SELECT * FROM dbo.tbl WHERE prod_id='XXX333'
SELECT * FROM dbo.tbl WHERE prod_id='ZUIJKL'
I have in fact 3 different queries and I should have stored 3
different execution plans in cache.
That was what I thought.
But not. All selects use same plan from cache and only one plan is
stored in cache.
And also EXEC and sp_executesql works the same - only one plan.
Was there some change in SQL2008 R2 version?

Nope. That's simple parametrisation. That is, for very simple queries,
SQL Server will replace constants with parameters.