Performance tuning is a crucial aspect of managing a SQL Server database. As a DBA, one of the first tools you might use to monitor performance data is Perfmon. However, it’s important to note that the same PerfMon data is also exposed through dynamic management views (DMVs).
In a recent encounter with a client’s SQL Server box, I came across an uncommon issue where the sys.dm_os_performance_counters DMV was returning zero rows. Upon further investigation, I discovered that this issue occurred after a change in the service account. The client had made the mistake of changing the service account using the services.msc tool instead of the recommended SQL Server Configuration Manager.
Microsoft has provided ample documentation advising users to use the SQL Server Configuration Manager for such tasks. In this particular case, the ERRORLOG revealed the following error messages:
Error: 8319, Severity: 16, State: 1. Windows kernel object ‘Global\SQL_90_MEMOBJ_MSSQLSERVER_0’ already exists. It’s not owned by the SQL Server service account. SQL Server performance counters are disabled.
Error: 3409, Severity: 16, State: 1. Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.
To resolve this issue, the following steps were taken:
- Change the account that starts the SQL Server Service and SQL Agent Service to the Local System Account.
- Restart the SQL Server Service and SQL Agent Service to apply the changes.
- Grant the necessary permissions to the SQL Server account in the Local Security Policy > User Rights Assignment. These permissions include:
- Adjust Memory for A Process
- Log on as Service
- Log on as a batch job
- Lock pages in memory
- Act as part of the operating system
- Bypass traverse checking
- Replace a process level token
- Add the SQL Server Domain Account to the SQL Server Groups on the server.
- Change the account that starts the SQL Server Service and SQL Agent Service to the SQL Server Domain Account.
- Restart the SQL Server Service and SQL Server Agent Service.
- Check the ERRORLOG and performance counters to ensure they are functioning correctly.
It’s worth emphasizing the importance of changing the service account using the SQL Server Configuration Manager, as this can help avoid such issues. Following the above steps resolved the problem for my client.
Have you encountered a similar issue with SQL Server performance counters? Let me know in the comments below!