The Resources Summary tab displays real-time charts that allow you to quickly view key diagnostic statistics for monitoring the resources for the selected SQL Server instance and computer on which it resides. The Resources Summary tab is especially useful in determining resource bottlenecks. For more details, click any of the charts to navigate to the associated tab.
Access the Resources Summary view
You can open the Summary view of the SQL Diagnostic Manager Resources tab, select the appropriate SQL Server instance, select Resources, and click Summary.
Use the CPU Usage chart
The CPU Usage chart helps you determine the amount of strain your SQL Server instance is putting upon the CPU. This is an important factor in analyzing poorly-performing SQL Server instances. Click this chart to open the CPU, which contains more charts and information on your CPU resources.
SQL Server is the primary process consuming CPU on the computer hosting your instance. If this is the case and the total CPU usage is consistently higher than 80 percent, the computer requires your attention. High usage may indicate the need to upgrade your CPU or add more processors.
Use the Call Rates chart
The Call Rates chart helps you determine why you have high CPU usage. The Call Rates chart displays the following metric statistics:
Batches
Displays the rate at which SQL Server issues SQL Statement batches.
Compiles
Displays the rate at which SQL Server compiles SQL Statements into the Procedure Cache.
Re-Compiles
Displays the rate at which SQL Server recompiles stored SQL Statements into the Procedure Cache.
If the number of re-compiles is consistently higher than 10, or the number of compiles is high, you may need to tune your queries. Consider running the Query Monitor as a way of diagnosing query performance issues. You can enable the Query Monitor on the Query Monitor tab of the Monitored SQL Server Properties window.
Transactions
Displays the number of SQL Statement transactions affecting the Procedure Cache.
Use the Memory Usage chart
The Memory Usage chart allows you to analyze the way in which your SQL Server instance uses memory. The chart provides the amount of memory allocated to SQL Server, the memory currently used by SQL Server, and the total amount used on the host computer.
If the SQL Used metric is consistently high compared to the amount of physical memory on the computer where the SQL Server instance resides, it may indicate that you need more physical memory.
Use the Cache Hit Ratios chart
The Cache Hit Ratios chart allows you to view how often a request is serviced by the buffer and procedure cache.
An acceptable Buffer Cache and Procedure Cache percentage is 90 percent or higher. If this number is consistently lower, analyze your memory settings and add more memory if necessary.
Use the Disk Busy chart
Use the Disk Busy chart to analyze bottlenecks in your file system.
If your Disk Busy is consistently over 90 percent you may need to use a faster disk drive, move files to an additional disk or server, or add disks to a RAID array to improve your SQL Server performance.
Use the SQL Server Physical I/O chart
The SQL Server Physical I/O chart allows you to view the number of pages read from, and written to, the physical disk.
The SQL Server Physical I/O chart displays the following reads and writes:
Checkpoint Writes
Save time during recovery by creating a point at which all dirty pages are guaranteed written to disk.
Lazy Writer Writes
Create more available buffers for the system by clearing all unnecessary buffers.
Page Reads
Are disk writes where the requesting session must wait for the disk write operation to finish.
Page Writes
Are disks writes where the requesting session must wait for certain operations to finish.
If the number of reads and writes approaches the limits of the I/O capacity of the computer the SQL Server instance resides, consider reducing the values by tuning your application or database, increasing the I/O capacity of your hardware, or adding memory.
Read Ahead Reads
Are part of a process in which SQL Server predicts what pages a session requires next and writes them to memory in advance.
Understanding your chart data and refresh interval
SQL Diagnostic Manager charts provide data whether collected real-time or as part of a scheduled collection event. For additional information about how SQL Diagnostic Manager displays your chart data, see Charts.