Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Local System Account=fast, domain acct=slow Expand / Collapse
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, June 27, 2016 1:46 AM
Points: 49, Visits: 86
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

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.

Post #1405719
Posted Friday, January 11, 2013 4:08 AM



Group: General Forum Members
Last Login: 2 days ago @ 4:59 AM
Points: 16,773, Visits: 31,522
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
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1405876
Posted Friday, January 11, 2013 4:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 938, Visits: 1,868
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:

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, June 27, 2016 1:46 AM
Points: 49, Visits: 86
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
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 22, 2016 2:11 AM
Points: 3,032, Visits: 3,704
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.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2016, 2014, 2012, 2008 R2, 2008 and 2005. 11 May 2016: now over 37,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
Post #1406635
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse