February 17, 2019
Hot Topics:

SQL Profiler Tips and Tricks

  • March 16, 2005
  • By Mike Gunderloy
  • Send Email »
  • More Articles »

6. Locating Deadlock Causes

If you're having intermittent deadlock problems, it can be tough to figure out where they're coming from. This is especially true if your server is busy: how do you even spot the deadlocks doing by? SQL Profiler can help you here. Set up a trace and in the Events selection expand the Locks group, then select the Lock:Deadlock and Lock:Deadlock Chain events. If you monitor for these two events, SQL Profiler will produce a trace that contains details on just the deadlocks on your server. Record whatever identifying information you want - for example, the application name, logon name, and so on - and you'll be well on your way to figure out where the culprits are.

7. Auditing Logins

How about tracking the user activity on your SQL Server? You can use SQL Profiler for this too. Again, the key lies in properly choosing the events that you profile. Set up a trace that monitors the Security Audit:Audit Login and Security Audit:Audit Logout events, and send it to a trace table. Then you'll have a persistent record right in your database of who was using the database and when they were using it.

8. Watching for Table Scans

Another good use of SQL Profiler is to find queries that are causing table scans on your server. You want SQL Server to be using indexes to find the data that your users require - not to be looking through every row! To check, turn on a trace with the MISC:Execution Plan event. This will capture the query execution plan for every query. Then look over these plans for any that include a "Table Scan" or "Clustered Index Scan" (which indicates that the server is scanning all rows in the index, not that it's using the index to find a particular row). You can then examine those particular queries in more detail to see whether adding additional indexes to your database's tables could make them more efficient in the future.

9. Using Keyboard Shortcuts

Like any other Windows application, SQL Profiler supports keyboard shortcuts for more efficient use. If you use this tool frequently, you'll probably want to memorize some of the most useful ones:

  • Ctrl+Shift+Delete to clear the current trace window
  • Ctrl+F to open the Find dialog box
  • F3 to find the next match
  • Shift+F3 to find the previous match
  • Ctrl+N to open a new trace
  • F5 to start a replay
  • Shift+F5 to stop a replay
  • F9 to toggle a breakpoint
  • F10 to single-step
  • Ctrl+F10 to run to the cursor

10. Choosing Data Columns

Finally, remember that you don't always have to accept the default data columns for your traces. You might find that SQL Server suggests too much or too little data for your tastes. In many cases, the NT User Name and application name will be irrelevant for troubleshooting SQL issues, and you can remove them from the data to avoid cluttering up the display. On the other hand, if you're tracing events for multiple databases, you'll probably want to throw in the database name. There's a lot of other information available, from the duration of the event to the name of the object affected by the current statement, so take a look at the list before you just blindly click OK.

And Yes, There's More

As you should be able to tell by now, SQL Profiler is a vital tool for diagnosing SQL Server issues of all types. When you're trying to figure out what the heck is going on, especially with a heavily-loaded server, a well-chosen trace can help you pick out just the key events that you need to diagnose a problem. Spend some time to get acquainted with the events that you can trace and the data that you can capture, and you'll find many uses for this tool in the future.

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his recent book, Coder to Developer from Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.

Page 2 of 2

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date