Feb. 11, 2011, 1 a.m.
posted by routh
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.