Data Management optimize

Capture execution plans with SQL Server 2005 Profiler

Tim Chapman uses SQL Server 2005 Profiler to generate custom trace scripts to capture execution plans for statements that are executed on his database server.

Execution plans are one of the best tools for diagnosing problem queries and for performance tuning queries on your SQL Server. Prior to SQL Server 2005, the only options for query executions were text plans or graphical execution plans for queries run by hand. In SQL Server 2005, you can capture execution plans in a couple of new ways that give you even more power when it comes to diagnosing and tuning database queries.

You can view query execution plans through a SQL Server trace and through dynamic management views. In this article, I will use SQL Server 2005 Profiler to generate custom trace scripts to capture execution plans for statements that are executed on my database server.

Follow these steps to open SQL Server 2005 Profiler:

  1. Select SQL Server Profiler from the Tools menu in SQL Server Management Studio. SQL Profiler will open with a blank screen.
  2. On the File menu, select New Trace and connect to the server instance that you want to monitor.
  3. On the Trace Properties screen, select the Events Selection tab to select the events that you want to monitor. In the lower right portion of the screen, select the check box for Show All Events. This will expand all of the available events to monitor in the trace.
  4. Navigate to the Performance section and select the Showplan XML event. This event will capture statement execution plans in an XML format, which you can view graphically in SQL Profiler or SQL Server Management Studio. View Figure A.

Figure A

Figure A

It is important to know what you are profiling inside your traces. If you monitor too much information, it can cause your database server to perform slowly; instead, monitor what you need to see and not too much more.

Once I select the Showplan XML event, the Performance heading I see is the Events Extraction Settings tab. The tab gives me two options for saving XML data from my Profiler trace. The first option allows me to save the execution plans created from the trace as one or many XML documents. I like this option because it saves the execution plans to a .sqlplan file that I can open in SQL Server Management Studio to view the graphical plans. This allows me to see all of the execution plans that occurred at one time. View Figures B and C. Figure B

Figure B

Figure C

Figure C

I exclude any events related to login information or existing connections. The RPC:Completed event fires for each execution of a remote procedure call on the database. This will track the initial stored procedure call from any applications that hit the database. (If I want to capture any stored procedures that are executed by the RPC:Completed event I would include the SP:Completed event as well, but for the purposes of this example, RPC:Completed is fine.)

Before I begin my trace, I want to make sure that I don't include any unnecessary data. I don't want to include the stored procedure sp_reset_connection because it gets called by the database engine for connection pooling purposes. To do this, I click the Column Filters button on the Trace Properties screen, which brings up the screen in Figure D. This Edit Filter screen presents me with the columns I currently have selected and can filter on. I don't have the ObjectName column selected, so I will just filter on the TextData column. Then I enter the stored procedure call into the Not Like section. This will prevent that database call from showing in my trace. Other common filters I use are: the LoginName filter (if I want to see database activity for a certain user) or the Duration and Reads columns (if I am looking for specific long running queries). After I select by filter, I can start my trace. Figure D

Figure D

Figure E is a partial listing of the activity on my server. A few databases on this server are currently set up for transactional replication, which explains some of the replication system procedure calls. In the example below, I highlight one of the Showplan XML events, which does show the same visual execution plan as if we had executed it in Management Studio. This is an amazing tool for immediately seeing potential problems with long running queries. Figure E

Figure E

Figure F is the resulting .SQLPlan file created from the trace I just ran. SQL Server Management Studio recognizes these types of files, so I can open this file and see all of the execution plans that were captured by the SQL trace, which makes it really convenient for tuning purposes. Figure F

Figure F

Stay tuned

Next week, I will describe how to write custom scripts using dynamic management views to capture the same type of execution plan information without having to run a profiler trace.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

Get database tips in your inbox

TechRepublic's free Database Management newsletter, delivered each Tuesday, contains hands-on SQL Server and Oracle tips and resources. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

0 comments