The SQL Server Physical I/O chart displays the rate at which pages are physically read from and written to disk by SQL Server. The read and write operations are carried out by either SQL Server processes or system processes.
The following list presents the main physical I/O types shown in the chart:
• |
Checkpoint Writes are the most common type of write activity under normal circumstances. The checkpoint process periodically scans the buffer cache for modified pages and flushes all modified pages out to disk, thereby minimizing the amount of work SQL Server is required to do on restart. |
• |
Lazy Writer Writes are carried out when the Lazy Writer process needs to free up buffer pages that have been modified in the buffer cache. Freeing up the buffers requires the Lazy Writer process to write first the changed pages to disk. |
• |
Physical Page Reads are carried out when a user connection requests a page that is not already in the buffer cache. The connection requesting the page awaits until the I/O operation completes. |
• |
Physical Page Writes are write operations where the user connection has to wait for the I/O to complete before continuing. These are most often caused by operations such as create index, bulk insert, or restore. |
• |
Read Ahead occurs when SQL Server forecasts the need for data that currently resides on the disk. In this case, the pages are pre-fetched into the buffer cache before being requested by the user, using Read Ahead processing. |
The SQL Server Logical I/O chart displays the number of Logical reads (getpage requests) issued by SQL Server.
The Disk Queue Length chart displays the disk activity for each logical disk.
The Access Methods chart allows viewing various statistics used for monitoring the methods used for accessing and updating SQL Server data. These counters are all collected from the SQL Server Access Methods Manager.
The rate at which SQL Server is retrieving forwarded records. When a row in a table that does not have a clustering index is updated - and if the modified row no longer fits on the old page - SQL Server moves the row to a new page and leaves a forwarding pointer in the old page. | |
The rate at which SQL Server encounters ghosted records during scans. When a record is deleted from a table, SQL Server improves concurrency by not physically removing the row from index Leaf levels, but marking it instead as deleted (ghosted). | |
The Sessions panel is divided into 3 views:
The resource consumption section is divided into 2 time frames, Current and Last Hour.
• |
Sessions — Displays the distribution of the currently opened SQL Server sessions between active (sessions that are actively processing in SQL Server) and inactive sessions during the current sampling period. |
• |
Throughput — Divided to 3 metrics which represent the throughput of the monitored instance: |
• |
Blocked — Displays the total number of SQL Server sessions that are blocked during the current sampling period. |
• |
CPU — The Host CPU Usage the total percentage of CPU resources being used on the monitored host. The displayed value represents the total CPU that is consumed by all Windows processes (SQL Server and non-SQL Server processes). |
The Last 1 hour trend presents the activity on the instance during the last 1 Hour by presenting:
• |
By clicking the All Wait Events link at the upper right corner of the section, The Active Wait Events pop-up appears, with the Resource drop-down list unfiltered and displaying the entire list of wait events
Configuring SQL PI presents in-depth analysis of the session details. See Reviewing Session details with SQL PI
Session Process ID; the unique number that SQL Server has assigned to identify the selected session. | |
The name of the program the user is running to access SQL Server. | |
The Workload page displays the selected session workload (Seconds/Sec) during the specified time range (default 1 Hr.). The breakdown displays the workload, distributed by the various wait event categories. The colors of the categories match the colors of the various resources displayed in the Resource Breakdown section. By clicking the All Wait Events link at the upper right corner of the section, The Active Wait Events pop-up appears, with the Resource drop-down list unfiltered and displaying the entire list of wait events.
Displays the sum amount f CPU (in seconds) that the selected session is consuming.
Displays the sum amount of IO (in pages) that the selected session is consuming.
The Session Details section contains several tabs of information.
Additional columns are displayed in the Metrics table when SQL PI is configured. See .
The number of logical disk reads performed by the server on behalf of the event. | |
The number of physical disk writes performed by the server on behalf of the event. | |
The Locks table displays information about all locks currently held or requested.
The status of the lock: Blocked, Blocking, or blank (Granted) |
Configuring SQL PI enhances the Current session details by adding the next enhancements:
Displays the same data as described in the Reviewing Session details with SQL PI .
The Locks panel displays information about all locks and latches in the currently monitored SQL Server instance.The information is provided by the panes described in the following topics:
The Locks table displays information about all locks currently held or requested.
To configure the default retrieval settings for this panel, use the Lock view in the Databases Administration dashboard. For details, see Setting Options for Displaying Data in the Locks Panel .
The type of the resource that is currently locked (Database, Table, Page, Row, Extent, and so on). | |
The status of the lock: Blocked, Blocking, or blank (Granted). | |
The Lock Types chart shows lock statistics broken down by the various types of locks available in SQL Server.
This chart displays one line for each of the following lock types:
Use the list on the chart title to select which of the following lock types to display:
• |
Waits — the rate of lock request wait events. Such wait events take place when lock requests cannot be satisfied immediately and require the caller to wait before being granted the lock. |
• |
Average Wait Time — the average time (in seconds) that elapses before a lock request wait is cleared. |
• |
Lock Requests — the number of lock requests and lock conversion requests per second. |
• |
Timeouts — the number of lock time-outs per second. |
• |
Deadlocks — the number of lock requests per second that resulted in a deadlock. |
The Latches chart displays statistics on latch requests.
This chart shows the following series of data:
• |
Latch Waits — how many wait events for latches occurred in the specified time range |
• |
Total Wait Time — the total amount of time (in milliseconds) that latch requests spent waiting during the specified time range |
A high rate of latch wait events per second may indicate a slow disk I/O subsystem.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Termini di utilizzo Privacy Cookie Preference Center