What are the best practices to avoid CPU load in SQL Server?
Having optimal query plans should keep your CPU usage low.|||>>optimal query plans ?
hmmm.Does not mean no Joins? no triggers? no Unions?
which one is the worst?
|||It is hard to say one way or the other. It depends on the query plan, amount of data and the problem you are trying to solve for example. It is too open ended of a question to suggest specific things to do. Troubleshooting performance problems generally involves many factors and in case of SQL Server the interaction of the various systems (OS, disk, memory, sql server engine) makes it more complicated. You can start by setting MAXDOP to optimal value so you don't have parallel queries consuming lot of valuable resources on the server or disable parallelism for queries altogether. You can find a post on how to set MAXDOP at:
http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
You can also start with the whitepaper below on query stats which also has a link to the recompilation whitepaper which will help you to understand the issues with plan caching, query stats more.
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
Another link is the troubleshooting performance problems whitepaper. You can find it at:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
And the SQL Server 2005 Performance Center in MSDN.
No comments:
Post a Comment