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.
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.
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.
IE 7
Firefox 2.0
