January 10, 2013 at 7:02 pm
I am an ERP software specialist.
I have installed SQL Server 2008 R2 at several sites and generally have MSSQLSERVER service logging on with the Local System Account.
That is how I have it on my own PC too.
I have been fiddling with my own installation of SQL Server and have found that when I change the service login account from Local System Account to an account with admin rights on my local PC, then SQL Server runs really slow, like REALLY slow!
For example, when SQL is running under Local System Account & I run a query
SELECT * FROM MyTable
Then the results are displayed instantly.
Change SQL service to run with a domain account (or an account set up on my local PC with admin privileges) then the same query runs so long I have not bothered to wait to see how long it takes - I have waited about 2 minutes max. It should be instant! The table has maybe 200 records.
Ideas?
January 11, 2013 at 4:08 am
That is not behavior I've run into before. Is there something special about your domain setup? I use domain specific logins for all my installations and haven't seen this type of behavior ever.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2013 at 4:11 am
I can think of a couple of things to try.
First go into SQL Server Configuration Manager and verify that shared memory is ahead of TCP/IP for the instance on your local machine. If not then you are using TCP/IP for connections to SQL, even if that connection is local and will be slower than shared memory in returning results.
Another thing to check is how SQL is authenticating you.
Is this instance of SQL on a domain or a workgroup? I wonder if the difference in performance you are seeing is due to the difference between Kerberos and NTLM authentication. Set SQL to use local system and then run this query:
select
auth_scheme,
login_name
from
sys.dm_exec_connections c
inner join sys.dm_exec_sessions s
on c.session_id = s.session_id
where c.session_id = @@spid
order by login_name
Note the authentication scheme used. If you are connecting to SQL over TCP it should list Kerberos. Now change SQL to use a local system account, restart and run the query again. If it says NTLM then I wonder if your performance difference is due to NTLM vs Kerberos authentication.
Joie Andrew
"Since 1982"
January 13, 2013 at 4:07 pm
Thanks - I ran that script for both different service login accounts.
Both times the script gave identical results:
auth_scheme SQL
login_Name sa
Bizarrely too, I also did a SELECT * FROM MyTable when logged in under the domain account, and this time it was fast, that is - the expected time. Must've been something playing silly buggers on my PC.
I'll keep switching between the two over the next few days and see what happens.
January 14, 2013 at 3:30 am
Using a domain account that is in the local Administrators group in Windows 2008 or above DOES NOT mean you get the same privilege set that you get when running under NT Authority/System.
Windows 2008 and above have User Account Control, and one aspect of this is that accounts in the local Administrators group get two sets of privileges. By default they use the local User set of privileges. They only get to use the full Administrator set when the application is 'Run as Administrator'.
There is in fact no need whatsoever for the account running the SQL Server services to be a member of the local Administrators group. What is needed is for the account to be explicitly given the privileges that it needs to run SQL Server efficiently.
The Microsoft documentation has a ilst of the rights needed in various circumstances, but my consolidated ist would grant the SQL service account the following rights. Some of these are set up by the SQL install or SQL Configuration Manager, but others you need to grant explicitly. When you have done this, the domain account should perform at least as well as the Local System account.
seAssignPrimaryTokenPrivilege
seBatchLogonRight
seCreateGlobalPrivilege
seImpersonatePrivilege
seIncreaseQuotaPrivilege
seLockMemoryPrivilege
seManageVolumePrivilege
seProfileSingleProcessPrivilege
seServiceLogonRight
seSystemProfilePrivilege
seTcbPrivilege
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply