Datasheet
Wort c01.tex V3 - 12/17/2007 7:11pm Page 10
Part I: Finding Bottlenecks when Something’s Wrong
one stored procedure, there isn’t usually a need to revisit the problem statement or the plan of attack.
However, in some cases the reason for the stored procedure running slowly might be due to a server
resource bottleneck. It might be that the data has grown to the point where processing it now takes
more CPU than is available, that it needs more memory than is available, or that it now needs more I/O
capacity than is available. In these cases then the problem statement remains the same, but the plan of
attack might change to alter the data collection to review the server or SQL Server resources.
Tools
There is a wide variety of tools available to help you in your data collection and analysis, and to help with
preventive measures such as monitoring activity, and capturing a baseline of your system’s performance.
System Monitor
Also known as Perfmon or Performance Monitor on Windows Vista, this is the first tool you should think
of when looking at performance tuning. There is a massive number of counters available to show you all
aspects of performance from many different applications. Chapters 2 and 3 cover using System Monitor
and discuss in detail which counters to look at.
SQL Server Profiler
SQL Profiler is the tool of choice when you need to find long-running queries in a highly variable work-
load. Profiler lets you capture a record of every query executed by SQL over a period of time. This is
extremely useful when either there is a wide variety of queries run infrequently in the server or there
are ad hoc user queries running as well. Under those conditions other tools don’t help you find the long
running query, and that’s where Profiler comes in.
Using Profiler you can also capture a workload over a given period of time and then use this later to
replay against a restore’s database system. This is a great way of running a stress or performance test,
and for repeatedly reproducing a database workload.
Chapters 5 and 10 discuss using SQL Server Profiler.
SQL Server Management Studio (Query Analyzer)
For many SQL Server users, SQL Server Management Studio will be the application they spend their
work lives inside. It is now a Visual Studio compatible integrated development environment (IDE)
and provides a single place to perform all your SQL-related work. Starting with a new Visual Studio
solution/project-based approach, it includes:
❑ A server/database object explorer
❑ The template explorer, which is an invaluable aid for writing T-SQL scripts
❑ The Query Analyzer interface
❑ SQL Server profiler
❑ Database Tuning Advisor (DTA)
❑ A shell to launch third-party tools
SQL Server Performance Dashboard
SQL Server Management Studio comes with many performance-related reports already built. The SQL
Server Performance Dashboard reports are a suite of reports that can be downloaded and installed.
10