SQL Profiler
This article is excerpted from Essential SQL Server 2000: An Administration Handbook (Addison Wesley, 2001, ISBN: 0201742039).
This chapter is from the book
This chapter is from the book
This chapter is from the book
The SQL 2000 Profiler is the primary tool that you use to monitor the server.
You can use Profiler to track SQL statements for troubleshooting, or even monitor the behavior of the server engine that you normally can't see. You can also use Query Analyzer and Windows NT/2000 Performance Monitor to monitor certain aspects of the server.
I won't spend a great deal of time on Profiler in this excerpt, but I will show you a quick overview of its use.
Click on Start, Programs, Microsoft SQL Server, Profiler, as shown in Figure 1.
){kind=link}
Figure 1. Profiler.
You'll need to connect to a server to monitor it, creating a session called a Trace.
To do this select the menu item File, then New, and then Trace, which brings up the login dialog box, as you can see in Figure 2.
){kind=link}
Figure 2. Profiler login.
After you log in, you're shown the General Trace Properties, as shown in Figure 3.
){kind=link}
Figure 3. Profiler Trace Properties—General tab.
This panel has quite a few things to fill out, but for now, you'll concentrate on using the tool rather than everything that it can do.
You can leave the panels as they are. Before you move on, notice that the trace can be run as is, saved to a table, or saved to a file. Recording these traces can be useful for troubleshooting.
Next select the Run button, and Profiler begins to record activities from the SQL Server. The items that it is recording aren't important right now, but just to show the type of output it produces, open up Query Analyzer, and perform a very basic query against the pubs database.
You can see the results of that activity displayed in the Profiler screen displayed in Figure 4.
){kind=link}
Figure 4. Profiler output.
Here, you see the actual SQL statement you typed in Query Analyzer in Profiler's output, line by line. Clicking on a line in the top panel displays what SQL Server "saw" coming in.
You use the green arrow on the icon bar to start or continue a trace, use the double bars next to it to pause one, and use the red square to stop the trace. You can also use the shortcut keys shown in Table 1 to manage Profiler.
Table 1. Profiler Shortcut Keys
|
CTRL+Shift+Delete |
Clear a trace window |
|
CTRL+F4 |
Close a trace window |
|
- |
Collapse a trace grouping |
|
CTRL+C |
Copy |
|
ALT+Delete |
Delete a trace |
|
+ |
Expand a trace grouping |
|
CTRL+F |
Find |
|
F3 |
Find the next item |
|
Shift+F3 |
Find the previous item |
|
F1 |
Display available help |
|
CTRL+N |
Open a new trace |
|
ALT+F7 |
Replay the settings |
|
CTRL+F10 |
Run to cursor |
|
F5 |
Start a replay |
|
F11 |
Step |
|
Shift+F5 |
Stop a replay |
|
F9 |
Toggle a breakpoint |