SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Local System Account=fast, domain acct=slow


Local System Account=fast, domain acct=slow

Author
Message
Danster
Danster
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 90
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?
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40220 Visits: 32659
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Joie Andrew
Joie Andrew
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2339 Visits: 2032
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"
Danster
Danster
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 90
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.
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5832 Visits: 3866
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search