Question on AdHoc and Prepared Query Plans

• Rob Reid-246754

  SSCarpal Tunnel

  Points: 4183

  More actions

  June 22, 2015 at 11:08 am

  #309387

  I am using SQL 2012 and have just had to work on an ancient batch job which uses some string built, client side AdHoc queries which loop through a members table looking up individual ID's based on a supplied email address.

  I was going to do some performance tuning which included changing them to use ADO and parameters to force Query Plan re-use etc. However when I did some testing the results were not what I expected. This was after reading an article about FORCED v SIMPLE Parameterization.

  I was looking at the Query Plan and the Cached Plan DMV's to see what kind of plan re-use was going on due to the nature of the SQL being passed in e.g just a string with the values hardcoded into it which was being executed against the connection object. Instead of getting back AdHoc queries which is what I expected, I found I was getting back Prepared plans that were being re-used. Therefore I ran these tests in SQL Query Analyser.

  I am using a fully qualified name e.g database_name.dbo.my_members_table so that I can filter my results from other databases in the report query at the bottom

  this is due to the DBID column being NULL in the DMV.

  -- First clean the cache

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  -- running one query at a time AND don't include the comments as I want the query text to just show the query

  -- Query 1

  -- expecting prepared and returning prepared with a plan that shows a non-clustered index seek on my unique index for email within the table

  -- plan is >> (@email nvarchar(1000))SELECT MemberID FROM database_name.dbo.my_members_table WHERE Email = @email

  EXEC sp_executesql N'SELECT MemberID FROM database_name.dbo.my_members_table WHERE Email = @email',N'@email nvarchar(1000)',@email='test1@fakedomain.co.uk'

  -- Query 2

  -- this should (and does) use the same query plan as the code above even though the email address has changed due to us using a parameter with a datatype

  -- plan is >> (@email nvarchar(1000))SELECT MemberID FROM database_name.dbo.my_members_table WHERE Email = @emai

  EXEC sp_executesql N'SELECT MemberID FROM database_name.dbo.my_members_table WHERE Email = @email',N'@email nvarchar(1000)',@email='test2@fakedomain.co.uk'

  -- Query 3

  -- this should still be prepared but because I am returning all columns the plan is different e.g an index seek + key lookup

  -- plan is >> (@email nvarchar(1000))SELECT * FROM database_name.dbo.my_members_table WHERE Email = @email

  EXEC sp_executesql N'SELECT * FROM database_name.dbo.my_members_table WHERE Email = @email',N'@email nvarchar(1000)',@email='test1@fakedomain.co.uk'

  -- Query 4

  -- this should (and does) use the same query plan as the code above

  -- plan is >> (@email nvarchar(1000))SELECT * FROM database_name.dbo.my_members_table WHERE Email = @email

  EXEC sp_executesql N'SELECT * FROM database_name.dbo.my_members_table WHERE Email = @email',N'@email nvarchar(1000)',@email='test2@fakedomain.co.uk'

  -- Now for some AdHoc queries.....or so I thought...

  -- Query 5

  -- this returns an AdHoc query plan for an index seek + key lookup but doesn't re-use the same execution plan as query 3 and 4 which is what I expected

  -- plan is >> SELECT * FROM database_name.dbo.my_members_table WHERE email ='test1@fakedomain.co.uk'

  SELECT * FROM database_name.dbo.my_members_table WHERE email = 'test1@fakedomain.co.uk'

  -- Query 6

  -- this returns ANOTHER DIFFERENT AdHoc query plan using the same index seek + key lookup execution plan as Query 5, 3 and 4 but has a different cached plan which is what I expected

  -- plan is >> SELECT * FROM database_name.dbo.my_members_table WHERE email ='test2@fakedomain.co.uk'

  SELECT * FROM database_name.dbo.my_members_table WHERE email = 'test2@fakedomain.co.uk'

  -- Query 7

  -- this returns a PREPARED query plan using the same non-clustered index seek execution plan as Query 1 & 2 but with a different bucket ID & Query plan, this isn't what I expected, e.g

  -- plan is >> (@1 varchar(8000))SELECT [MemberID] FROM [database_name].[dbo].[my_members_table] WHERE =@1

  SELECT MemberID FROM database_name.dbo.my_members_table WHERE email ='test1@fakedomain.co.uk'

  -- Query 8

  -- this uses the same PREPARED query plan as Query 7 even though I am not using a parameter this isn't what I expected

  -- plan is >> (@1 varchar(8000))SELECT [MemberID] FROM [database_name].[dbo].[my_members_table] WHERE =@1

  SELECT MemberID FROM database_name.dbo.my_members_table WHERE email ='test2@fakedomain.co.uk'

  -- View the results

  -- As the DBID is NULL and I am on a busy server I am filtering the results to only return those for my database by looking for the database name in the query text

  -- This is not ideal but the only way to ensure I just get the results I want. Also for some reason SQL adds a newline before some queries so I cannot start without a %

  SELECTbucketid, objtype, execution_count, text

  FROMsys.dm_exec_query_stats qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

  JOINsys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle

  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

  WHEREcp.plan_handle=qs.plan_handle

  -- only return AdHoc and Prepared

  AND (ObjType = 'Adhoc' OR ObjType='Prepared')

  -- and filter by my DB

  AND Text LIKE '%database_name%'

  AND Text NOT LIKE '%SELECT creation_time, execution_count%'

  -- results

  Bucket IDTypeCountQuery Text

  39386Prepared2(@1 varchar(8000))SELECT [MemberID] FROM [database_name].[dbo].[my_members_table] WHERE =@1

  35425Adhoc1SELECT * FROM database_name.dbo.my_members_table WHERE email = 'test2@fakedomain.co.uk'

  24559Adhoc1SELECT * FROM database_name.dbo.my_members_table WHERE email = 'test1@fakedomain.co.uk'

  25580Prepared2(@email nvarchar(1000))SELECT * FROM database_name.dbo.my_members_table WHERE Email = @email

  16635Prepared2(@email nvarchar(1000))SELECT MemberID FROM database_name.dbo.my_members_table WHERE Email = @email

  As you can see the first row contain a record for Queries 7 and 8 which I was expecting from test on older machines to return AdHoc queries like (I thought) they used to. However the last time I looked into FORCED v SIMPLE Parametrization was back with SQL 2000 so maybe my memory is fading. It is just that I read an article this morning to refresh myself and from it I got the impression a query like

  SELECT MemberID FROM database_name.dbo.my_members_table WHERE email ='test1@fakedomain.co.uk'

  would return an AdHoc query with QueryText being the same as the SQL not a Prepared plan like the results I am seeing e.g

  (@1 varchar(8000))SELECT [MemberID] FROM [database_name].[dbo].[my_members_table] WHERE =@1

  Although it's not sharing the prepared plan for Queries 1 & 2 which use the exact same execution plan i.e a non-clustered index seek on my unique index for the email column it does have multiple execution counts and is being re-used.

  Although I have not tested this on SQL 2005 I did change the compatibility mode to 90 and it returned the same results.

  As I said, the last time I looked I was probably using SQL 2000 so I don't know if something has changed or not but I was expecting the bottom two queries to appear as individual AdHoc queries rather than sharing their Prepared Query Plans.

  Obviously this is a good thing however after reading the article I just wasn't expecting it.

  So the question is, even though my DB is set to have Parametrization to SIMPLE is the Query Planner good enough now to recognize queries that have parameters even if not

  explicitly set with ADO or sp_executesql e.g Name='Rob' and therefore use Prepared plans?