If you ever find that you don’t see data being collected in Performance Advisor, you can usually get this resolved by following a few basic troubleshooting steps.
1. Review Security:
The first thing to check, especially if this is a newly monitored connection, is that all security requirements are met, as outlined in our post on Security Requirements for Performance Advisor.
If the problem is occurring on a previously monitored connection, please verify if there have been any changes made to the SQL Sentry Monitoring Service account, and that any changes were made via the Service Configuration Utility.
If you are monitoring SQL Server 2008 or newer, please review the SQL Server 2008 Security Concern post as well.
2. Review Error Messages:
Next, look to see if there is a red banner error in the Dashboard. You can get the details of the message by mousing over the banner, or by opening the client alerts by double-clicking the “traffic light” in the lower right-hand corner of the SQL Sentry Client application.
You will also want to check the System Status view. From the Navigator pane, expand the Monitoring Service group and double click the System Status node. By default, only errors will be shown in the System Status view.
Depending on the error messages you see, either on the Dashboard or in the System Status view, the following posts may be of assistance:
· Performance Advisor Initialization Problems (RPC Server Not Available, WMI Collection Failed, etc.)
· Missing Performance Counter Categories
· Monitoring 32-bit SQL Server installed on a 64-bit Windows machine
· Performance Advisor: WMI or Registry Access Denied
If in doubt, please send the error message you are seeing to firstname.lastname@example.org.
3*. Review Logs:*
If you don’t see any error messages, you will then want to look at the Failsafe Actions log in the SQL Sentry Client, as well as the Windows Event Logs, to see if these indicate any problems. If you are unsure, then please send these files in to email@example.com. You can export the Failsafe Actions log from the SQL Sentry Client by opening File -> Export Data while the Log screen is open.
4. Check Performance Counters:
From the monitored server itself, attempt to add SQL Server related performance counters to a session in “perfmon.exe”. If these counters are not available, SQL Sentry will not be able to collect them. Please see the post on Missing Performance Counter Categories.
If that test passed, attempt the same test, but do so from the system on which the SQL Sentry Monitoring Service is installed. If the counters are not available here, this indicates an issue with either network communication or with the “Remote Registry” service on the monitored server. This can usually be corrected by restarting that service. The “Remote Registry” service can generally be restarted without any down-time or adverse effect to current users. See the post on initialization problems in Performance Advisor. Note that you must be working under the security context of the Windows account used by the SQL Sentry Monitoring Service in order to properly perform the tests in this step. This ensures that your tests are performed with the same rights SQL Sentry has.
5. Scan For Configuration Changes:
Next, you should right-click the relevant Computer node in the Navigator pane and select “Scan for Configuration Changes.”
6. Restart SQL Sentry Monitoring Service:
If the “Scan for Configuration Changes” option doesn’t work, then restart the SQL Sentry Monitoring Service(s). If the issue still persists at this point, go back to Step 2 to see if any new error messages have appeared that indicate any additional issues.
7. Enable Server Service Tracing:
If restarting the SQL Sentry Monitoring Service doesn’t work, then you should enable the SQL Sentry Monitoring Service trace and let this run for a while. The log files will be named “rolling-log.txt” and “timedmethod-rolling.txt”. They will be located in the SQL Sentry installation folder. Note that the trace is set to rollover the logs at 5MB with a max of 15 files, at which point it will override the first file with a new one, so you should never see these logs exceed 150MB (total for both log types). You may be able to determine the issue by viewing the trace logs yourself, but you will generally want to send them to firstname.lastname@example.org. If the files are too large for email, just let us know and we’ll accommodate you with another method of file transfer.
8. Contact Support
Finally, if you aren’t able to get the issue resolved by following these steps, or need assistance at any time, then please contact email@example.com and include any information that you have gathered in the previous troubleshooting steps.