Capture execution diagrams with SQL Server 2005 Profiler

Tim Chapman

Executable diagrams are one of the best tools for diagnosing query errors in order to adjust the execution of queries on SQL Server. In previous versions of SQL Server 2005, the only option for query performance was text diagrams or graph execution diagrams for manual queries. On SQL Server 2005, you can capture execution plans in two new ways that will give you more capabilities to diagnose and tune database queries.

You can identify query execution diagrams through a trace on SQL Server and through dynamic management windows. In this article, we will use SQL Server 2005 Profiler to generate random tracking instructions to capture the execution plan of the statements executed on the database server.

Follow these steps to open SQL Server 2005 Profiler.

1. Select SQL Server Profiler from the Tools menu on SQL Server Management Studio. SQL Profiler will be opened with a blank screen.

2. In the File menu, select New Trace and connect to the server you want to monitor.

3. At the Trace Properties screen, select the Events Selection tab to select the events you want to monitor. At the bottom right of the window, select the Show All Events check box. This option will expand all available events to follow.

4. Navigate to Performance section and select Showplan XML event. This event captures diagrams implemented in an XML format that you can graphically view in SQL Profiler or SQL Server Management Studio. See Figure A.

Capture execution diagrams with SQL Server 2005 Profiler Picture 1
Picture A

What is important is what you are following. If too much information is tracked, it is likely to cause a slow server database operation; Instead, keep track of the information you need and not too much.

Once you select the Showplan XML event, you will see that the Performance title is the Events Extraction Setting tab. This tag offers two options for saving XML data from Profiler tracking. The first option allows you to save the execution plan generated from tracking one or more XML documents. This option is good because it stores executable diagrams in a .sqlplan file that you can open with SQL Server Management Studio to view. This option allows you to view all execution plans at the same time. See Figure B and Figure C.

Capture execution diagrams with SQL Server 2005 Profiler Picture 2
Figure B

Capture execution diagrams with SQL Server 2005 Profiler Picture 3
Figure C

Eliminate all events related to login information or existing connections. RPC: Events completed in each step perform a procedure to call the remote database. This will track the first stored procedure from any application that finds the database.

You should make sure that you will not track any unnecessary data. You should remove the stored procedure sp_reset_connection because it is called by the database tool for group connection purposes. To do this, click the Column Filters button in the Trace Properties window as shown in Figure D. This Edit Filter window displays the columns you have selected and can continue filtering. ObjectName columns are not selected, so you will only filter on TextData columns. Then enter the stored procedure in the Not Like section. This will prevent calling the database into the tracking table. You can also use some other filters like: LoginName filter (If you want to see the database activity of a certain user), either the Duration and Reads columns (if you are looking for solid queries special). After selecting the filter, you can start the tracking process.

Capture execution diagrams with SQL Server 2005 Profiler Picture 4
Figure D

Figure E is a list of activities on the server. Some server databases have been set up for transaction copies to explain calling some copy system procedures. The following example highlights one of the Showplan XML events that gives the same visual execution diagrams if they are implemented in Management Studio. This is an incredible tool to instantly point out potential problems with queries.

Capture execution diagrams with SQL Server 2005 Profiler Picture 5
Figure E

Figure F shows the result. The SQLPlan file is created from the track you just made. SQL Server Management Studio recognizes these types of files, so you can open the file to see all the execution plans captured by SQL tracking, which is very convenient for adjusting purposes.

Capture execution diagrams with SQL Server 2005 Profiler Picture 6
Figure F

4 ★ | 1 Vote

May be interested

  • What is a capture?What is a capture?
    capture has many different meanings and is used in many fields, including in the field of technology.
  • How to draw pictures and diagrams in WordHow to draw pictures and diagrams in Word
    microsoft word not only supports you to edit documents but also supports you with many features, it provides us with drawing tools and drawing diagrams easily and conveniently.
  • Detects code execution vulnerabilities in WinRAR, noting more than 100 infringement casesDetects code execution vulnerabilities in WinRAR, noting more than 100 infringement cases
    a code execution vulnerability in winrar has been exploited more than 100 times separately by hackers in the first week since it was revealed, and it is expected that this number will continue to increase in the future.
  • Hundreds of thousands of IoT devices are likely to be attacked by vulnerabilities on the serverHundreds of thousands of IoT devices are likely to be attacked by vulnerabilities on the server
    on christmas day, a vulnerability affecting web servers was embedded with hundreds of thousands of iot devices, namely goahead, a web server created by embedthis software.
  • 26 useful screen capture apps for macOS26 useful screen capture apps for macOS
    by default, macos has an impressive screen capture capability, but if you need to capture multiple screens, you will begin to notice its limitations. see the following third-party options to expand your screen capture toolkit.
  • Instructions on how to draw diagrams in WordInstructions on how to draw diagrams in Word
    the use of drawing models in word content will help readers more generalize and better understand the content, as well as increase the content of the content.
  • ABS function in SQL ServerABS function in SQL Server
    this article will show you in detail how to use the abs () handling function in sql server with specific syntax and examples to better visualize and capture functions.
  • The difference between web server and app serverThe difference between web server and app server
    you have probably seen that the terms web server and app server are often used interchangeably as if they are related to the same thing and also facilitate the website to function properly. but in reality, they are not the same.
  • Learn about SQL Injection and how to prevent itLearn about SQL Injection and how to prevent it
    sql injection is one of the types of web hacking by injecting sql query / command codes into input before transferring to the web application, you can login without a username and password, remote execution (remote execution), dump data and retrieve the root of sql server.
  • Multithread (Multithread) in C #Multithread (Multithread) in C #
    [thread in c #] a thread is defined as an execution path (execution path) of a program. each thread defines a single control line. if your application includes complex and time-consuming activities, it is often useful to set up execution paths or thread, with each thread performing a specific task.