SQL Performance issue based on service acct
-
Recently we have been testing our server which is running under the local admin acct. Running a script that scripts the entire database to files on the hard drive was taking 20 + hours, not sure how much longer it would take because I would kill the process. We have checked for locks, boosted SQLs priority for the CPU, etc.. we couldn't seem to find a solution to the horrible performance on this script. The CPU showed during this time a usage of 2 or 3%, and SQL was maxing out it's available memory.
Changing SQL to run under the system account all of a sudden the script was running in 5 minutes and the memory was no longer maxing out.
Does anyone know why the local admin acct caused such an issue? eventually SQL will need to run under a domain acct so we need to find out why we have such an issue.
Thanks!
Sheena
-
When you change the account to the new user, I bet you had to stop and restart SQL, right? I am thinking that SQL was in need of a restart. SQL has this annoying habit of taking all memory you give it, and never give it back. I would also bet your max memory is set to all the memory the server has, leaving none for the server itself to run the task you asked it to do. That said, I bet that you could change the userid back, restart SQL, and it will perform well. I would look at reducing the max memory SQL can use, and / or adding more memory.
just my 2 cents
Cory
-- Cory
-
Sheena,
Tell us about this local admin account and how it was set to be a startup account for SQL Server service. It may be security issues both ways:
1. SQL Server: This "admin" account does not have sufficient rights in SQL Server for being a startup account. He/She/It has to be a member of sysadmins in SQL Server AND the account should be set up as a startup account in Enterprise Manager, not through the Services applet of Control Panel. It may not have drive permissions or something.
2. Windows: This "admin" account does not really have admin rights on SQL Server resources and directories or maybe registry entries too. Yes, I did see it happen several times when someone removed Administrators (and in 2 cases Local System) from SQL Server folders directory or other directories. Or if you tell the scripting to access files in other directories /shares then the access should be provided too.
Also, tell us if Builtin\Administrators have sysadmin access in SQL Server.
Yelena
Regards,Yelena Varsha
-
Regarding the response from Cory,
We have restarted the service (and machine more than a few times
) because SQL was hogging all the server memory, then we monitered it to see how long it took the server to reclaim all the memory during the running process. We also decreased the max memory available to SQL to use way less, since based on our other implementations it shouldn't need anywhere near to what it was using. The server we use (test box) has 2 GB ram, more than enough for the database it's running. -
The local admin account was set to start SQL in Enterprise Manager.
Properties dialog of Local Server -> Security Tab->
Under "Startup Service Account" select "This Account". This is where the local admin user was entered.
I did check Builtin\Administrators and it does have sysadmin in SQL Server.
Could it be option 2? if so how would I check this?
Thanks,
Sheena
-
Sheena,
To check if the account has rights to SQL Server or other folders, right-clcik on the folder name in Windows Explorer, Properties, Security Tab. Highlight the account name and see permissions. If account is not there but say, Administrators group is there, see the permissions for the group. Also, the account may be a member of more then one group. To see effective permissions on the same Security tab click Advanced button. In the Advanced Security Settings click Effective Permissions tab. Select the name of a user or group and it will calculate effective permissions for this folder.
Regards,Yelena Varsha
-
The Administrator has full permissions and full effective permissions also. Any other possibilities?
-
Sheena,
You say that the scripts are outputted to files. Are ANY of the files created when running under local admin account? Also describe your actions when you "run a script". Is it as a job or in Query Analyzer or is it SMO or DMO script? Are there any domain policies in place for local admin account?
Regards,Yelena Varsha
-
Yes there are hundreds of files created during the script run, I'd say on a rough estimate, probably 2000.
We've run the job through Query Analyzer, osql and as a scheduled job.
The Local admin should have full control over the particular server we are using, at least I am not aware of any Domain policies that would affect it.
Something of interest though, we logged into the server specifically as the Administrator and noticed it takes forever just to open folders and programs, while concurrently, users that have logged in are getting excellent response time.
-
Sheena,
I think I know what could be the issue. Good observation!
We just lost the network for 15 min and it took FOREVER for me to open Start->Run or a Folder or Task Manager. I am familiar with the cause. It is because there is a mapped drive that could not be accesses and each Windows-related action tries to check mapped drives and waits for timeouts. As soon as I disconnected the mapped drive that was not accessible, everything become normal on my local machine.
Please check for the Mapped Network Drives for the local admin. If they are not accessible, disconnect them and check the performance again.
Please let me know if it helped.
Regards,Yelena Varsha
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply