Microsoft SQL Server 2008 R2 Unleashed- P154:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1474 CHAPTER 39 Monitoring SQL Server Performance Even though many of the performance counters and relevant information are now available in the SQL Server Data Collector as mentioned previously the Data Collector does incur some overhead on SQL Server. Performance Monitor on the other hand incurs significantly less impact on SQL Server performance. In addition the SQL Server Data Collector currently doesn t have a built-in alerting capability. As mentioned previously you can set up performance counter alerts in Performance Monitor. In addition Performance Monitor enables you to monitor more than what is provided with SQL Server Data Collector including all aspects of the operating system as well as other applications. One other feature that s very useful with Performance Monitor logs is the capability to import performance counter logs into SQL Server Profiler. NOTE For more information on importing and viewing performance counter logs in SQL Server Profiler see Chapter 6. SQL Server Performance Counters For each SQL Server instance installed Performance Monitor has a number of SQL Server-specific performance objects added to it each with a number of associated counters. Each SQL Server instance has its own set of monitoring objects because you certainly wouldn t want to mix monitoring values across multiple instances. Performance counters for named instances use the naming convention MSSQL followed by the instance name for example MSSQL SQL2008DEV General Statistics . Performance counters for the default instance of SQL Server use the naming convention of SQLSERVER followed by the counter name for example SQLServer General Statistics . Table provides a list of the SQL Server performance counters available for SQL Server 2008 TABLE SQL Server Performance Objects Performance Object Description SQLServer Access Methods Information on searches and allocations of SQL Server database objects for example the number of index searches or number of pages .
