Besides SSMS, another great tool available to database developers and DBAs to view query plans and troubleshoot query compilation or execution issues is the SQL Server Profiler. In the Profiler, all the showplan events are listed under the Performance Event category. All the SQL Trace events generate showplan information at query-level granularity, i.e. a single XML document is generated for each query. There are 9 showplan events in SQL Server 2005! In this post, I will describe the commonly-used ones.
Showplan XML – This event is new to SQL Server 2005. It generates the query plan in XML and displays it as text in the TextData column. It also displays the query plan in graphical format in the profiler trace window. When enabled, this trace event is generated every time a T-SQL query is executed. It displays batch information along with the Showplan by grouping together statements that are executed in a batch. This trace event is equivalent to using Showplan XML SET OPTION in SSMS.
select * from employees where hiredate < ‘1-1-2001’
Besides the XML events, the Performance Events category also contains other legacy showplan events. Of these I find the Showplan Statistics Profile event useful in displaying the execution stats in tabular format. The Performance Statistics event provides additional information (such as sql_handle, number of recompiles, etc.) which can be used to debug costing, plan generation, IO bottlenecks and other such related issues. Most of the legacy events generate Showplan in binary format which is displayed in the BinaryData column. The binary xml is converted to text format and is displayed in the expanded trace event viewing pane. We recommend using XML showplan event over the legacy showplan events since they contain additional information such as missing indexes, rows processed on each thread in a parallel query plan, memory grant per iterator, etc.
If using the Profiler to generate trace events is not an option, consider using SQL Trace to collect server trace data. SQL Trace is a mechanism that SQL Server supports to generate and capture server-side trace events using system stored procedures.
SQL Server Query Processing