SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

How to Identify CPU Bottlenecks in SQL Server Using Performance Counters

here are instance when you are facing CPU bottlenecks on your SQL Server Instance. In this article we will take look at How to Identify CPU Bottlenecks in SQL Server Using Performance Counters.The most common reason for a CPU bottleneck will be insufficient hardware. However, as a database administrator one can make certain configuration changes and tune the SQL Server queries to improve the performance. To begin with Performance Monitor is a great tool which can be used to quickly identify CPU bottlenecks on your SQL Server Instance. Monitoring the below counters can help you in Performance Tuning of SQL Server.

Most important CPU counters to be looked at are: –

Processor: % Processor Time

This counter gives you information with respect to how much time the processor has actually spent working on productive threads and how often it was busy serving such requests. If you find the value of this counter hovering above 80% per CPU then this gives a clear indication that the server is clearly facing CPU bottlenecks.

System: Processor Queue Length

This counter gives you information with respect to the number of threads in the processor queue. This counter basically displays the ready threads; it doesn’t count the threads which are running. Even if you have multiple processors on the system this counter will display a cumulative value. Hence if you have 5 processors on a system and the value for the system turns out to be 60 then it mean on an average the Processor Queue Length for a single processor is 12. Ideally the average processor queue length should be less than 10.

System: % Total Processor Time

This counter gives you information with respect to the total performance of the entire system i.e. it is an average of all the processors together on the system.

SQL Server: SQL Statistics: Batch Requests/sec

This counter gives you information with respect to the number for TSQL command batches received per second.

SQL Server: SQL Statistics: SQL Compilations/sec

This counter gives you information with respect to number of SQL Compilations happening per second.

SQL Server: SQL Statistics: SQL Re-Compilations/sec

This counter gives you information with respect to number of TSQL statement recompiles per second. This counter has the count of number of times statement recompile is triggered by the database engine. This counter value should be very low. Ideally SQL Statistics: SQL Re-Compilations/sec value should not be more than one percent of SQL Statistics: Batch Requests/sec.

For example if your system has 1000 SQL Statistics: Batch Requests/sec then the value of SQL Statistics: SQL Re-Compilations/sec shouldn’t exceed 10 (> 1%) after the SQL Server is stable and its up and running for more than 24 hours.

SQL Server: Cursor Manager By Type: Cursor Requests/Sec

This counter gives you information with respect to number of SQL cursor requests received by server. If you end up seeing 100 of cursor requests per sec then it’s a worrying factor. This means that the code needs to be rewritten to avoid as much cursor usage as possible to improve the overall performance.

Reference: SQL Server, SQL Statistics Objects

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Add comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning