locked
EVENT SESSION does not pick up insert statement RRS feed

  • Question

  • Hi All,

     

    It looks like event session does not work properly. The problem is when standalone insert into table statement it is not picked up by event session. It creates event. But that event plan_handler is useless. It is not real plan handler, sys.dm_exec_query_plan can't find. Other statements work fine. Here is my test.

    Step 1.Create session

     

    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='SQLStmtEvents')

    DROP EVENT session SQLStmtEvents ON SERVER;

    GO

    CREATE EVENT SESSION SQLStmtEvents

    ON SERVER

    ADD EVENT sqlserver.sql_statement_completed

    (ACTION (sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.sql_text,sqlserver.tsql_stack,package0.callstack,sqlserver.request_id)

    WHERE sqlserver.database_id=8 --set AdventureWorks2008 DB_ID()

    )

    ADD target package0.ring_buffer

    GO

    WITH (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON)

    USE AdventureWorks2008;

    GO

     

    Step 2. Start session and insert 1 row

     

    ALTER EVENT SESSION SQLStmtEvents ON SERVER STATE = START;

    GO

    INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])

    VALUES(getdate(),SYSTEM_USER,-1,-1,-1,'ErrorProcedure, nvarchar(126)',-1,'ErrorMessage, nvarchar(4000)')

     

    Step 3. Retrieve session xml

     

    SELECT CAST(target_data AS XML) AS TargetData

    FROM sys.dm_xe_session_targets st

    JOIN sys.dm_xe_sessions s

    ON s.address = st.event_session_address

    WHERE name = 'SQLStmtEvents'

    AND target_name = 'ring_buffer'

     

    Step 4. Find insert plan handler and put it in sys.dm_exec_query_plan. It returns nothing

     

    select * from sys.dm_exec_query_plan(0x06000800DD8D6D0840015585000000000000000000000000) --replace this value with plan_handle value

     

    Even if you try  to use tsql_stack handle sys.dm_exec_query_plan returns nothing. The reason is plan is not generated for insert statement, it is generated for cluster index . And that event is not picked up by event session. We can find cluster index plan from current cache using the following statement:

     

    ;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

    select qp.query_plan as QueryPlan, st.text, cp.plan_handle

    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)

    where cp.cacheobjtype = N'Compiled Plan' and RelOp.op.value(N'@LogicalOp', N'varchar(50)') = 'Insert'

     

    But this request has no idea about my session attributes (application name, host, etc.). Which means I can't match this query

     

    When you combine insert statement with delete one

    insert

     

    into [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage]) values(getdate(),SYSTEM_USER,-1,-1,-1,'ErrorProcedure, nvarchar(126)',-1,'ErrorMessage, nvarchar(4000)')

    delete

     

    from [dbo].[ErrorLog] where [UserName] = SYSTEM_USER and [ErrorNumber] = -1

    - it will produce accurate handle (one for all statements) which is accurately picked up by sys.dm_exec_query_plan.

    Please don't mislead with parameterized handles. It is different stuff. In this issue I can't pick up even parent plan (which has reference to parameterized handles).

    I know it is not trivial task. But if somebody can reproduce it for me I will upload this thread to MS connect.

     

    Sunday, February 20, 2011 4:24 AM

