June 20, 2009 at 1:59 pm
I have a dedicated SQL Server running an instance of SQL 2000 and two instances of 2k5. Memory management is set statically and processor affinity is set to automatic as well as I/O affinity and boost SQL Priority is set on. Parametrization is set to 1. I am looking at task manager, perfmon and Performance Dashboard Reports on our only really active instance and something is not adding up. Perf Dashboard shows 25% + usage as "Other" (NonSql related), Taskmanager and Profiler are showing the same with typically one core (4 cores total) at 80-90% and the other three pretty flat which indicates to me that is a sql query(s) due to parameterization settings not letting the thread span cores. When I look at processes tab in Task Manager it showing (variable of course) 70% system idle process and 25% SqlService account (dedicated domain account for Sql Server) which indicates it IS SQL using up this amount of CPU time.
I check for long running queries and get none, no blocks and everything running fine. How do I determine if its is SQL using this amount of CPU time (profiler trace running now to capture it) or if its some other event on the server? Have any of you guys ever seen inaccurate results from Performance Dashboard reports?
Thanks!
'nix
June 22, 2009 at 9:13 am
To get an accurate picture of what is using your cpu you need to use perfmon, Processor\% Processor Usage\All Instances (I normally don't bother to add _Total) and Process\%Processor Usage\All Instances (again without _Total_)
This will tell you for sure what is using your 25% cpu.
Ed
June 22, 2009 at 9:23 am
There are some process tools at sysinternals as well that can help you dig down into more detail on which processes if you want to go that far.
I'd tend to do what Mr. Elliot suggested to get a rough idea.
June 22, 2009 at 9:44 am
Thank you sir! I've done that and it does show the higher than expected CPU usage. I'm adjusting the domain service accounts each instance uses to narrow down where its coming from. Different domain account for SQL Service account per instance. That should help. Cant afford in the budget any really cool nice tools like sysinternals so I'll do it the old fashioned way, rule out and investigate.
Does replication processes show up as a sql process in Performance Dashboard reports? Curious, but the process showing using the cpu is sqlservice.exe, just dont know which instance, two of the three using very little to 0 right now and cant tell which one is the offender for sure..but switching service accounts on the less used instances should help ID them.
'nix
June 22, 2009 at 10:00 am
To find out which process is using the cpu, add the counters "Process\ID Process\sqlservr.exe*"
Each instance has an identifier so if you have three instances you will have "sqlservr", "sqlservr#1", "sqlservr#2" - when you know which one of this is causing the cpu, use the corresponding "ID Process" to get the pid and then look at the current sql server log and it has the process id which matches up with the "ID Proces":
"Server Process ID is 0000"
As Steve mentioned you can also can get sysinternals procexp for free and might be eaiser:
http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
Ed
June 22, 2009 at 10:55 am
Awesome counter that I didnt know existed.. you guys rock!
Issue temporarily resolved. The cause is not known but restarting the instance stopped the problem. I’ll continue to monitor it to see if it returns. This is the instance I am doing replication from (Publisher) but that doesn’t seem to be the issue:
1.First stopped the log reader agent (CPU no change)
2.Stop the instance (CPU dropped to 6% from 36%)
3.Restart the instance (CPU, no change (didnt increase back up)
4.Restart replication (CPU no change ( no jump in CPU usage)
What was running under that instance causing the issue is not known at this time but the configs on it are the same as main instance with the exception, although a big one of replication on instance that I restarted.
I'm keeping a trace running on the metric you sent me and setting up an alert to email me if CPU usage goes above a certain number at a time of day I know it shouldnt which would replicate this problem, i.e. CPU goes up and stays, up with periodic dips meaning its not a straight line, 24/7.
Good stuff man!
'nix
'nix
June 22, 2009 at 2:58 pm
ok, more info I've found out....in the process of setting up a different domain account for one of the instances, SQL Agent for that instance would not restart. Investigating this issue led me to the Lock Pages in Memory setting in Group Policy. I had Administrators, one domain account that is used as a SA account along with the original account used to run SQL Service on all three instances. Adding the new account for the new instance fixed the SQL Agent not starting. Found that here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24480197.html
Now, having administrators and that other account in there tells me that anyone launching an application such as third party backup software or monitoring software locked its pages in memory and never let it go......ooops! Whether this could/did impact the CPU usage or not I dont know but its definitely fixed now removing Administrators and the other domain account with SA privileges... Setting up new accounts for running my backup software and monitoring software and getting them off of the same account running SQL Service. FunFun, but learning more every day and that is the point right?
'nix
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply