Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Local System Account=fast, domain acct=slow Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 7:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 5:35 PM
Points: 46, Visits: 74
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?
Post #1405719
Posted Friday, January 11, 2013 4:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #1405876
Posted Friday, January 11, 2013 4:11 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 11:07 AM
Points: 710, Visits: 1,452
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"
Post #1405878
Posted Sunday, January 13, 2013 4:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 5:35 PM
Points: 46, Visits: 74
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.
Post #1406524
Posted Monday, January 14, 2013 3:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,900, Visits: 3,301
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 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1406635
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse