␡
- Introduction
- Analyzing Memory Usage
- Analyzing Processor Usage
- Analyzing Disk Usage
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Analyzing Processor Usage
Processor usage is very important to SQL Server because the more processor time available to execute instructions at any given time, the faster SQL Server can perform necessary tasks. Several factors can affect processor usage, from poorly designed databases and queries to increased disk usage. The following counters are good for checking up on the processor(s):
- System: Processor Queue Length. This counter is good for clueing into processor strain. To come up with a good queue number for your benchmark, multiple the number of processors on your server by two. For example, if your server has two processors, a good queue length should not exceed four; if your processor queue length is consistently higher than four on a system with two processors, your CPU is bottlenecking, causing increased query-execution time. To remedy this problem, you'll have to tune queries, reduce paging, or come up with better indexes. If these steps don't improve queue length, it may be time to add another processor system.
- System: Context Switches/Sec. SQL Server uses threads to execute batches of SQL statements from clients. If this value is high, SQL Server and Windows are switching many times per second from executing on one thread to executing on another, increasing CPU time and bogging down the system. If this value is high, it usually also means that the queue length (see bullet above) is also high. To optimize threading and increase processor performance, try setting SQL Server to use lightweight pooling. Doing so will make SQL Server use fibers instead of threads. Fibers use fewer resources; when pooled, they also allow SQL Server to optimize processing time when executing SQL statements concurrently. Lightweight pooling is an advanced option and can be set to 1 (to turn it on) using the sp_configure system-stored procedure.
- Processor: %User Time and Processor: %Privileged Time. Viewing these two counters together is good for indicating unnecessary processor strain by handling excessive I/O requests to the disk subsystem. If the %Privileged Time is consistently over 20% and %User Time is consistently below 80%, you have excessive I/O requests. Check your disk counters to confirm this problem, and get your I/O requests to the disk subsystem down to a reasonable level (see the next section).