The Resources tab of the single instance dashboard contains several views such as CPU, Memory, Disk, and Server Waits. The Resources tab allows you to monitor the system resources on the computer hosting the SQL Server instance as well as what is used by SQL Server. 

SQL Workload Analysis

 You can launch SQL Workload Analysis from the SQLDM web console single instance view and its tabs. The icon Launch SWA located on the top-right side of the screen is available when your instance is registered in SQL Workload Analysis product.

Resource bottlenecks are often the cause of SQL Server performance problems. These bottlenecks can result from poor database application design or point to other system processes using valuable resources needed by SQL Server.

CPU view

The Resources > CPU tab contains several charts and a Top sessions by active CPU widget (Click image to view full size).

Charts

Processor Time (%)

The Processor Time chart displays the percentage of CPU time used by all processes on the computer hosting the SQL Server instance.

Processor Queue Length

The Processor Queue Length chart displays the number of ready threads in the processor queue on the computer hosting the SQL Server instance.

CPU Usage (%)

The CPU Usage (%) chart displays the percentage of processing power in use on the computer that hosts the SQL Server instance over a period of time. You can select that information displays for the last 30 minutes, 1 hour, 4 hours, 8 hours, and 12 hours.

Top Sessions by Active CPU

This widget allows you to view the top sessions of a selected SQL Server instance per CPU usage. You can configure this widget to display additional entries by clicking the gear icon or you can expand/contract by clicking .


Memory view

The Resources > Memory tab contains the following charts (Click image to view full size).

Charts

SQL Memory Usage (MB)

This chart displays the SQL memory usage (SQL Used) compared to what is allocated (SQL Allocated) and the total used (Total Used).

Memory Areas (MB)

This chart displays the memory usage of the Procedure Cache, Connections, Locks, and Database.

Page Life Expectancy (sec)

This chart provides the time in seconds that a page remains in the cache or buffer pool.

Cache Hit Ratios (%)

This chart displays the hit ratios of the Buffer and Procedure cache.

Disk view

The Resources > Disk tab contains several charts and the Top sessions by I/O activity widget (Click image to view full size).


Charts

Disk Reads/Second Per Disk

This chart displays the number of reads per disk per second during a sample interval. You can select intervals of 30 minutes, 1 hour, 4 hours, 8 hours, and 12 hours.

Disk Writes/Second Per Disk

This chart displays the number of writes per disk per second during a sample interval. You can select intervals of 30 minutes, 1 hour, 4 hours, 8 hours, and 12 hours.

Disk Transfers Per Second

This chart displays the number of disk transfers per second during a sample interval. You can select intervals of 30 minutes, 1 hour, 4 hours, 8 hours, and 12 hours.

SQL Server Physical I/O

This chart displays the number of read and write requests sent to the physical disk the SQL Server instance resides in. Read and write requests are classified in Checkpoint Writers, Read Ahead Reads, Page Writes, Lazy Writer Writes, and Page Reads.

Top Sessions by I/O Activity 

This widget allows you to view the top sessions of a selected SQL Server instance per I/O Activity. You can configure this widget to display additional entries by clicking the gear icon or you can expand/contract by clicking


Server Waits view

The Resources > Server Waits tab contains a chart that display information on all waits occurring on your system and show the time different wait types occurred and the length of time (in milliseconds) it took for the waits to clear. Click image to view full size.

Wait categories in the drop-down menu

Total Waits

Total waits include all the types of waits collected.

Signal Waits

Signal waits occur when a resource is now available (after a resource wait) and the task is waiting to run again.

Resource Waits

Resource waits occur when access to a resource is denied because the resource is in use or not available.

Server Waits panel

Below the charts, the Server Waits panel includes information on the wait category, wait type, length of time the wait took to clear, total wait time for all the collected wait types, and a definition of the wait type.

Available wait types

The SQL Diagnostic Manager web console displays information on the following wait types: I/O, Memory, Non-I/O Page Latch, Non-Page Latch, Transaction Log, and Other categories.

For additional information on Server Waits metrics, see Server Waits Panel.

Access the Resources tab

SQL Diagnostic Manager provides two paths to access the Resources tab. The first access path is by selecting the Resources tab of the single instance dashboard. You can also click the resources category icon of a specific instance in the thumbnail sub-view.


SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >