我没有MS SQL(SQL Express 2008)的“完整”版本,所以我没有探查器工具。
我想查看由我的实体框架代码生成的SQL,但是我发现的所有示例都使用了
var x = from u in table select u;
语法类型;但我的大部分查询更像是..
var x = context.Users.Single(n => n.Name == "Steven");
语法类型。如何查看通过这种编码方式生成的SQL?有任何想法吗?
Express Edition是否支持扩展事件?如果是这样,它将以类似于Profiler的方式捕获语句并sp完成事件。
编辑: 我已将其更改为使用内存目标,而不是文件目标。理想情况下,取消注释这些WHERE部分,并用适当的用户名替换以仅捕获感兴趣的事件,或者您可以使用spid进行筛选WHERE (([sqlserver].[session_id]=(56))),例如。
WHERE
WHERE (([sqlserver].[session_id]=(56)))
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='test_trace') DROP EVENT SESSION [test_trace] ON SERVER; CREATE EVENT SESSION [test_trace] ON SERVER ADD EVENT sqlserver.sp_statement_completed( ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text) -- WHERE (([sqlserver].[username]='Domain\Username')) ), ADD EVENT sqlserver.sql_statement_completed( ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text) --WHERE (([sqlserver].[username]='Domain\Username')) ) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF) ALTER EVENT SESSION [test_trace] ON SERVER STATE = START
并查看结果(使用Adam Machanic的XE代码生成器生成的查询)
DECLARE @session_name VARCHAR(200) = 'test_trace' SELECT pivoted_data.* FROM ( SELECT MIN(event_name) AS event_name, MIN(event_timestamp) AS event_timestamp, unique_event_id, CONVERT ( BIGINT, MIN ( CASE WHEN d_name = 'cpu' AND d_package IS NULL THEN d_value END ) ) AS [cpu], CONVERT ( BIGINT, MIN ( CASE WHEN d_name = 'duration' AND d_package IS NULL THEN d_value END ) ) AS [duration], CONVERT ( BIGINT, MIN ( CASE WHEN d_name = 'object_id' AND d_package IS NULL THEN d_value END ) ) AS [object_id], CONVERT ( INT, MIN ( CASE WHEN d_name = 'object_type' AND d_package IS NULL THEN d_value END ) ) AS [object_type], CONVERT ( DECIMAL(28,0), MIN ( CASE WHEN d_name = 'reads' AND d_package IS NULL THEN d_value END ) ) AS [reads], CONVERT ( VARCHAR(MAX), MIN ( CASE WHEN d_name = 'session_id' AND d_package IS NOT NULL THEN d_value END ) ) AS [session_id], CONVERT ( INT, MIN ( CASE WHEN d_name = 'source_database_id' AND d_package IS NULL THEN d_value END ) ) AS [source_database_id], CAST((SELECT CONVERT ( VARCHAR(MAX), MIN ( CASE WHEN d_name = 'sql_text' AND d_package IS NOT NULL THEN d_value END ) ) AS [processing-instruction(x)] FOR XML PATH('') ) AS XML) AS [sql_text], CONVERT ( DECIMAL(28,0), MIN ( CASE WHEN d_name = 'writes' AND d_package IS NULL THEN d_value END ) ) AS [writes] FROM ( SELECT *, CONVERT(VARCHAR(400), NULL) AS attach_activity_id FROM ( SELECT event.value('(@name)[1]', 'VARCHAR(400)') as event_name, event.value('(@timestamp)[1]', 'DATETIME') as event_timestamp, DENSE_RANK() OVER (ORDER BY event) AS unique_event_id, n.value('(@name)[1]', 'VARCHAR(400)') AS d_name, n.value('(@package)[1]', 'VARCHAR(400)') AS d_package, n.value('((value)[1]/text())[1]', 'VARCHAR(MAX)') AS d_value, n.value('((text)[1]/text())[1]', 'VARCHAR(MAX)') AS d_text FROM ( SELECT ( SELECT CONVERT(xml, target_data) FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name = @session_name AND st.target_name = 'ring_buffer' ) AS [x] FOR XML PATH(''), TYPE ) AS the_xml(x) CROSS APPLY x.nodes('//event') e (event) CROSS APPLY event.nodes('*') AS q (n) ) AS data_data ) AS activity_data GROUP BY unique_event_id ) AS pivoted_data;