February 7, 2011 at 1:58 am
Hello,
We have a server with 2*quad xeon 5550 processor. CPU usage rarely goes up 40% .
The only appliction instlled on server is SQL server. The trasaction rate is 2000 transactions/s.
procesor que lenght is also about 2. Every thing seems normal, except context switches which has an average of 88000 and max of 232000 during work hours. I've read in SQL server documents that a number larger that 15000 shows cpu problem. Is it problem whil other cpu counters seems normal?
Regards,
Parissa Bakhshi
February 7, 2011 at 6:19 am
It may be an issue, and it may not. Any time someone puts forward numbers like that, you have to assume they're a guideline, not an absolute.
Are you monitoring waits? What are the top 3 or 4 waits? If they're not CPU related, you may not have an issue. Are there any other issues you're running into, long running queries, blocking? If everything is fine, then I wouldn't get too concerned about one number.
"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
February 8, 2011 at 5:57 am
Thanks for you reply
As a matter of fact we have long time ruuning queries issue,
Top 4 waits:
-latch
-logging
-buffer I/O
-Transactions
I don't know much about these waits
February 8, 2011 at 6:41 am
Those waits are I/O related. Spend your time working on that. Don't worry about the memory for now. Keep an eye on it, of course, but don't focus there.
"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
February 8, 2011 at 6:55 am
But I/O can be anywhere. the simple I/O issue is disk speed,while we have many I/O issues.
another question is that I didn't see any wait resource showing CPU operation and performance. How can I make sure if high rate context switches is not itself a problematic issue?
February 8, 2011 at 8:12 am
parissa_bakhshi (2/8/2011)
But I/O can be anywhere. the simple I/O issue is disk speed,while we have many I/O issues.another question is that I didn't see any wait resource showing CPU operation and performance. How can I make sure if high rate context switches is not itself a problematic issue?
Because you have no other indications of CPU problems. No one counter ever completely defines an issue. You have to take any counter in combination with all other counters. You've said that you have I/O problems. It's entirely possible that the I/O problems are causing a certain amount of context switching as a given thread stops to wait for I/O, context switches to the next thread, which is waiting for I/O, so the next thread gets switched to, etc.. This is why wait states are such a great way to performance tune. Rather than look at a metric out of context, you get a very explicit measure, this is what most of the system is waiting on. Focus there.
"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
February 8, 2011 at 8:58 am
How can I find which I/O is problematic? Is it memory which is unable to respond to paging request in a timely fashion? disk and memory delay in transfering paged files?
February 8, 2011 at 9:10 am
parissa_bakhshi (2/8/2011)
How can I find which I/O is problematic? Is it memory which is unable to respond to paging request in a timely fashion? disk and memory delay in transfering paged files?
Do you gather performance metrics using a server side trace? Figure out which queries are running longest or using the most i/o, and working from there. Also, you can see what a query is waiting on, in real time, if you look at sys.dm_exec_requests.
"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
February 8, 2011 at 12:15 pm
1-Sure,I've used perfmon and every things seemed ok, except context switches.
and also memory , but SQL always takes the memory till a process need it. goof buffer cache hit ratio rate, disk read/write is ok...
2- how can I Figure out which queries are running longest or using the most i/o?
3-what does buffer I/O wait mean?
4- Do you know of any good related resources?
February 8, 2011 at 12:36 pm
parissa_bakhshi (2/8/2011)
1-Sure,I've used perfmon and every things seemed ok, except context switches.and also memory , but SQL always takes the memory till a process need it. goof buffer cache hit ratio rate, disk read/write is ok...
2- how can I Figure out which queries are running longest or using the most i/o?
3-what does buffer I/O wait mean?
4- Do you know of any good related resources?
I'll answer all these at once. Look at my signature lines. My book will help with all this.
"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
February 8, 2011 at 11:06 pm
You mean the book which is published by APRESS?
written by Grant Fritchey and Sajal Dam?
February 9, 2011 at 4:49 am
parissa_bakhshi (2/8/2011)
You mean the book which is published by APRESS?written by Grant Fritchey and Sajal Dam?
Yep. My book covers a pretty hefty chunk of what you're asking about.
"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
February 9, 2011 at 5:30 am
That book will definitely help you long-term (good one Grant!). However, if you want to get things running better NOW, hire a performance tuning professional and have them monitor/fix your system while mentoring you on how to do the same. Fastest way to learn what is important to learn and fastest way to get your system running faster too!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 23, 2011 at 11:49 am
Hi again,
Thanks for your kind replies. they were useful. They helped me out of my problem. The high context switches rate was for I/O waits, not for CPU. When I increased the RAM, context switches rate decreased from 150,000 to 9,000
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply