The topic around performance tuning is the most sought out search on this blog. Almost every single day the top search that lands here are around performance or around some random errors one might receive from the application. If you are a DBA, the first high level tool that you might be using to watch perf data is around using Perfmon data. Interestingly, these PerfMon data is actually exposed via the DMV’s too.
One of uncommon issue, which I found on one of my client SQL Server box, was to get zero rows in sys.dm_os_performance_counters dynamic management views.
My first question was – “did that ever show some values?” and the answer was “yes. It was working and it broke when we changed service account”.
My next question was – how did you change the service account? And as expected, the answer was – we have used the services. ms. And once again, I wanted to pull my hairs because of seeing same mistake again. There has been enough documentation from Microsoft, which suggests to use the SQL Server Configuration Manager. I asked them to show ERRORLOG.
Here is what pointed to the issue.
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.
Below are the things which were done to fix the problem.
- Change the account that start SQL Server Service and SQL Agent Service to the Local System Account.
- Restart SQL Server Service and SQL Agent Service in order to apply the last change.
- The Follow permissions must be granted to the SQL Server account in the Local Security Policy> * User Rights Assignment.
- Adjust Memory for A Process
- Log on as Service
- Log on as a batch job
- Locks 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 start SQL Server Service and SQL Agent Service to the SQL Server Domain Account.
- Restart SQL Server service and SQL Server Agent Service.
- Check your ERRORLOG and performance counters.
This is one of the reasons, I strongly recommend changing the service account from the SQL Server Configuration Manager. Above worked for my friend. Have you faced some similar issue?
Reference: Pinal Dave (https://blog.sqlauthority.com)