asked
Profiler: How to capture an Execution Plan where a specific table is used?
Hi! I'm on SQL 2008 R2 and I would like to capture all execution plans of running SPs where a specific table is updated. Using the SQL Server Profiler I can set a column filter for TextData: LIKE %mytable% but it's not working out as expected. I'm not able to capture the plan :( Any hints highly appreciated! Thanks!
First off, I would absolutely not recommend doing this with the Profiler GUI or Trace events. The way that Trace works is that it captures all events first, then filters the results. In the case of capturing execution plans, one of the single most expensive events you can capture, you're going to be hitting the server quite severely. Instead, I suggest you use extended events to do this capture. They filter at the point of capture, reducing the Observer Impact on the system. While you can use the LIKE command for a filter criteria in the T-SQL text, as with regular T-SQL, that's going to be slow. If you have stored procedures, better to use their object IDs. Then you can filter by both database and object id to ensure you're only capturing plans for the events you want, further reducing the impact on your system.
Thanks Grant! I've ended up with the following: CREATE EVENT SESSION [mySession] ON SERVER ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1) ACTION(sqlserver.sql_text) WHERE ([object_id]=(954486479))) ADD TARGET package0.ring_buffer WITH (STARTUP_STATE=ON) GO But on my 2008 R2 machine I'm getting the following error message: Msg 25623, Level 16, State 1, Line 1 The event name, "sqlserver.query_post_execution_showplan", is invalid, or the object could not be found* This piece of code is accepted fine on my test machine which is a SQL 2012 instance. I suppose capturing the plan with extended events is not supported on SQL 2008 R2... Are there any alternatives?
I think you can try the pre_execution or the post_compilation events.
nope, similar error message: "Msg 25623, Level 16, State 1, Line 1 The event name, "sqlserver.query_post_compilation_showplan", is invalid, or the object could not be found" respectively: "Msg 25623, Level 16, State 1, Line 1 The event name, "sqlserver.query_pre_execution_showplan", is invalid, or the object could not be found" :( I'm afraid I need to go ahead with the Profiler :(
15 People are following this question.
