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.