January 20, 2021
Hot Topics:

Query Analyzer Tips and Tricks

  • By Mike Gunderloy
  • Send Email »
  • More Articles »

6. What's Up With This Query?

If you're faced with a query that has a performance issue, query analyzer is your first stop for gathering information. You have easy access to four different ways of looking at query performance:

  • Ctrl+L will show you the estimated execution plan before you run the query.
  • Ctrl+K will show you the actual execution plan after you run the query.
  • Ctrl+Shift+T will open the trace pane, showing you the trace events on the server as you run the query.
  • Ctrl+Shift+S will show you client-side statistics after you run the query.

There are items on the Query menu for each of these panes, just in case your brain is already too full to hold new shortcut keys.

7. Customize Connection Properties

When you fire up Query Analyzer and connect it to a database, it sets a few defaults - for instance, ANSI style null handling. If you'd like to tweak the connection properties for your own server environment, select Connection Properties from the Edit menu to open the dialog box shown in Figure 3, and click to your heart's content.

Editing connection properties

8. Get Back Wide Results

If you run a query that returns a column with lots of data, you'll discover that Query Analyzer truncates the results after the first 256 characters. This is especially annoying when you're working with FOR XML queries, which can return thousands of characters in an XML document format. Fortunately, this limitation is easy to modify. Select Options from the Tools menu, and navigate to the Results tab of the Options dialog box. Enter a new value for the Maximum Characters Per Column property and you're all set. While you're there, take the time to click around the rest of the Options dialog box; Query Analyzer lets you tweak quite a few things to match your own preferences.

9. Query Debugging

Query Analyzer contains a complete debugger. You can single step through stored procedures, inspect the value of local and global variables, supply values for parameters, inspect the callstack when multiple procedures are nested, and so on. The easiest way to get started is to right-click a stored procedure in the Object Browser window and select Debug . Figure 4 shows the debugging environment in action. This can be a real lifesaver when you're trying to figure out what's wrong with a complex stored procedure.

Debugging a stored procedure

10. The Tools Menu the Way You Want It

The Query Analyzer Tools menu is extensible. Select Customize from the Tools menu to open the Customize dialog box, and switch to the Tools tab. Here you can enter new values to appear on the menu, and specify the executable file to call for each entry. You can also pass the server name, database name, or user name to the external utility.

And Yes, There's More

I hope this article has convinced you that Query Analyzer can be a powerful tool for creating, executing, and debugging SQL statements. There are plenty of features that I didn't cram into this top ten. For example, the Query Analyzer editor supports bookmarks, forcing case, adjusting tabs, and more. If this is a product that you use on a frequent basis, you owe it to yourself to keep digging and find out how to use it more efficiently. The time spent learning will be amply repaid.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his latest 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

This article was originally published on October 7, 2004

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