Troubleshooting SQL Server 2005 performance counters problems
If you have problems with SQL Server 2005 performance counters, review this troubleshooting
checklist to find potential solutions.
1. Install the latest SQL Server 2005 service pack.
Because many SQL Server 2005 performance counters bugs were fixed in SQL Server service packs,
you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2005 service pack was service pack 3.
Check this page to obtain the service pack 3 for SQL Server 2005:
http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en
2. Check that SQL Server 2005 is running, if the SQL Server counters are not
visible in the System Monitor.
If SQL Server is not running, the SQL Server counters are not visible in the System Monitor.
After starting SQL Server 2005, if the System Monitor is open, close it, and then re-open it.
Keep in mind, you should always close and re-open System Monitor, if it was opened before you
start SQL Server 2005.
3. SQL Server 2005 Analysis Services performance monitor "Connection:Successes/sec"
counter do not report values.
In this case, the "Connection:Successes/sec" counter always return the value zero. This is
SQL Server 2005 bug. To work around this problem, you can monitor connection activity through
the Audit Login trace event in SQL Server Profiler.
4. SQL Server 2005 Analysis Services performance monitor "Proc Indexes:Rows/sec"
counter do not report values.
In this case, the "Proc Indexes:Rows/sec" counter always return the value zero. This is SQL
Server 2005 bug. To work around this problem, you can monitor indexing activity by using the
"EventSubclass 20" column of the "Progress Report EventClass" event class in SQL Server Profiler.
5. SQL Server 2005 Analysis Services "Storage Engine Query:Avg Time/Query"
counter do not report values.
In this case, the "Storage Engine Query:Avg Time/Query" counter always return the value zero.
This is SQL Server 2005 bug. To work around this problem, you can track query times in the
"Duration" column of the "Query End EventClass" event class in SQL Server Profiler.
6. The Event ID 1023 may occur when you use System Monitor to collect data from
SQL Server 2005 Analysis Services performance counters.
In this case, the counter data is not logged. This problem occurs when you run System Monitor
on a Windows XP-based computer to collect data from SQL Server 2005 Analysis Services performance
counters. This problem occurs because the Performance Logs and Alerts service (Smlogsvc.exe) does
not have sufficient permissions to access the performance counter DLL of SQL Server 2005 Analysis
Services. To resolve this problem, you should grant the Read & Execute permission to the service
the Performance Logs and Alerts service (Smlogsvc.exe) runs under on the following directory:
C:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\Bin
In this directory the performance counter DLL (Msmdctr90.dll) for SQL Server 2005 Analysis
Services is located.
7. When you use the mining model to generate predictions and the prediction process
takes a long time, the Predictions/sec counter for the MSOLAP$InstanceName:Data
Mining Prediction performance object has a very small value.
In this case, this counter should have a value of several hundred or a larger value. This is SQL
Server 2005 bug. This bug was fixed in SQL Server 2005 service pack 3. To resolve this problem,
install the latest SQL Server service pack.
8. When you define an alert activity on a fractional performance counter of an
instance of SQL Server 2005 in System Monitor the performance counters do not
appear in the performance counter list.
In this case, the alert activity is not executed when the alert criteria are met. This problem
occurs because these performance counters use an absolute value instead of a percentage value.
This is SQL Server 2005 bug. This bug was fixed in SQL Server 2005 service pack 3. To resolve
this problem, install the latest SQL Server service pack.
