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.
SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location
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)
9 Comments. Leave new
Nice blog Pinal Sir
I’ve faced a strange behaviour: setting the sql service account to a domain account from SQL Configuration Manager fails because a password error (but the password was correct!). Then i’ve tried to set this change from “Services”, writing the SAME password, and all it’s ok.
Then, i’m come back to SQL Configuration Manager, and i’ve restarted SQL Server Service from there, and all works.
We had this same problem, and this article helped me. Thanks! 1 question and 1 comment:
question: Why are you setting “Log on as batch job” and “Act as part of the Operating System”? My other SQL Servers do not have this, and I did not set them on the problem server, and it works fine.
comment: Even after all of these steps, I still needed to reboot the server to clear the error- something about creating the user profile for the new service account I guess…
Thanks again Pinal!
For what it is worth, we ran into the same issue and we *DID* use Configuration Manager to update the service accounts. We have never used any other method. We added the domain account to all the sql server ACLs as well as to local Administrators, it’s a member of sysadmin server role, we reloaded the counters, and we’ve double-checked the permissions in Local Security Policy. No joy, we still get error 8319/3409 when we start, which goes away when we revert to LocalSystem. We tried logging in as the domain account to create a user profile, with no success. This is SQL 2008 R2 Standard + Windows 2008 R2.
I’d also like to echo what Clint said. “Log on as a batch job” and “Act as part of the operating system” are not mentioned anywhere in the BOL or in any of Microsoft’s documentation as permissions necessary for the engine or agent service accounts.
Got the same error when changing service account and we also *DID* use configuration manager. A restart of the whole server cleared the error.
Thanks for this Thomas. I changed the service account using configuration manager and was unable then to get performance counter results. Spent ages looking for the reason. swapped it back to the original and it worked then back to the new one and it didn’t. I hadn’t restarted the server as there were multiple instances but following your comment I have done this and it has fixed the problem.
This blog post saved me a lot of time and effort after changing the service account for a SQL 2014 server from LocalSystem to gMSA. Thanks Pinal!
Hello,
we also used the SQL Server Configuration Manager to change the service account from domain users to managed service accounts.
There are two almost identical sqlservers, the change on the first was successfull, on the second the performance counter are missing now.
On other machines the counters sometimes come back after the second/third/… restart of sqlserver.
I can not find any differences between the mashines.
It is possible, that a 3rd-party-monitoring-tool is the cause?
What is required to prevent the missing performance counters before changing the service account.
Hello,
I had exactly the same issue and thanks to this workaround i found that also in the regedit where the OS is installed you have to go to HKEY LOCAL MACHINE, right click, permission and in ” advanced ” check that the SYSTEM and ADMINISTRATORS are not only in “ read “ mode but also in “ read and full control “
That helped me a lot and now the performance counterr issue has gone…