Professional Microsoft® SQL Server® 2008 Administration

13.3. Choosing the Appropriate Monitoring Tools

Once you define your monitoring goals you should select the appropriate tools for monitoring. The following list describes the basic monitoring tools:

• Performance Monitor: Performance Monitor is a very useful tool that tracks resource use on Microsoft operating systems. It can monitor resource usage for the server and provide information specific to SQL Server either locally or for a remote server. It can be used to capture a baseline of server resource usage, or it can monitor over longer periods of time to help identify trends. It can also be very useful for ad hoc monitoring to help identify any resource bottlenecks responsible for performance issues. It can be configured to generate alerts when predetermined thresholds are exceeded.

• SQL Profiler: This tool is a graphical application that enables you to capture a trace of events that occurred in SQL Server. All SQL Server events can be captured by this tool into the trace. The trace can be stored in a file or written to a SQL Server table.

  SQL Profiler also enables the captured events to be replayed. This makes it a very valuable tool for workload analysis, testing, and performance tuning. It can monitor a SQL Server instance locally or remotely. You can also use the features of SQL Profiler within a custom application, by using the Profiler system stored procedures.

• SQL Trace: SQL Trace is the T-SQL stored procedure way to invoke a SQL Server trace without having to start up the ...