January 10, 2011 at 10:33 pm
waht is cpu bottleneck and what will do when it occurs?any one plz
January 11, 2011 at 12:51 am
srik780 (1/10/2011)
waht is cpu bottleneck and what will do when it occurs?any one plz
The first hit on Google for the keywords sql server cpu bottleneck:
http://www.sql-server-performance.com/tips/performance_monitor_cpu_counter_p1.aspx
A little research won't harm you.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 11, 2011 at 5:44 am
Hi,
I can help you may be.
As per my knowledge :
•Excessive Compilation And Recompilation
•Inefficient Query Plan
•Intra Query Parallelism
•Poor Cursor Usage
First you check all these areas to find cpu bottelneck.
Regards,
Shikha
January 11, 2011 at 5:48 am
Try Grant Fritchey's free eBook
January 11, 2011 at 7:02 am
As much as I love people reading my book, I'm not sure that one will help.
What will happen when the CPU is maxed out? Processes start to wait for CPU time. Performance slows down as requests pile up behind the process that is using all the CPU. So, you have to fix it. There are lots of causes. The trick is to figure out what's causing it, then address that. With 2005/2008, the best way to identify the cause of slow-downs and waits is to take advantage of the Dynamic Management Objects (DMO) such as sys.dm_exec_requests. For lots more detail on that, I'd recommend, in this order, Louis Davidson & Tim Ford's book, "Performance Tuning With Dynamic Management Views", and then my other book, "Performance Tuning Distilled."
"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
January 12, 2011 at 8:49 am
srik780 (1/10/2011)
waht is cpu bottleneck and what will do when it occurs?any one plz
A CPU bottleneck is when your CPU(s) are running so much that your performance suffers due to waiting for a slice of CPU time. It occurs when the CPU(s) are asked to do more than they can do per unit time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 12, 2011 at 1:54 pm
Check queries that have high cpu usage using activity moitor, then by thinking on execution plan u can solve problem. sql profiler + advisor may also help u.
January 12, 2011 at 2:41 pm
I would not push Advisor as a choice for improving performance.
"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
January 12, 2011 at 2:48 pm
farax_x (1/12/2011)
Check queries that have high cpu usage using activity moitor, then by thinking on execution plan u can solve problem. sql profiler + advisor may also help u.
I am curious what you mean by "advisor" here. Hopefully you do NOT mean Database Tuning Advisor!! Avoid that like the plague!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 12, 2011 at 11:47 pm
TheSQLGuru (1/12/2011)
farax_x (1/12/2011)
Check queries that have high cpu usage using activity moitor, then by thinking on execution plan u can solve problem. sql profiler + advisor may also help u.I am curious what you mean by "advisor" here. Hopefully you do NOT mean Database Tuning Advisor!! Avoid that like the plague!!
Just out of curiosity, why? I haven't really worked with it before, so I'm a bit in the dark here.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 13, 2011 at 6:00 am
Koen (da-zero) (1/12/2011)
TheSQLGuru (1/12/2011)
farax_x (1/12/2011)
Check queries that have high cpu usage using activity moitor, then by thinking on execution plan u can solve problem. sql profiler + advisor may also help u.I am curious what you mean by "advisor" here. Hopefully you do NOT mean Database Tuning Advisor!! Avoid that like the plague!!
Just out of curiosity, why? I haven't really worked with it before, so I'm a bit in the dark here.
Because it's very frequently wrong, and not just a little wrong. Not only can it suggest bad or redudant or even useless indexes, it can miss really obvious indexes too. It's primarily a marketing tool, not a performance tuning tool. You can use it, but never, ever, trust it.
"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
January 13, 2011 at 6:10 am
Grant Fritchey (1/13/2011)
Koen (da-zero) (1/12/2011)
TheSQLGuru (1/12/2011)
I am curious what you mean by "advisor" here. Hopefully you do NOT mean Database Tuning Advisor!! Avoid that like the plague!!
Just out of curiosity, why? I haven't really worked with it before, so I'm a bit in the dark here.
Because it's very frequently wrong, and not just a little wrong. Not only can it suggest bad or redudant or even useless indexes, it can miss really obvious indexes too. It's primarily a marketing tool, not a performance tuning tool. You can use it, but never, ever, trust it.
If it is such a crappy product, it is quite a shame that Microsoft pushes the use of the DTA through the certification exam. In those questions, using the DTA is portrayed is a best practice.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 13, 2011 at 6:16 am
Koen (da-zero) (1/13/2011)
Grant Fritchey (1/13/2011)
Koen (da-zero) (1/12/2011)
TheSQLGuru (1/12/2011)
I am curious what you mean by "advisor" here. Hopefully you do NOT mean Database Tuning Advisor!! Avoid that like the plague!!
Just out of curiosity, why? I haven't really worked with it before, so I'm a bit in the dark here.
Because it's very frequently wrong, and not just a little wrong. Not only can it suggest bad or redudant or even useless indexes, it can miss really obvious indexes too. It's primarily a marketing tool, not a performance tuning tool. You can use it, but never, ever, trust it.
If it is such a crappy product, it is quite a shame that Microsoft pushes the use of the DTA through the certification exam. In those questions, using the DTA is portrayed is a best practice.
You're right. It is a shame that they push it.
The problem is, they need something. SQL Server is not as self-tuning as it used to be advertised (and as most people think of it). Lots of businesses don't have a tuning expert in-house or on-call. So, they built a tool that takes statistical information, similar to the stuff that is used to identify missing indexes and missing statistics, and make it into an engine that spits out indexes. But, just as some statistics are off and some of the missing index information is off, the DTA is off. It's an imperfect solution to a very difficult problem. For the foreseeable future, the best answer is, get someone in who can tune your structures and your queries. But, best isn't always possible. A very distant, second-best, is the DTA.
"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
January 13, 2011 at 6:18 am
Koen (da-zero) (1/13/2011)
If it is such a crappy product, it is quite a shame that Microsoft pushes the use of the DTA through the certification exam. In those questions, using the DTA is portrayed is a best practice.
You could say the same applies to a number of other areas, my personnel bug bear is XML.
Heres some fairly recent advice on XML performance from MSoft.
Again , dont use it is the best advice!
Im yet to find a reason for the existence , other than laziness, for XML indexing.
January 13, 2011 at 6:27 am
Dave Ballantyne (1/13/2011)
Koen (da-zero) (1/13/2011)
If it is such a crappy product, it is quite a shame that Microsoft pushes the use of the DTA through the certification exam. In those questions, using the DTA is portrayed is a best practice.You could say the same applies to a number of other areas, my personnel bug bear is XML.
Heres some fairly recent advice on XML performance from MSoft.
Again , dont use it is the best advice!
Im yet to find a reason for the existence , other than laziness, for XML indexing.
You are absolutely right.
And yet again, using XML in SQL Server is quite a substantial portion of the certification exam.
(especially XQuery, baaaarrfff)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply