November 20, 2011 at 7:22 am
Did a Perfmon on a production box.
Memory\Available MBytes - Avg - 11,914, MAX - 12,007
Memory\Pages\Sec - Avg 17, MAX 822
Buffer Cache Hit Ration - Avg 100, Max 100
Buffer Manager \Page Life Expectancy - Avg - 59,000, MAX - 60,500
User Connections - AVG - 500, MAX - 750
Memory Grants Pending - AVG - 0
SQL - Batch Request\Per Sec - AVG - 883 , Max - 1204
Compilations \ Sec - AVG - 17, MAX - 120
Disk Reads \ Sec - 0 AVG
Disk Writes \ Sec - 72 AVG
Processor Queue Length - AVG - 1, MAX - 16
1. WHAT IS THE PROBLEM, AND WHERE MIGHT BE THE PROBLEM
2. IDEAL SOLUTION FOR IT.
Thanks for u time.
Learner
November 20, 2011 at 7:36 am
The very first question is, is there a problem?
Very few of those counters have specific values that say for certain that there is a problem, it's deviation from normal that's most important. So, are you experiencing any problems? If so, what problems are you experiencing? Are any of those counter values very different from normal for this server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2011 at 8:23 am
GilaMonster:
To be honest not sure; its only that the processes running are all sleeping processes and are in MSDB, Master; and they use a lot of CPU. How can we cut the usage of CPU to minimum numbers.
I need a query to find the the processes using high CPU so that I can figure out which processes are using what. And once I have that I need to find what are those processes running; I can do it by DBCC INPUT BUFFER but do we have another query which gives out the result in text format.
Do the perfmon numbers look normal.
thanks,
S
November 20, 2011 at 9:04 am
goofy2sql (11/20/2011)
GilaMonster:To be honest not sure; its only that the processes running are all sleeping processes and are in MSDB, Master; and they use a lot of CPU. How can we cut the usage of CPU to minimum numbers.
Sleeping means they're waiting for something to do. Sure those aren't system processes? System processes are connected permanently from the time that the server starts, and those CPU numbers in sys.dm_exec_sessions are cumulative. Hence if the server's been up a long time, those values can look high.
I need a query to find the the processes using high CPU so that I can figure out which processes are using what. And once I have that I need to find what are those processes running;
Do the perfmon numbers look normal.
The only person who can answer that is you. Normal for perfmon counters means normal for that server and that application with that specific workload. It's not going to be the same as any other system or workload. The key to interpreting perfmon counters is to identify what is normal for your system. Once you know what's normal for your system, then you can tell when the perfmon counters are abnormal.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2011 at 4:28 am
The only number in there that means anything without a comparison to other numbers is the processor queue length. A length of 16 suggests you might have some CPU conflicts going on, but I'd want to see a lot more information and suggestions about issues. Are queries running slow? Are you seeing blocked processes?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 21, 2011 at 5:54 am
Grant:
Thanks for your feed back. Well we are just seeing HIGH CPU/ IO pressure thats the only problem. No blocked processes.
thanks,
S
November 21, 2011 at 7:02 am
NM
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2011 at 8:17 am
CPU & IO pressure are different. I'd need a lot more information to make even vague suggestions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 21, 2011 at 8:32 am
Read through the articles I posted, or read through chapters 1 and 3 (at least) of this: www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Or both.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2011 at 10:20 am
GailM. what do you mean by NM
November 21, 2011 at 10:21 am
goofy2sql (11/21/2011)
GailM. what do you mean by NM
NM = nevermind.
She usually does that to edit her post out completely (since we can't delete them).
November 21, 2011 at 10:24 am
I am trying to figure out If the problem is with SQL or memory. Since there is 11GB of memory I dont see memory problem but processor queue length thats 1 and above is considered CPU bottleneck. The only number which look unusual is SQL Batch requests which is way above then 100...is that a sign of inefficient execution plan!.....I need to understand this problem from two sides If SQL then how to start and If its the box then how to start......thanks for the links from GailM. Probably will do it later today.
thanks again
S
November 21, 2011 at 10:57 am
goofy2sql (11/21/2011)
I am trying to figure out If the problem is with SQL or memory. Since there is 11GB of memory I dont see memory problem but processor queue length thats 1 and above is considered CPU bottleneck. The only number which look unusual is SQL Batch requests which is way above then 100...is that a sign of inefficient execution plan!.....I need to understand this problem from two sides If SQL then how to start and If its the box then how to start......thanks for the links from GailM. Probably will do it later today.thanks again
S
A queue is an indication that things are waiting on the processor, the CPU. That's unrelated to memory problems. Batch requests is just a measure of load. 100 means... 100. It's less than 101, more than 99. It doesn't mean anything unless you compare it to some other situation, ie: "Normally my system has about 20 batch requests a minute, but today it's at 100 and I'm seeing CPU queueing." That would be useful. Just saying that it's 100 doesn't mean anything. And, in terms of batch requests, it's a very low number.
You need to read through Gail's articles. The thing is, you're tossing around numbers and concepts that aren't related to each other and aren't related to some specific performance issue or concern and asking us to interpret them. We can't. The measures and approaches have to be fairly systematic in order to understand what's occurring on a machine, especially remotely, through the interpretation of typing stuff into these windows instead of looking directly at what's occurring on the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 21, 2011 at 11:03 am
Also I think you really need to read Jonathan's book (I posted the link)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply