| The SQL Profiler is not included with SQL Server Express or Visual Basic Express, so don't bother looking for it. It also can't monitor User Instances (at this point in time), so don't bother trying. It can, however, monitor both local and remote SQL Server service instancesall you need to do is open a connection to the target service. Let's walk through a typical SQL Server Profiler scenario. Suppose you have a new stored procedure you're trying to run, and you're wondering why it's getting a syntax error. Perhaps, your code or ADO.NET (like the CommandBuilder) is generating some of the code, and you wonder if it's confused (again). Adding Filter Criteria Before you run off and launch the Profiler to start scanning the stream of T-SQL being passed to your SQL Server instance, it's a really good idea to think about how you're going to pick your application's commands out of the flood of commands being sent to the server. Consider your instance of Visual Studio, Reporting Services, Notification Services, as well as every other application and instance of Visual Studio in your entire organization that's using the selected instance. SQL Server is constantly polling the server, running queries, and adding to the work SQL Server is asked to perform. Monitoring SQL Server with the Profiler is like trying to understand a scintillating conversation in a noisy baryou'll need a lot more than a few disjointed words to make sense of what your friend is saying. An easy way to "mark" the traffic your application generates is by setting the Application Name property in the ConnectionString when you open the connection. As I'll illustrate in a moment, SQL Profiler can create a trace that only reports on the activity from your application or any subset of the traffic. Without some kind of filter, you won't make much sense out of the data it presents. Suppose you're trying to use Visual Studio to walk through a stored procedure from the Server Explorer. In this case, Visual Studio will use the current Data Connection's ConnectionString to open the connection. Here is where you need to set the Application Name property. 1. | Right-click on the Data Connection you're using in the Visual Studio Server Explorer and choose "Modify Connection". This opens the Modify Connection dialog.
| | | 2. | Click the "Advanced" button. This opens the Advanced Properties dialog, shown in Figure III.1. Scroll to the Context section and set the Application Name (or Workstation ID) property to a unique value. The default value for Application Name is set by Visual Studio to ".Net SqlClient Data Provider" so this same Application Name is used by every other Visual Studio application using a Data Connection to access SQL Serverthat's why you need to make the Application Name key unique, so you can pick out your traffic from the cacophony of commands being monitored.
Figure III.1. Setting the Application Name (or Workstation ID) in the Advanced Properties dialog. 
| 3. | Click "OK" to accept the new ConnectionString for the selected Data Connection.
|
Configuring SQL Profiler Now that the ConnectionString has a unique value that can be used to filter its traffic out of the stream of trace events, I'm ready to start tracing. | | 1. | Launch the SQL Profiler by using the Start | All Programs | SQL Server 2005 | Performance Tools | SQL Profiler. Yes, the Profiler is also supported in older versions of SQL Serverit's been around for some time. All of the functionality demonstrated here is in SQL Server 2000 as well as SQL Server 7.0 (to a great extent).
| 2. | At first, the SQL Profiler is simply sitting there waiting for you to start a new Trace. We're about to do that. Click the File | New Trace menu. This opens the "Connect to Server" dialog. Fill in the server name\instance for the instance of SQL Server referenced in your Visual Studio Data Connection's ConnectionString (from step 1). Choose appropriate authentication credentials to log on to the SQL Server instance.
| 3. | This opens the Trace Properties dialog, shown in Figure III.2. If you accept the default properties, you'll probably get the unfettered stream of commands I've been talking about, so let's set the needed filters to make sure you see only pertinent events. Click on the "Events Selection" tab.
Figure III.2. The Trace Properties dialog is shown when creating a new trace. 
| | | 4. | This opens the Events Selection dialog, where you're presented with a dizzying array of options from which to choose. Feel free to experiment here, but we want to narrow our focus to setting the ApplicationName filter criteria, so click on that column, as shown in Figure III.3.
Figure III.3. The Trace Properties dialog Events Selection tab. 
| 5. | This opens yet another dialog that's used to set the criteria for the Application Name filter, shown in Figure III.4. Each of the other events and event columns has similar dialogs, so it's possible to set any number of filters on the data to be returned by the trace engine. Set the Like expression to test for your unique Application Name string. Notice that this is a Like expression, so you can use a wildcard (%) to make this easier. Click "OK" when you're done.
|
Figure III.4. Setting the Application Name event filter to test for your unique value. 
Starting the Trace Once SQL Profiler has been configured to filter out other traffic, we're ready to start the trace. I think you'll find that even given a filter that shows only traffic from your unique connection, you're going to want to revisit the Trace Properties dialog Events Selection tab to further refine the criteriayou're still going to get more information than you need. That's because Visual Studio performs a staggering number of housekeeping and other functions to gather information about the SQL Server objects it's being asked to manipulate. 1. | Click "Run" on the Trace Properties dialog to start the trace. This opens the Trace View window (shown in Figure III.5), which is simply a real-time view of all trace events that meet the criteria you set in the Trace Properties dialog. You should see an "ExistingConnection" event in the window (in green). We're ready to return to the application to start testing our stored procedure.
Figure III.5. The Trace View window shows unexpunged stream events. 
| | | 2. | Return to Visual Studio and, using the Server Explorer, right-click on the stored procedure to test (I'm testing the CustomerUpdate procedure), and choose either "Execute Procedure" or "Step Into Procedure". This opens the Run Stored Procedure dialog, shown in Figure III.6.
Figure III.6. Visual Studio prompting for stored procedure input (and input/output) parameters. 
| | | 3. | Click "OK" to accept the parameters. Sure, you can fill in the parameter values, but remember, this is an UPDATE stored procedure, so you'll be changing the data as you click "OK".
| 4. | Switch back to the SQL Profiler and observer the Trace View window, as shown in Figure III.5. This window is named based on the name you gave it in the Trace Properties dialog. Yes, you can have several traces running simultaneously. It should now show the T-SQL commands executed by Visual Studio when you executed or started to step through the stored procedure.
|
Evaluating the Trace As you can see in Figure III.6, SQL Server was asked to execute about five queries to step through the stored procedure. Sure, it might make sense to filter out the "BatchStarting" or "BatchCompleting" events (but not both). This would cut down the volume of events by half. This can be done by unchecking the undesired event in the Trace Properties dialog (as shown in Figure III.3). Note that Visual Studio (or SQL Server Management Studio) adds a lot of extra events to the streamit does quite a bit of work behind the scenes that (hopefully) won't appear on a production system where your applications are not competing with developers and Visual Studio queries. However, your SQL Server might have to support queries from FullText Search, Reporting Services, Analysis Services, and a host of other SQL Serverrelated services that can appear in the trace and can materially affect performance. A Few SQL Profiler Tips Here are a few tips and suggestions for optimal use I've learned over the years when working with the SQL Profiler: A trace can be paused and restarted at any time, but restarting the trace flushes the Trace View window. It makes a lot of sense to save your events filter configuration so that next time you start SQL Profiler, you can simply create a trace from an existing template. Remember that the SQL Profiler consumes CPU resources and RAM, so leaving it running (with an active Trace) can hurt performance. The SQL Profiler can be used to create a long-term trace log that can be fed back into SQL Server Management Studio tools to evaluate overall system performance and suggest changes in the indexes or other SQL Server configuration options. See BOL for more details.
|
