SQL Profiler and Query Analyzer

SQL Profiler and Query Analyzer

SQL Server comes with a couple tools that are as underappreciated by Web developers as the Visual Studio debugger. The Profiler and Query Analyzer let you find those pesky SQL queries and stored procedures that bring your server to its knees and take forever to complete.

The Query Analyzer can be run directly from the Start menu or from the Tools menu of the Enterprise Manager. At its most simple, it's a place to experiment with queries to make them do exactly what you want. The status bar will show you how long the query took. A simple SELECT from the analyzer is shown in Figure. To make your query go, just click the Play button in the toolbar.

3. Performing a simple query in the Query Analyzer.

One of the more useful operations of the analyzer is to explore the details of a query's execution plan. An execution plan describes how SQL Server will go about performing the operations you've specified. Execution plans are well beyond the scope of this book, but understanding what SQL Server is doing under the hood makes for a fascinating discipline. Figure shows the graphical representation of an execution plan.

4. An execution plan.

Although the analyzer can help you gauge performance before you start to test your application, the Profiler can help you find problems while your application is running. This program peeks into SQL Server's operation and monitors every single query executed against its databases. Among other things, it can show you what values are actually being passed into a query, rather than the values you think are being passed in. It is also available from your Start menu and from the Tools menu in the Enterprise Manager.

The first clue that indicates that you need to use the Profiler is that SQL Server is using a lot of CPU time on the Processes tab of the Task Manager. When your database is well tuned, SQL Server generally doesn't use much CPU time in most Web applications.

The easiest way to start a new trace is to specify an ending time for the trace. Generally a couple minutes worth of data will provide plenty of opportunities to find problem spots. Figure shows a complete trace with one item selected, whose details are shown in the lower pane.

5. The SQL Profiler in action.

In this case, I noticed that one query stood out in terms of CPU usage (scrolling right will show additional attributes associated with the query). Selecting the item shows what the underlying query was, and it gives me a clue as to where I should look for problems. The query shown is a simple SELECT statement with a single parameter, so the problem probably isn't the query itself, but the way the table is indexed. You'll find that indexing issues account for many SQL Server performance issues.

Getting the most out of SQL Server would be a book unto itself, so if you don't happen to have a database administrator on your staff (what staff?), I strongly suggest reading up on designing and maintaining SQL Server databases.

It might be out of scope for this book, but as we mentioned, indexing has a lot to do with how fast your database can find data. As a very general rule, it's a good idea to index any column that is frequently searched. Primary key columns are indexed by default. There is a performance trade-off in indexing columns because the index must be modified every time a new row is inserted into the column, so index carefully!

What about stored procedures? Stored procedures (or "sprocs," as they're often called) are intended to group complex queries into single units or to limit the amount of data that a user account may access. The long-standing myth is that it's faster for a sproc to get data than "ad hoc" SQL statements in your code because the sproc's execution plan is compiled and ready to go. However, simple parameterized queries like the one shown in Figure do have their execution plans cached, so there is little to no performance benefit to using sprocs for this kind of simple access.

     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows