Showplan Trace Events
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.
- Showplan XML For Query Compile – Also introduced in SQL Server 2005, this event is similar to the Showplan XML event except that Showplan output is generated only when the query is compiled (or recompiled). For subsequent executions, if the query plan is retrieved from plan cache, no showplan information is displayed. This event is less expensive from a performance perspective and best for quick view of the generated query plan.
- Showplan XML Statistics Profile – This event is similar to the Showplan XML event, it not only contains the compile-time plan information, but also includes the runtime query execution statistics such as actual number of rows and executions per iterator, memory grant and degree of parallelism, etc. The event is generated once per execution. The showplan output is displayed as XML in textual format in the TextData column. The query plan portion for the query below is shown in the attached document:
use nwind
go
select * from employees where hiredate < '1-1-2001'
go
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.
Gargi Sur
SQL Server Query Processing