Skip to main content

How to use the SQL Profiler

  1. Last updated
  2. Save as PDF

Overview

 

How To Use SQL Profiler For TIER® Troubleshooting

Definition         

SQL Profiler is used to capture and analyze SQL Server events.

Launching SQL Profiler

SQL Profiler can be launched from two different locations.

  1. From the Windows Start button, select All Programs > Microsoft SQL Server > Profiler.
  2. From within Enterprise Manager, select Tools from the menu bar, then SQL Profiler.

Creating a Trace

Once the Profiler has been opened, existing traces can be opened or new traces can be created.

  1. Click the New Trace Icon or Select New Trace from the program File menu.
  2. Connect to the SQL Server you want to trace.
  3. The Trace Properties Dialog box appears.To set up a new trace, complete the four tabs of this box.
  4. General
    • Trace templates define a set of choices for the other panels in the dialog box. For starters, select the default SQL ProfilerStandard template. (Once you understand how the trace works, you can select other existing trace templates or create one of your own.)
    • This tab also allows you to save the data to either a file or table.
    • Finally, you can enable and specify an end trace time if you are not going to be actively looking at the trace.
  5. Events
    • The default event selection is a good starting point if you want to see SQL statements sent to your database.
    • You may want to add more events once you become familiar with the program. For example, start time/end time and items under stored procedures are helpful. For more information on what each event does, highlight each event and read the description summaries at the bottom.
  6. Data Columns
    • Tell the SQL Profiler how much information to save for each event that it captures. If you’re the only user, then the login name & application name are not useful information. DatabaseName is a good item to select if you use multiple databases. You can also use this tab to specify the order of the data selected by highlighting and clicking the up or down buttons.
  7. Filters
    • This tab lets you narrow the events a bit. For example, to tell SQL Profiler to only listen for events for a specific user login, expand the Login node in the treeview and then click the Like node. ​This will reveal a text box where you can type the login name.
  8. To start the trace, click the Run button at the bottom of the new Trace Dialog box. Trace results will appear in the window. 
  9. There are several buttons on the SQL Profiler toolbar to operate the trace after you’ve started it.
    • Properties: Allows you to change the settings of a trace.
    • Find: Allows you to find data displayed in a trace.
    • Clear: Clears the trace window.
    • Pause: Temporarily stop a trace
    • Stop: Stop a trace. (Clicking start again erases the data displayed.)

Sample Traces

SQL Profiler shows you the behind the scenes SQL actions of TIER® and can be used for several troubleshooting tickets.

Now that you understand how to set up and run a basic trace, let’s look at some troubleshooting examples.

Troubleshooting Data Truncation Errors

This example shows you how to set up a trace that records a user’s SQL actions when performing basic steps in TIER® and capture queries that return errors.

Field length short error messages are caused when the size of the field in form design is larger than the size of the field in SQL.

If a user receives Field Length Short error messages when saving a form, SQL Profiler can be used to capture the query that runs when the form is saved. Then you can use SQL Query Analyzer to troubleshoot the query and fix the data column.

This type of trace is helpful for showing the order of events and for indicating exactly what runs in SQL when a form is opened, and in what order. 

Steps

  1. Obtain the following information for Trace Filters:
    • Database Name
    • User Login Name
  2. Launch SQL Profiler and begin a new trace.
  3. Select the SQL Profiler Standard Trace Template on the Filters window. We’re troubleshooting this on the spot and will not need to title or save this trace.
  4. Remove the Security Audit section of the Events tab by highlighting the category on the right and clicking the Remove button.
  5. Modify the data selected on the Data Columns tab remove Reads, Writes, Duration, Client ProcessID, SPID & StartTime.
  6. On the Filters tab, enter the user’s database and login name obtained from step 1.
  7. Contact the user and let them know you want to capture their error message.
  8. Start the trace by clicking Run and ask the user to click Save to create the Field Length Short error message and let you know when it appears on their screen.
  9. Click Stop on the trace toolbar.
  10. The query that caused the error message will be displayed as the last item on the list.
  11. Copy/Paste this query into SQL Query Analyzer by highlighting the data in the lower half of the results pane and run the analyzer to confirm this is the troublesome error.
  12. Once the bad query has been identified, troubleshoot the query by commenting out lines of the insert statement to identify the problem data column.
  13. Once the data column causing the error has been identified, increase the size of this field in the table design.
  14. After correcting the data column, ask the user to refresh their login and save the form again to confirm the problem has been resolved.
 

Improving TIER® Performance

SQL Profiler allows you to look for problem queries and long transactions. Long transactions can cause deadlocks, blocks and overall poor performance.

This example shows you how to set up a trace that records long transactions. After you’ve identified the long transactions, they can be analyzed and corrected through indexes, rewrites, etc.

Steps

  1. Launch SQL Profiler and begin a new trace.
  2. On the General tab, select the SQLProfilerTSQL_Duration template.
    1. Specify a trace file name and save location.
    2. Check the enable trace stop time and enter a stop time.
  3. On the Events tab, expand the locks and add the following by highlighting and selecting the Add button.
    • Lock: Timeout
    • Lock: Deadlock
    • Lock: Deadlock Chain
  4. On the Data Columns tab, modify the data selected by adding LoginName to the list of selected data columns.
  5. On the Filters tab, you may filter by duration if you want to display only items which ran for a particular period of time or greater.
  6. Click Run to begin the trace. Profiler will save the data to a trace file and stop recording transactions once the end time has reached.
  7. Review the data. Review the data by opening the trace file from within SQL Profiler by using the Open File icon on the profiler toolbar.
  8. The queries run will be displayed in the window ordered by duration.
  9. Review the list to identify problem queries – those with the longest durations as well as those that are deadlocked.
  10. Troubleshoot poor performing queries by rewriting or applying indexes where needed. See your Database Administrator or contact NetsmartCares Support for assistance with rewrites.
 

 

  1. Back to top
  • Was this article helpful?

Recommended articles

  1. Page type
    Topic
  2. Tags
    1. role:sysadmin
    2. search:ready
    3. SQL profiler