All replies

  • The Event Session is working exactly how it is supposed to work. The plan_handle retrieved by the Event Session is the plan_handle for the plan used by the Engine to execute the INSERT operation. However, not all plans are cached by SQL Server in the Plan Cache, specifically zero cost plans like the simple INSERT you are using as an example. This is discussed in the following blog post:

    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx  

    What exactly are you trying to do here? I don't understand what it is that you want to do with this information exactly so I can't offer alternatives that may solve your problem.


    Jonathan Kehayias | Senior Database Administrator and Consultant
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!
    Sunday, February 20, 2011 4:53 AM
    Moderator
  • Hi Jonathan,

    Thanks for the quick responce. The link you provided is not alive.

    I know about zero cost plans. But that is not exactly what I mean. The real plan for insert exists in cache. And that plan is for cluster index, but not for insert statesment. If table has no cluster index I can see pick up plan properly.

    What I am looking for is to find out which table is affected by wich appication (there are pleanty of). I do not want to use trace analisys for some reasons. Event session should give me opportunity to pick up sql statment and link it with accurate execution plan. If execution plan not found in cache it should generate it for me on fly. BUT NOT TO EXECUTE that statement again.

    I was able to pickup select, update, delete events with accurate plan_handle, but not for insert event (handle is dummy).

    Sunday, February 20, 2011 5:29 AM
  • Andrew, if you look closely at the hyperlink that Jonathan provided you can see the terminating "x" is not underlined, a simple typo. You could just go ahead and copy and paste the full hyperlink into a browser address bar or click the corrected link below.

    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx

    Being a resident expert on XEVENTS, I will leave it with JK to follow up to your comments......


    John Sansom | SQL Server DBA Blog | Twitter
    Sunday, February 20, 2011 9:42 AM
  • Thank John, it works. I understand that cost of insert query in simple cluster based tables is pretty low. 

    But what I really do not understand is when I open event session xml

        <action name="client_app_name" package="sqlserver">

          <type name="unicode_string" package="package0" />

          <value>Microsoft SQL Server Management Studio - Query</value>

          <text />

        </action>

        <action name="plan_handle" package="sqlserver">

          <type name="unicode_string" package="package0" />

          <value>&lt;plan handle='0x06000800F9DD170540010187000000000000000000000000'/&gt;</value>

          <text />

        </action>

        <action name="sql_text" package="sqlserver">

          <type name="unicode_string" package="package0" />

          <value>INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])

         VALUES(getdate(),SYSTEM_USER,-1,-1,-1,'ErrorProcedure, nvarchar(126)',-1,'ErrorMessage, nvarchar(4000)')

    </value>

          <text />

        </action>

     

    and can see insert event with plan_handle action. When I try to use that handle it returns nothing

    select query_plan from sys.dm_exec_query_plan(0x06000800F9DD170540010187000000000000000000000000)

     

    What I want to say is SQL Server does uses query plan for insert statement. It uses cluster index plan(as I said before), indeed. But that plan handle chain (event session->query cache->index cache) is broken. And my task is to retrieve application name which insert data in tables. Without event session I don't know which application associated with cached query.

    Sunday, February 20, 2011 10:47 AM
  • This is all working exactly as designed, your expectations of what you want aren't how the system works, and Microsoft is not likely to change things in either of these areas just because you think it should work a specific way. Feel free to submit the connect feedback and a Microsoft PM will respond in kind.

    I still don't get what you are trying to accomplish here.  You keep telling us how you think SQL Server works, which is wrong, and not what you are trying to do and why.

    The only way you are going to get the exact plan generated by any specific statement without any chance of loss in SQL Server 2008 is to use SQL Trace and capture the SHOWPLAN_XML event. However, this is incredibly expensive and will most definitely impact the performance of the system. Even in SQL Denali CTP1, the Extended Event for this event is incredibly expensive and not recommended for general tracing.


    Jonathan Kehayias | Senior Database Administrator and Consultant
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by John Sansom Sunday, February 20, 2011 3:11 PM
    • Unproposed as answer by Andrew_Butenko Monday, February 28, 2011 10:37 AM
    Sunday, February 20, 2011 3:07 PM
    Moderator
  • Jonathan,

    If you do not get it, please do not blame me. I have found solution for insert statement's missed plan by matching cluster index query plan with event statement

          ;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

          ,index_plans as(

                select qp.query_plan, st.text

                FROM sys.dm_exec_cached_plans cp

                CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

                CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

                CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)

                where cp.cacheobjtype = N'Compiled Plan' and RelOp.op.value(N'@LogicalOp', N'varchar(50)') = 'Insert'

          )

          update #plans set query_plan = index_plans.query_plan

          from #plans

          inner join index_plans on index_plans.text like '%'+#plans.sql_text

          where #plans.sql_text like '%insert%into%' and #plans.query_plan is null

     

    I have got brilliant analyser now for all types of statements in event session. Let's stop this thread. Thank you all for assistance.

    Monday, February 21, 2011 11:21 AM
  • Jonathan,

    If you do not get it, please do not blame me. I have found solution for insert statement's missed plan by matching cluster index query plan with event statement

          ;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

          ,index_plans as(

                select qp.query_plan, st.text

                FROM sys.dm_exec_cached_plans cp

                CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

                CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

                CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)

                where cp.cacheobjtype = N'Compiled Plan' and RelOp.op.value(N'@LogicalOp', N'varchar(50)') = 'Insert'

          )

          update #plans set query_plan = index_plans.query_plan

          from #plans

          inner join index_plans on index_plans.text like '%'+#plans.sql_text

          where #plans.sql_text like '%insert%into%' and #plans.query_plan is null

     

    I have got brilliant analyser now for all types of statements in event session. Let's stop this thread. Thank you all for assistance.


    What exactly don't I get about this?  You are pulling back the auto-parameterized plan stub, which is a different thing from the actual plan that exists when the Extended Event Action for plan_handle fires.  I know what I am talking about, specifically for how Extended Events works and what it is pulling.  You think you have a brilliant analyzer, but you also have an incredibly load heavy scan of the plan cache to find those auto-parameterized plan stubs.  If you don't believe what I am telling you that's your choice, but it doesn't change how the Engine, plan generation, caching, and Extended Events functions, and I'm not going to allow someone from Microsoft to just mark an incorrect explaination as an Answer to this post.


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!

    Sunday, February 27, 2011 4:12 PM
    Moderator
  • Jonathan,

    If you do not get it, please do not blame me. I have found solution for insert statement's missed plan by matching cluster index query plan with event statement

          ;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

          ,index_plans as(

                select qp.query_plan, st.text

                FROM sys.dm_exec_cached_plans cp

                CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

                CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

                CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)

                where cp.cacheobjtype = N'Compiled Plan' and RelOp.op.value(N'@LogicalOp', N'varchar(50)') = 'Insert'

          )

          update #plans set query_plan = index_plans.query_plan

          from #plans

          inner join index_plans on index_plans.text like '%'+#plans.sql_text

          where #plans.sql_text like '%insert%into%' and #plans.query_plan is null

     

    I have got brilliant analyser now for all types of statements in event session. Let's stop this thread. Thank you all for assistance.

    This is not the answer to the problem and I have discussed this at depth with the Extended Events Team at Microsoft.  The simple fact is that Extended Events are doing exactly what they are designed to do here, and returning the actual execution plan_handle for the context of the execution which is very different than the parameterized plan stub that is in the plan cache after the fact.  Your response in this case is not actually the answer to this question based on how the Extended Events Engine and the specific Event you are collecting actually works, regardless of what you think.  I will have someone from the Extended Events team respond to this thread or your Connect item (http://connect.microsoft.com/SQLServer/feedback/details/648258/extended-events-does-not-track-insert-statements) to explain this to you.  The context of the parameterized plan is a level higher in the engine execution context than where the actual plan handle is being captured, and this is exactly how it should be, especially for future considerations of how additional information can be collected by Extended Events like my own Connect feedback for Denali (http://connect.microsoft.com/SQLServer/feedback/details/648351/extended-events-action-to-collect-actual-execution-plan).  The actual plan can be significantly different from a auto-parameterized or cached estimated plan for statements and the Action is correct for returning the actual plan_handle for the execution context like it does.

    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!

    Saturday, March 12, 2011 3:20 AM
    Moderator
  • Andrew, I don't know extended events as well as Jonathan does. Jonathan is known as the foremost expert for extended events in the SQL community.

    I do know enough that the problem you're stating is a well-known problem. A query plan handle is nothing more than a pointer. It's not some magical hash of the query plan that SQL Server can rebuild a query plan from. If you capture the plan handle, you generally have a limited time to get the plan from cache before it goes out of cache. Once it is out of cache, you can't get the plan. And SQL absolutely cannot reconstruct the query plan from the handle.

    Jonathan understands what you are saying. It seems like you're refusing to hear him because he's not saying what you want to hear.



    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring
    Saturday, March 12, 2011 3:49 AM
  • Andrew,

    I posted the following modification of your Connect Item repro as a comment on your connect item, but I am also going to post it here as well as in a blog post that explains what is happening with the plan_handle action in this scenario.  If you change your insert statement to be parameterized and execute it using parameters, the plan_handle returned by the Extended Events Session for the sqlserver.plan_handle Action, will return the plan from cache as you are expecting:

    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='SQLStmtEvents')
    DROP EVENT session SQLStmtEvents ON SERVER;
    GO
    CREATE EVENT SESSION SQLStmtEvents
    ON SERVER
    ADD EVENT sqlserver.sql_statement_completed
    (ACTION (sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.sql_text,sqlserver.tsql_stack,package0.callstack,sqlserver.request_id)
    WHERE sqlserver.database_id=5 --set AdventureWorks2008 DB_ID()
    )
    ADD target package0.ring_buffer
    WITH (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON)
    GO
    
    ALTER EVENT SESSION SQLStmtEvents ON SERVER STATE = START;
    GO
    
    USE AdventureWorks2008R2;
    GO
    DECLARE @ErrorTime datetime = GETDATE(),
    		@UserName sysname = SYSTEM_USER,
    		@ErrorNumber int = -1,
    		@ErrorSeverity int = -1,
    		@ErrorState int = -1,
    		@ErrorProcedure nvarchar(126) = 'ErrorProcedure',
    		@ErrorLine int = 10, 
    		@ErrorMessage nvarchar(4000) = 'An error occured'
    		
    INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])
    VALUES(@ErrorTime,@UserName,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorProcedure,@ErrorLine,@ErrorMessage)
    GO 5
    
    SELECT 
    SELECT CAST(target_data AS XML) AS TargetData 
    FROM sys.dm_xe_session_targets st 
    JOIN sys.dm_xe_sessions s 
    ON s.address = st.event_session_address 
    WHERE name = 'SQLStmtEvents' 
    AND target_name = 'ring_buffer'
    
    
    -- Shred events out of the target
    SELECT event_name,
    	sql_text,
    	query_plan
    FROM
    (
    SELECT
    	event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    	CAST(event_data.value('(event/action[@name="plan_handle"]/value)[1]', 'varchar(max)') AS XML) as plan_handle,
    	event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
    FROM(	SELECT evnt.query('.') AS event_data
    		FROM
    		(	SELECT CAST(target_data AS xml) AS TargetData
    			FROM sys.dm_xe_sessions AS s
    			JOIN sys.dm_xe_session_targets AS t
    				ON s.address = t.event_session_address
    			WHERE s.name = 'SQLStmtEvents'
    			 AND t.target_name = 'ring_buffer'
    		) AS tab
    		CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS split(evnt) 
    	 ) AS evts(event_data)
    ) AS tab
    CROSS APPLY sys.dm_exec_query_plan(plan_handle.value('xs:hexBinary(substring((plan/@handle)[1], 3))', 'varbinary(max)')) as qp
    
    

    The problem is the auto-paramterization of the adhoc insert statement is what gets cached not the actual plan that is executing in the engine when the Extended Event fires.  Hopefully this repro will show you what I was saying last month when I originally replied to this post.  The problem here is not what is being returned as much as it is what you are expecting from SQL Server isn't the way SQL Server works. 

    Consider the impact that would occur if the engine had to cache every adhoc request that was sent to it so that you could pull the actual query plan back from every adhoc insert that occured.  This is what happened in SQL Server 2005 prior to SP2 and the result was that 75% of the memory in a system with 16GB RAM was being consumed needlessly by the plan cache because of caching single use adhoc statements.  The problem was so big that Microsoft made major changes to plan caching in SQL Server 2005 SP2 that reduced the plan cache sizing algorithms as well as made special caching exceptions for statements like the original insert.  Why cache a plan that is not likely to ever be reused again by the engine?  This was further enhanced in SQL Server 2008 with the addition of Optimize for Adhoc Workloads which requires a adhoc statement to hit twice before the plan stub is cached at all, which is why I included GO 5 for the INSERT in my modified repro, just in case you or someone else looking at this post happens to have that sp_configure option enabled.

    Hopefully, this helps clarify the problem for you better.


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!

    Thursday, April 7, 2011 5:54 AM
    Moderator
  • Jonathan,
    Your explanation is 100% misleading. I do not use ad hoc optimisation ('optimize for ad hoc workloads', 0). The only reason why the following query
    INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])
    VALUES(getdate(),SYSTEM_USER,-1,-1,-1,'ErrorProcedure, nvarchar(126)',-1,'ErrorMessage, nvarchar(4000)')
    
    was not found in cache (and, as a result, has incorrect plan handle in event session) is that Microsoft is not able to handle accurate auto parametrisation.
    Even if I force it as it is described here.
    ALTER DATABASE AdventureWorks2008 SET PARAMETERIZATION FORCED
    
    It does not work properly in extended event session.
    But, if I run this query in SSMS and open execution plan I can see that query is accuretly autoparametrised as
    INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage]) values(getdate(),suser_sname(),@1,@2,@3,@4,@5,@6)
    
    
    Yes, my original statement is not parametrised consciously. It is real life, some application developers are not aware of parameters and stored proceduers. This is not my fault. I just state it. Your query is parametrised:
    INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])
    VALUES(@ErrorTime,@UserName,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorProcedure,@ErrorLine,@ErrorMessage)
    and it works accuratly! I can see accurate execution plan. So parametrization is the only one reason of this argument. I absolutly agree with you that each query should be, at least, properly parametrized. But your explanation:
    "This was further enhanced in SQL Server 2008 with the addition of Optimize for Adhoc Workloads which requires a adhoc statement to hit twice before the plan stub is cached at all, which is why I included GO 5 for the INSERT in my modified repro, just in case you or someone else looking at this post happens to have that sp_configure option enabled."
    
    is irrelevant . GO 5 is not the point.  Optimize for Adhoc Workloads or not - it does not matter.
    The real problem is Microsoft can't implement auto parametrisation for many years. By the way, Oracle did it 10 years ago and they are proud of :)
    Regards,
    Andrew Butenko

    Tuesday, May 3, 2011 12:15 PM

  • Andrew, I don't know extended events as well as Jonathan does. Jonathan is known as the foremost expert for extended events in the SQL community.

    I do know enough that the problem you're stating is a well-known problem. A query plan handle is nothing more than a pointer. It's not some magical hash of the query plan that SQL Server can rebuild a query plan from. If you capture the plan handle, you generally have a limited time to get the plan from cache before it goes out of cache. Once it is out of cache, you can't get the plan. And SQL absolutely cannot reconstruct the query plan from the handle.

    Jonathan understands what you are saying. It seems like you're refusing to hear him because he's not saying what you want to hear.




    Robert, you can believe Jonathan, but there is no reason to trust. You have to understand what is the real problem. That is not an issue of cache size. That pointer is for non parametrized query. It does not hit cache at all.


    <Edited by Jonathan Kehayias to remove inappropriate language from this post>


    Tuesday, May 3, 2011 12:22 PM

  • Andrew, I don't know extended events as well as Jonathan does. Jonathan is known as the foremost expert for extended events in the SQL community.

    I do know enough that the problem you're stating is a well-known problem. A query plan handle is nothing more than a pointer. It's not some magical hash of the query plan that SQL Server can rebuild a query plan from. If you capture the plan handle, you generally have a limited time to get the plan from cache before it goes out of cache. Once it is out of cache, you can't get the plan. And SQL absolutely cannot reconstruct the query plan from the handle.

    Jonathan understands what you are saying. It seems like you're refusing to hear him because he's not saying what you want to hear.




    Robert, you can believe Jonathan, but there is no reason to trust. You have to understand what is the real problem. That is not an issue of cache size. That pointer is for non parametrized query. It does not hit cache at all.


    <Edited by Jonathan Kehayias to remove inappropriate language from this post>


    I have edited your post to remove the inappropriate language from it.  The use of inappropriate language is against the forums rules.

    I am going to lock this thread to end the discussion here since no new information is being added of benefit to the thread.


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, May 3, 2011 3:17 PM
    Moderator
  • Jonathan,
    Your explanation is 100% misleading. I do not use ad hoc optimisation ('optimize for ad hoc workloads', 0). The only reason why the following query
    INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])
    VALUES(getdate(),SYSTEM_USER,-1,-1,-1,'ErrorProcedure, nvarchar(126)',-1,'ErrorMessage, nvarchar(4000)')
    
    was not found in cache (and, as a result, has incorrect plan handle in event session) is that Microsoft is not able to handle accurate auto parametrisation.
    Even if I force it as it is described here.
    ALTER DATABASE AdventureWorks2008 SET PARAMETERIZATION FORCED
    
    It does not work properly in extended event session.
    But, if I run this query in SSMS and open execution plan I can see that query is accuretly autoparametrised as
    INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage]) values(getdate(),suser_sname(),@1,@2,@3,@4,@5,@6)
    
    
    Yes, my original statement is not parametrised consciously. It is real life, some application developers are not aware of parameters and stored proceduers. This is not my fault. I just state it. Your query is parametrised:
    INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])
    VALUES(@ErrorTime,@UserName,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorProcedure,@ErrorLine,@ErrorMessage)
    and it works accuratly! I can see accurate execution plan. So parametrization is the only one reason of this argument. I absolutly agree with you that each query should be, at least, properly parametrized. But your explanation:
    "This was further enhanced in SQL Server 2008 with the addition of Optimize for Adhoc Workloads which requires a adhoc statement to hit twice before the plan stub is cached at all, which is why I included GO 5 for the INSERT in my modified repro, just in case you or someone else looking at this post happens to have that sp_configure option enabled."
    
    is irrelevant . GO 5 is not the point.  Optimize for Adhoc Workloads or not - it does not matter.
    The real problem is Microsoft can't implement auto parametrisation for many years. By the way, Oracle did it 10 years ago and they are proud of :)
    Regards,
    Andrew Butenko

    It is not misleading at all, you didn't read or test what I was showing obviously because I am not using optimize for adhoc workloads in my demonstration either, but the INSERT is auto parameterized by SQL Server.  The GO 5 is there in case you happen to turn that on as explained in my post.  

    Microsoft has been doing auto parameterization of SIMPLE Statements like your INSERT since 2005 and it has had matching options to Oracles CURSOR_SHARING = FORCED option with Forced Parameterization since 2005 as well.  The matching option to Oracles CURSOR_SHARING = SIMILAR is the optimize for adhoc workloads config, and just like SQL Server, these are not set as defaults, you have to enable time specifically for the Oracle Database, the default is CURSOR_SHARING = EXACT.

    I am not going to continue arguing plan caching and parameterization in SQL Server with you, since you don't want to listen to what is being explained, but just want to talk about how Microsoft is doing it wrong.  Hopefully, your connect item will get an update from the team confirming exactly what I showed in the repro that I posted above and to your connect item.  If you use SET STATISTICS XML ON and look at the information contained in the XML Showplan from the actual execution you would see that it is being auto parameterized like I've tried to explain.

     


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, May 3, 2011 3:37 PM
    Moderator