{"id":500,"date":"2012-01-16T22:29:15","date_gmt":"2012-01-16T22:29:15","guid":{"rendered":"\/blogs\/jonathan\/post\/Does-filing-bugs-on-Microsoft-Connect-for-SQL-Server-work.aspx"},"modified":"2017-04-13T12:54:37","modified_gmt":"2017-04-13T16:54:37","slug":"does-filing-bugs-on-microsoft-connect-for-sql-server-work","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/does-filing-bugs-on-microsoft-connect-for-sql-server-work\/","title":{"rendered":"Does filing bugs on Microsoft Connect for SQL Server work?"},"content":{"rendered":"

In a word; YES! In a lot more words, not always in the way that we want it to, but there are plenty of cases where it actually works and changes are made to the product as a result.<\/p>\n

Now with that said, it doesn\u2019t work all the time, and it helps to realize that what is important to us as an individual user might not be important to the product as a whole.  Before you post comments, I am sure that there are plenty of cases out there where people can say that Microsoft Connect for SQL Server is broken.  I have personally been irritated to the point of posting negative comments on Twitter about the whole Connect process.  I feel that it is about time that I show the other side of the story as well and talk about some Connect successes that have occurred in the past year, and of course what better topic to do this with than Extended Events.  Over the next few weeks, I\u2019ll post a couple of different examples of Connect actually working and bringing about changes to the product that are beneficial to the community, starting with this post.<\/p>\n

Extended Events does not track insert statements<\/a><\/h1>\n

This Connect item is actually incorrectly titled and is based on some confusion about what the sqlserver.plan_handle action actually returns when executed in the engine.  I blogged about this with much more detail last year in my blog post; What plan_handle is Extended Events sqlserver.plan_handle action returning?<\/a><\/p>\n

If we revisit the Connect item there is a note that the sql_statement_completed event in SQL Server 2012 now includes a parameterized_plan_handle customizable column that can be used to retrieve the parameterized plan handle for statements that are auto-parameterized by SQL Server during their execution.  Taking the same original demo code from my previous blog post, we can now see how this Connect item has improved the ability to find information about plan caching in SQL Server 2012:<\/p>\n

\n
-- Create the Event Session<\/span>\r\nIF<\/span> EXISTS<\/span>(SELECT<\/span> * \r\n          FROM<\/span> sys.server_event_sessions \r\n          WHERE<\/span> name='SQLStmtEvents'<\/span>)\r\n    DROP<\/span> EVENT SESSION<\/span> SQLStmtEvents \r\n    ON<\/span> SERVER;\r\nGO<\/span>\r\nCREATE<\/span> EVENT SESSION<\/span> SQLStmtEvents\r\nON<\/span> SERVER\r\nADD<\/span> EVENT sqlserver.sql_statement_completed(\r\n    SET<\/span> collect_parameterized_plan_handle = 1\r\n    ACTION<\/span> (sqlserver.client_app_name,\r\n            sqlserver.plan_handle,\r\n            sqlserver.sql_text,\r\n            sqlserver.tsql_stack,\r\n            package0.callstack,\r\n            sqlserver.request_id)\r\n--Change this to<\/span> match<\/span> the AdventureWorks, \r\n--AdventureWorks2008 or<\/span> AdventureWorks2008 SELECT<\/span> DB_ID('AdventureWorks2008R2'<\/span>)\r\nWHERE<\/span> sqlserver.database_id=9\r\n)\r\nADD<\/span> TARGET package0.ring_buffer\r\nWITH<\/span> (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON<\/span>)\r\nGO<\/span>\r\n \r\n-- Start the Event Session<\/span>\r\nALTER<\/span> EVENT SESSION<\/span> SQLStmtEvents \r\nON<\/span> SERVER \r\nSTATE<\/span> = START<\/span>;\r\nGO<\/span>\r\n \r\n-- Change database contexts and insert one row<\/span>\r\nUSE<\/span> AdventureWorks2008R2;\r\nGO<\/span>\r\nINSERT INTO<\/span> [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])\r\nVALUES<\/span>(getdate(),SYSTEM_USER<\/span>,-1,-1,-1,'ErrorProcedure'<\/span>,-1,'An error occurred'<\/span>)\r\nGO<\/span> 10\r\n \r\n-- Drop the Event<\/span>\r\nALTER<\/span> EVENT SESSION<\/span> SQLStmtEvents\r\nON<\/span> SERVER\r\nDROP<\/span> EVENT sqlserver.sql_statement_completed;\r\nGO<\/span>\r\n\r\n-- Retrieve the Event Data from the Event Session Target<\/span>\r\nSELECT<\/span>\r\n    event_data.value<\/span>('(event\/@name)[1]'<\/span>, 'varchar(50)'<\/span>) AS<\/span> event_name,\r\n    event_data.value<\/span>('xs:hexBinary((event\/data[@name="parameterized_plan_handle"]\/value)[1])'<\/span>, 'varbinary(64)'<\/span>) as<\/span> parameterized_plan_handle,\r\n    event_data.value<\/span>('xs:hexBinary((event\/action[@name="plan_handle"]\/value)[1])'<\/span>, 'varbinary(64)'<\/span>) as<\/span> plan_handle,\r\n    event_data.value<\/span>('(event\/action[@name="sql_text"]\/value)[1]'<\/span>, 'varchar(max)'<\/span>) AS<\/span> sql_text\r\nFROM<\/span>(    SELECT<\/span> evnt.query('.'<\/span>) AS<\/span> event_data\r\n        FROM<\/span>\r\n        (   SELECT<\/span> CAST<\/span>(target_data AS<\/span> xml) AS<\/span> TargetData\r\n            FROM<\/span> sys.dm_xe_sessions AS<\/span> s\r\n            JOIN<\/span> sys.dm_xe_session_targets AS<\/span> t\r\n                ON<\/span> s.address = t.event_session_address\r\n            WHERE<\/span> s.name = 'SQLStmtEvents'<\/span>\r\n              AND<\/span> t.target_name = 'ring_buffer'<\/span>\r\n        ) AS<\/span> tab\r\n        CROSS<\/span> APPLY TargetData.nodes ('RingBufferTarget\/event'<\/span>) AS<\/span> split(evnt) \r\n     ) AS<\/span> evts(event_data)<\/pre>\n