OS View (SQL Server Performance Analyst)

From DBArtisan
Jump to: navigation, search

Go Up to Microsoft SQL Server Performance Analyst Statistics

In many scenarios, an optimally tuned database may not perform well because there are constraints imposed by the system where the database is running. These constraints may include processes competing with the database sever for resources (CPU, I/O, or Memory), a slow CPU, insufficient or slow I/O devices, and insufficient memory. The OS Statistics page of Performance Analyst lets you examine operating system metrics for the following platforms:

  • AIX
  • HP-UX
Note: To view processor info and swap disk info on an HP-UX box, you need to login as ROOT in the OS login.
  • Linux
  • Solaris
  • Unix
  • Windows XP and 2000
Note: The statistics available on the OS page depend on the operating system platform.
Tip: If you magnify a graph to change back to the original size or minimize the size, close the OS Page and then reopen it again. Performance Analyst displays the graph in its original form and size.

In addition to a Home page, the OS category of SQL Server Performance Analyst includes the following tabbed pages:

Contents

Home

The OS home page includes the following sections:

Key Resource Usage Pane

The following ratios are used on the Performance Analyst OS home page to communicate the general overall performance levels of the operating system:

Tip: To open the CPU tab, right-click Processor Time. To open the I/O tab, right-click Disk Time. To open the Memory tab, right-click Paged Memory Used.

Disk Time

The Disk Time statistic is the percentage of elapsed time that the selected disk drive/device was busy servicing read or write requests.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Key Resource Usage Pane

Metrics

You should avoid consistently seeing values for this statistic greater then 90%.

Correction

Add more disk drives and partition the files among all of the drives.

Load Average

The Load Average statistic represents the system load averages over the last 1, 5, and 15 minutes.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Key Resource Usage Pane

Metrics

High load averages usually mean that the system is being used heavily and response time is correspondingly slow.

Processor Time

The Processor Time statistic indicates the percentage of time the processor is working. This counter is a primary indicator of processor activity.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Key Resource Usage Pane

Metrics

If your computer seems to be running sluggishly, this statistic could be displaying a high percentage.

Correction

Upgrade to a processor with a larger L2 cache, a faster processor, or install an additional processor.

Paged Memory Used

The Paged Memory Used statistic is the ratio of Commit Memory Bytes to the Commit Limit. Committed memory is where memory space has been reserved in the paging file if it needs to be written to disk. The commit limit is determined by the size of the paging file. As the paging file increases, so does the commit limit.

Note: This statistic is available for the Windows platform.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Key Resource Usage Pane

Metrics

This value displays the current percentage value only and not an average. If the percentage of paged memory used is above 90%, you may be running out of memory.

Correction

Increase the size of page file.

Swap Memory Used

The Swap Memory Used statistic is the percentage of swap space currently in use.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Key Resource Usage Pane

Metrics

If the percentage of swap memory used is above 90%, you may be running out of memory.

Correction

Increase the size of your swap files.

Bottleneck Analysis Pane

The following ratios are used on the Performance Analyst OS home page to succinctly communicate the general overall performance levels of the operating system:

Tip: To open the I/O tab, right-click any Details menu item. To open the CPU tab, right-click the Item Processor Queues Length Details menu item. To open the Network tab, right-click Network Output Queues Length.
Note: The statistics available in this section depend on the platform of operating system.

Average Disk Queue Length

The Average Disk Queue Length statistic is the average number of both read and write requests that were queued for the selected disk during the sample interval.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

This metric is useful in identifying I/O related bottlenecks. If the disk queue lengths for certain disks are consistently much higher than others, you may need to redistribute the load among available disks. If the disk queues lengths for all disks are consistently large, and you see a high amount of I/O activity, your disks may be inefficient.

Correction

Some things you can do if you have problems with this statistic include: Redistribute the data on the disk with the large average disk queue to other disks. Upgrade to faster disk(s).

Interrupts/Sec

Interrupts/Sec is the average rate, in incidents per second, at which the processor received and serviced hardware interrupts. This value is an indirect indicator of the activity of devices that generate interrupts, such as the system clock, the mouse, disk drivers, data communication lines, network interface cards, and other peripheral devices. These devices normally interrupt the processor when they have completed a task or require attention. Normal thread execution is suspended. The system clock typically interrupts the processor every 10 milliseconds, creating a background of interrupt activity. This statistic shows the difference between the values observed in the last two samples, divided by the duration of the sample interval.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > CPU Tab > CPU Utilization

Metrics

A high value indicates possible excessive hardware interrupts; justification is dependent on device activity.

Network Output Queue Length/Network Queue Length

The Network Output Queue Length statistic is the number of threads in the processor queue.

Note: The name of this statistic depends on the platform of the operating system.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Unlike the disk counters, this counter shows ready threads only, not threads that are running. There is a single queue for processor time even on computers with multiple processors. Therefore, if a computer has multiple processors, you need to divide this value by the number of processors servicing the workload. A sustained processor queue of less than 10 threads per processor is normally acceptable, dependent of the workload.

Correction

A sustained high value in the Processor Queue Length could indicate that a processor bottleneck has developed due to threads of a process requiring more process cycles than are available. If this is the case, you should look at installing a faster (or an additional) processor.

Page Faults/Sec

The Page Faults/Sec statistic is the overall rate faulted pages are handled by the processor. It is measured in numbers of pages faulted per second. A page fault occurs when a process requires code or data that is not in its working set. This counter includes both hard faults and soft faults.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Memory Tab

Metrics

This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.

Correction

If the number of page faults remains consistently high, you can check with your Windows System Administrator for further investigation. Often, large numbers of page faults are not a problem so long as they are soft faults. However, hard faults, that require disk access, can cause delays.

Processor Queue Length

The Processor Queue Length statistic is the number of threads in the processor queue.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > CPU Tab

Metrics

Unlike the disk counters, this counter shows ready threads only, not threads that are running. There is a single queue for processor time even on computers with multiple processors. Therefore, if a computer has multiple processors, you need to divide this value by the number of processors servicing the workload. A sustained processor queue of less than 10 threads per processor is normally acceptable, dependent of the workload.

Correction

A sustained high value in the Processor Queue Length could indicate that a processor bottleneck has developed due to threads of a process requiring more process cycles than are available. If this is the case, you should look at installing a faster (or an additional) processor.

Processor Speed

The Processor Speed statistic displays the speed of the active processor in MHz. The speed is approximate.

Processor

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

The Processor Statistic displays the type of processor currently in use, for example, GenuineIntel.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Memory Analysis Pane

The following metrics are used on the Performance Analyst OS home page to succinctly communicate the general overall performance levels of the operating system:

Tip: To open the Memory tab, right-click any Details menu item.
Note: The statistics available in this section depend on the platform of operating system.

Available Paged Memory

The Available Paged Memory statistic shows the amount of virtual memory available for the processes.

Note: This statistic is available for the Windows platform.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Memory Analysis Pane

Metrics

If the available virtual memory is less than 10% of the total virtual memory, your system may run out of memory.

Correction

Increase the size of page file.

Available Physical Memory

The Available Physical Memory statistic represents the amount of RAM available to all processes.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Memory Analysis Pane

Metrics

This counter displays the last observed value only and not an average. Use this value with the Total physical memory and paging metrics (Memory details page). If the available physical memory is very small compared to this value, and the paging activity is high, your system may be running low on memory.

Correction

Some things you can do if you have problems with this statistic include: Check the running processes to see if there are any memory leaks. Stop any services that are not required. Install additional RAM.

Available Swap Memory

The Available Swap Memory statistic represents the amount of virtual memory available for the processes.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Memory Analysis Pane

Metrics

If the available Available Swap Memory is less than 10% of total Swap Memory, your system may run out of memory.

Correction

Increase the size of swap files.

Total Paged Memory/Total Swap Memory

The Total Paged Memory statistic shows the maximum amount of virtual memory available to all processes.

Note: The name of this statistic depends on the platform of the operating system.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Memory Analysis Pane

Metrics

It is recommended that this be 1½ - 3 times the amount of RAM on the system.

Total Physical Memory

The Total Physical Memory statistic shows the amount of physical memory installed on your computer.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Memory Analysis Pane

Metrics

This is an informational metric and displays the total amount installed on the machine. Use this value with the available physical memory and paging metrics (Memory details page). If the available physical memory is very small compared to this value, and the paging activity is high, your system may be running low on memory.

Disk Analysis Pane

The following ratios are used on the Performance Analyst OS home page to succinctly communicate the general overall performance levels of the operating system:

Tip: To open the Space tab, right-click any Details menu item.
Note: The statistics available in this section depend on the platform of operating system.

Free Disk Space

The Free Disk Space statistic shows the unallocated space, in megabytes on all logical disk drives.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Disk Analysis Pane

Correction

There are many things you can do to ensure that a database does not encounter a space problem due to physical space limitations:

If a database currently resides on a disk that has little free space, you can add more files to the database. Of course, you should add the new files to other physical hard disks that can accommodate a growing database. You should examine hard disks with shrinking disk space to see if you can relocate or delete files to allow more free space.

Total Disk Space

Total Disk Space displays the total allocated and unallocated space, in megabytes on all logical disk drives.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Disk Analysis Pane

Correction

There are many things a DBA can do to ensure that a database does not encounter a space problem due to physical space limitations, here are two: If a database currently resides on a disk that has little free space, you can add more files to the database. Of course, you should add the new files to other physical hard disks that can accommodate a growing database. You should examine hard disks with shrinking disk space to see if you can relocate or delete files to allow more free space.

Used Disk Space

The Used Disk Space statistic shows the amount of allocated space, in megabytes on all logical disk drives.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Disk Analysis Pane

Correction

There are many things a DBA can do to ensure that a database does not encounter a space problem due to physical space limitations:

If a database currently resides on a disk that has little free space, you can add more files to the database. Of course, you should add the new files to other physical hard disks that can accommodate a growing database. You should examine hard disks with shrinking disk space to see if you can relocate or delete files to allow more free space.

Workload Analysis Pane

The following ratios are used on the Performance Analyst OS home page to succinctly communicate the general overall performance levels of the operating system:


Tip: To open the Processes tab, right-click any Details menu item.
Note: The statistics available in this section depend on the platform of operating system.

Number of Logins

This statistic displays the total number of logins on the server.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Disk Analysis Pane

Number of Processes

This statistic displays the total number of processes on the server.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Disk Analysis Pane

Top Memory Process

Top Memory Process shows the current process that is consuming the most amount of memory. The information displayed is dependent on the platform of the operating system. Information displayed includes the name of the process, process ID, amount of memory consumed expressed in KB, amount of CPU expressed as a percentage, the amount of Major Page Faults, and the amount of I/O expressed in KB/sec.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Disk Analysis Pane

Metrics

If you are running out of memory on the system, this is a quick way to identify the top memory user. If the displayed process is using a significant portion of the total memory, it could be causing the memory issues.

Top CPU Process

Top CPU Process shows the current process that is consuming the most amount of CPU. The information displayed is dependent on the platform of the operating system. Information displayed includes the name of the process, process ID, amount of memory consumed expressed in KB, amount of CPU expressed as a percentage, the amount of Major Page Faults, and the amount of I/O expressed in KB/sec.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Disk Analysis Pane

Metrics

If the amount of CPU time used by this process is close to 100% and the CPU usage is very high, this process may be the bottleneck on the server.

Correction

Investigate the process further to see if it is in an inconsistent state. Also, look at minimum requirements for CPU speed for the process. You may need to upgrade your CPU.

Top I/O Process

The Top I/O Process statistic shows the current process that is consuming the most amount of CPU. The information displayed is dependent on the platform of the operating system. Information displayed includes the name of the process, process ID, amount of memory consumed expressed in KB, amount of CPU expressed as a percentage, the amount of Major Page Faults, and the amount of I/O expressed in KB/sec.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Home > Disk Analysis Pane

CPU Tab

The CPU tab of the OS Detail includes the following sections:

The following statistics, available on this tab, duplicate statistics available on the Bottleneck Analysis Pane of the OS View (DBArtisan - SQL Server Performance Analyst) Home page:

Note: The sections available on this tab depend on the platform of operating system.

CPU Utilization

The CPU Utilization section includes the following information:

% Privileged Time

This metric is the percentage of elapsed time that the process threads spent executing code in privileged mode.

Note: For Windows systems, when a Windows system service is called, the service will often run in privileged mode to gain access to system-private data. Such data is protected from access by threads executing in user mode. Calls to the system can be explicit or implicit, such as page faults or interrupts. These kernel commands, are considered privileged to keep the low-level commands executing and prevent a system freeze. Unlike some early operating systems, Windows uses process boundaries for subsystem protection in addition to the traditional protection of user and privileged modes. Some work done by Windows on behalf of the application might appear in other subsystem processes in addition to the privileged time in the process.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > CPU Tab

Metrics

The ideal range should be 0-40% (less than 40% indicates excessive system activity).

Correction

If your CPU consistently runs at less than 40% you may need to upgrade your system to include a faster processor(s).

% User Time

The % User Time statistic is the percentage of elapsed time the processor spends in the user mode. User mode is a restricted processing mode designed for applications, environment subsystems, and integral subsystems. The alternative, privileged mode, is designed for operating system components and allows direct access to hardware and all memory. The operating system switches application threads to privileged mode to access operating system services. This counter displays the average busy time as a percentage of the sample time.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > CPU Tab

Metrics

If the Privileged Time is high in conjunction with Physical Disk Reads, consider upgrading the disk I/O subsystem.

CPU Events

The CPU Events section includes the following information:

Note: The statistics available in this section depend on the platform of operating system.

Context Switches/Sec

The Context Switches/Sec section shows the combined rate at which all processors on the computer are switched from one thread to another. Context switches occur when a running thread voluntarily relinquishes the processor, is preempted by a higher priority ready thread, or switches between user-mode and privileged (kernel) mode to use an Executive or subsystem service.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > CPU Tab

Metrics

The ideal range should be between 0-10,000. GA number greater then 10,000 may indicate too many threads contending for resources.

System Calls/Sec

System calls are functions which require the services of the operating system. Usually they involve access to some data that users must not be permitted to corrupt or even change.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > CPU Tab

Correction

Try to find a good ratio between the amount of time the operating system runs (fulfilling system calls and doing process scheduling), and the amount of time the application runs. Your goal should be running 60% to 75% of the time in application mode, and 25% to 40% of the time in operating system mode. If you find that the system is spending 50% of its time in each mode, then you should investigate to determine what is wrong.

Load Averages

The Load Average statistic represents the system load averages over the last 1, 5, and 15 minutes.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > CPU Tab

Metrics

High load averages usually mean that the system is being used heavily and the response time is correspondingly slow.

Processes Tab

The Processes tab of the OS Detail page succinctly communicates the general overall performance levels of processes. The columns available in this table depend on the platform of operating system. The table below describes the information available in the table on this tab:

Column Description

Process

The name of the process.

User

The user of the process.

ID

The ID Process is the unique identifier of this process. ID Process numbers are reused, so they only identify a process for the lifetime of that process.

CPU

The CPU is the percentage of elapsed time that all of process threads used the processor to execution instructions.

User Mode

The User Mode is the percentage of elapsed time that the process threads spent executing code in user mode.

Memory WINDOWS ONLY

Memory is the current size, in bytes, of the virtual address space the process is using. Use of virtual address space does not necessarily imply corresponding use of either disk or main memory pages. Virtual space is finite, and the process can limit its ability to load libraries.

Memory (MB)

Memory is the current size, in bytes, of the virtual address space the process is using. Use of virtual address space does not necessarily imply corresponding use of either disk or main memory pages. Virtual space is finite, and the process can limit its ability to load libraries.

Memory

Memory is the percentage of the memory used of the total memory.

Active Memory

Active Memory is the amount of committed virtual memory, in bytes for this process. Active memory is the physical memory which has space reserved on the disk paging file(s). There can be one or more paging files on each physical drive. This counter displays the last observed value only; it is not an average.

I/O Data

The rate at which the process is reading and writing bytes in I/O operations. This counter counts all I/O activity generated by the process to include file, network and device I/Os.

Elapsed Time

The total elapsed time, in seconds, that this process has been running.

Thread Count

The number of threads currently active in this process. An instruction is the basic unit of execution in a processor, and a thread is the object that executes instructions. Every running process has at least one thread.

Handle Count

The total number of handles currently open by this process. This number is equal to the sum of the handles currently open by each thread in this process.

Priority

The current base priority of this process. Threads within a process can raise and lower their own base priority relative to the process' base priority.

Creating Proc ID

The Creating Process ID value is the Process ID of the process that created the process. The creating process may have terminated, so this value may no longer identify a running process.

Page Faults/Sec

Page Faults/Sec is the rate at which page faults by the threads executing in this process are occurring. A page fault occurs when a thread refers to a virtual memory page that is not in its working set in main memory. This may not cause the page to be fetched from disk if it is on the standby list and hence already in main memory, or if it is in use by another process with whom the page is shared.

Page File

Page File is the current number of kilobytes that this process has used in the paging file(s). Paging files are used to store pages of memory used by the process that are not contained in other files. Paging files are shared by all processes, and the lack of space in paging files can prevent other processes from allocating memory.

Private

Private is the current size, in kilobytes, of memory that this process has allocated that cannot be shared with other processes.

I/O Tab

The I/O tab includes the following sections:

Note: The sections available on this tab depend on the platform of operating system.

I/O Details

The table below describes the information available in this section:

Column Description

Disk

The disk number assignment.

Reading (KB/s)

The amount of bytes read from the device.

Writing (KB/s)

The amount of bytes written to the device.

Disk Read Time

Disk Read Time is the percentage of elapsed time that the selected disk drive was busy servicing read requests.

Disk Write Time

Disk Write Time is the percentage of elapsed time that the selected disk drive was busy servicing write requests.

Disk Time

Disk Time is the percentage of elapsed time that the selected disk was busy servicing requests.

Avg. Read Queue

Avg. Disk Read Queue Length is the average number of read requests that were queued for the selected disk during the sample interval.

Avg. Write Queue

Avg. Disk Write Queue Length is the average number of write requests that were queued for the selected disk during the sample interval.

Disk Reads/Sec

Disk Reads/Sec is the rate of read operations on the disk.

Disk Writes/Sec

Disk Writes/Sec is the rate of write operations on the disk.

Note: The columns available in this table depend on the platform of operating system.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > I/O Tab

Transfer Rate

The Transfer Rate section succinctly communicates the general overall performance levels of the server's I/O. The table below describes the statistics in this section:

Statistic Description Metrics

Reading

The average number of bytes transferred from the disk during read operations.

It is measured in number of KBs per second.

Writing

The average number of bytes transferred to the disk during write operations.

It is measured in number of KBs per second.

Transferred

The amount of time required to transfer the data between the device's cache and the actual device media.

N/A

Note: The statistics available in this section depend on the platform of operating system.

LocBytes per I/O Operation

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > I/O Tab

The Bytes per I/O Operation section of the OS Detail succinctly communicates the general overall performance levels of the server's I/O. The table below describes the statistics in this section:

Statistic Description Metrics

Reading

The average number of bytes transferred from the disk during read operations.

It is measured in number of bytes per second.

Writing

The average number of bytes transferred to the disk during write operations.

It is measured in number of bytes per second.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > I/O Tab

Average Disk Queue/Total Disk Queue

The Average Disk Queue section of the OS Detail page succinctly communicates the general overall performance levels of the server's I/O. The table below describes the statistics in this section:

Statistic Description Information

Average Read Queue

Average Read Queue Length is the average number of read requests that were queued for a particular disk.

Sustained loads on any of the disk queue lengths could represent problems within your system.

Average Write Queue

Average Write Queue is the average number of write requests that were queued for a particular disk.

Sustained loads on any of the disk queue lengths could represent problems within your system. Contact your Windows System Administrator for assistance in Correction these problems.

Average Queue

Average time in ms. to service I/O requests which for disks includes seek, rotational latency and data transfer time).

Sustained loads on any of the disk queue lengths could represent problems within your system. Contact your Unix System Administrator for assistance in Correction these problems.

Note: The statistics available in this section depend on the platform of operating system.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > I/O Tab

Correction

Contact your Windows System Administrator for assistance in Correction these problems.

Disk I/O Time

The Disk I/O Time section of the OS Detail page succinctly communicates the general overall performance levels of the disk’s I/O. The table below describes the statistics in this section:

Note: The statistics available in this section depend on the platform of operating system.
Statistic Description Metrics

% Disk Read Time

% Disk Read Time is the percentage of time during the sample interval that the disk is busy servicing read requests.

Use this in conjunction with the % Disk Write Time to identify the disks that may be running at capacity and may require upgrades. This metric may also be used to balance the load between under/over utilized disks.

% Disk Write Time

% Disk Write Time is the percentage of elapsed time that the selected disk drive was busy servicing write requests.

Use this in conjunction with the % Disk Read Time to identify the disks that may be running at capacity and may require upgrades. This metric may also be used to balance the load between under/over utilized disks.

% Disk Time

% Disk Time is the percentage of time during the sample interval that the disk is busy servicing requests.

Use this statistic to identify the disks that may be running at capacity and may require upgrades. This metric may also be used to balance the load between under/over utilized disks.


Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > I/O Tab

Disk Transfers/Sec

The Disk Transfers/Sec section of the OS Detail page succinctly communicates the general overall performance levels of the disk transfers. The table below describes the statistics in this section

Statistic Description

Reads/Sec

The the rate of read operations on the disk.

Writes/Sec

The rate of write operations on the disk.

Transfers/Sec

The rate of transfer operations per second.

Note: The statistics available in this section depend on the platform of operating system.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > I/O Tab

Memory Tab

The Memory tab of the OS Detail page includes the following sections:

The following statistic, available on this pane, duplicates a statistic appearing on the Bottleneck Analysis Pane pf the OS View (DBArtisan - SQL Server Performance Analyst) Home page:

Note: The sections available on this tab depend on the platform of operating system.

Paging Activity

The Paging Activity section includes the following statistics:

Note: The statistics available in this section depend on the platform of operating system.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Blocks Input/Sec

The Blocks Input/sec statistic is the number of blocks sent to a block device per second.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Correction

Although it never hurts to have as much physical memory as your system can handle, here are two things you can check within your system to alleviate the memory bottleneck: Check to see if you have any drivers or protocols that are running but not being used. They use space in all memory pools even if they are idle. Check to see if you have additional space on your disk drive that you could use to expand the size of your swap file. Normally, the bigger the initial size of your swap file, the better, in performance terms.

Blocks Output/Sec

The Blocks Output/sec statistic is the number of blocks received from a block device per second).

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Correction

Although it never hurts to have as much physical memory as your system can handle, here are two things you can check within your system to alleviate the memory bottleneck. Check to see if you have any drivers or protocols that are running but not being used. They use space in all memory pools even if they are idle. Check to see if you have additional space on your disk drive that you could use to expand the size of your swap file. Normally, the bigger the initial size of your swap file, the better, in performance terms.

Paged In

The number of disk block paged into core from disk which occurs when the server is experiencing a shortage of RAM. Any nonzero value indicates excessive swap activity. In any virtual memory environment, it is not uncommon to see RAM pages moved out to a swap disk. This is a special disk area in UNIX that's reserved for holding memory pages so that the processor is capable of addressing RAM in excess of its full capability. While page-out of operations are a normal part of any server's operations, page-in operations indicate that the real amount of available RAM has been exceeded and that additional RAM pages are required on the server.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Correction

High values indicate the possible need to increase the amount of RAM on the system.

Paged Out

The number of disk block paged out of core from disk. In any virtual memory environment, it is not uncommon to see RAM pages moved out to a swap disk. This is a special disk area in UNIX that's reserved for holding memory pages so that the processor is capable of addressing RAM in excess of its full capability. While page-out of operations are a normal part of any server's operations, page-in operations indicate that the real amount of available RAM has been exceeded and that additional RAM pages are required on the server.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Pages Input/Sec

The Pages Input/Sec statistic is the number of pages read from disk to resolve hard page faults. Hard page faults occur when a process requires code or data that is not in its working set or elsewhere in physical memory, and must be retrieved from disk.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Metrics

This value was designed as a primary indicator of the kinds of faults that cause system-wide delays. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) and in non-cached mapped memory files. This counter counts numbers of pages, and can be compared to other counts of pages, such as Memory: Page Faults/sec, without conversion. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.

Correction

Although it never hurts to have as much physical memory as your system can handle, there are some things you can check within your system to alleviate the memory bottleneck. Check to see if you have any drivers or protocols that are running but not being used. They use space in all memory pools even if they are idle. Check to see if you have additional space on your disk drive that you could use to expand the size of your page file. Normally, the bigger the initial size of your page file, the better, in performance terms.

Pages Output/Sec

The Pages Output/Sec statistic is the number of pages written to disk to free up space in physical memory. Pages are written back to disk only if they are changed in physical memory. A high rate of pages output might indicate a memory shortage.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Metrics

Windows NT writes more pages back to disk to free up space when low in physical memory. This counter counts numbers of pages, and can be compared to other counts of pages, without conversion. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.

Correction

Although it never hurts to have as much physical memory as your system can handle, there are some things you can check within your system to alleviate the memory bottleneck. Check to see if you have any drivers or protocols that are running but not being used. They use space in all memory pools even if they are idle. Check to see if you have additional space on your disk drive that you could use to expand the size of your page file. Normally, the bigger the initial size of your page file, the better, in performance terms.

Memory Available

The Memory Available section of the OS Detail page succinctly communicates the general overall performance levels of the server's memory. The statistics available in this section depend on the platform of operating system. The table below describes these statistics:

Statistic Description Metrics Correction

Physical Memory

The Physical Memory statistic is the amount of committed virtual memory, in megabytes. Committed memory is where memory space has been reserved in the paging file if it needs to be written to disk.

This counter displays the last observed value only and not an average.

N/A

Physical

The Physical statistic is the total physical memory available.

N/A

N/A

Paged Memory

The Paged Memory statistic is the amount of committed virtual memory, in bytes. Used Memory is the physical memory which has space reserved on the disk paging file(s). There can be one or more paging files on each physical drive.

This counter displays the last observed value only; it is not an average.

N/A

Swap Memory

The Swap Memory statistic is the free, unreserved swap space.

N/A

Increase the size of your swap file or add additional files if you consistently see a shortage of free, unreserved swap space.

Swap

The Swap statistic is the total swap memory being used.

N/A

N/A

Total Physical

The Total Physical statistic is the total physical memory available.

N/A

N/A

Total Swap

The Total Swap statistic is the total swap memory available.

N/A

N/A

Swap-ins

The number of memory pages paged in from swapspace.

N/A

If the system is consistently low on free memory (over a 30 second average), the memory scheduler will start to swap out processes. Increase the size of your swap file or add additional files.

Swap-outs

The number of memory pages paged in from swapspace.

N/A

If the system is consistently low on free memory (over a 30 second average), the memory scheduler will start to swap out processes. Increase the size of your swap file or add additional files.

Buffers

The Buffers statistic represents the amount of buffers used by the kernel in kilobytes.

N/A

N/A

Page Faults/Sec

The Page Faults/Sec statistic is the overall rate faulted pages are handled by the processor. It is measured in numbers of pages faulted per second. A page fault occurs when a process requires code or data that is not in its working set. This counter includes both hard faults and soft faults.

This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.

If the number of page faults remains consistently high, you can check with your Windows System Administrator for further investigation. Often, large numbers of page faults are not a problem so long as they are soft faults. However, hard faults, that require disk access, can cause delays.


Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Buffer Size

The Buffer Size statistic represents the amount of cache used by the kernel in kilobytes.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Cache Size

The Cache Size statistic represents the amount of cache used by the kernel in kilobytes.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Page Replacements

The following statistics are available in this section:


Memory Freed (Pages/sec)

Freed or destroyed (Kb/s).

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Clock Algorithm Scans (Pages/sec)

The VMM uses a technique known as the clock algorithm to select pages to be replaced. This technique takes advantage of a referenced bit for each page as an indication of what pages have been recently used (referenced). When the page-stealer routine is called, it cycles through the PFT, examining each page's referenced bit.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Cache Efficiency

The Cache Efficiency section of the Memory tab succinctly communicates the general overall performance levels of the server's memory. The following statistics are available in this section:

Copy Read Hits %

The Copy Read Hits % statistic is the percentage of cache copy read requests that hit the cache and does not require a disk read to provide access to the page in the cache.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Metrics

When the page is pinned in the memory, the page's physical address in the file system cache will not be altered. A copy read is a file read operation where a page in the cache is copied to the application's buffer. Because this value reflects hits, it ideally should be close to 100%. An amount below 100% indicates misses.

Correction

Adding physical memory to a server results in a larger file system cache, which is generally more efficient. Defragmenting disks also helps, putting related pages in the cache together and thereby improving the cache hit rate

Data Map Hits %

The Data Map Hits % statistic is the percentage of data maps in the file system cache that could be resolved without having to retrieve a page from the disk.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Metrics

Because this value reflects hits, it ideally should be close to 100%. An amount below 100% indicates misses.

Correction

Adding physical memory to a server results in a larger file system cache, which is generally more efficient. Defragmenting disks also helps, putting related pages in the cache together and thereby improving the cache hit rate.

MDL Read Hits %

The MDL Read Hits % statistic is the percentage of Memory Descriptor List Read requests to the file system cache that hit the cache and does not require disk access to provide memory access to the pages in the cache.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Metrics

Because this value reflects hits, it ideally should be close to 100%. An amount below 100% indicates misses.

Correction

Adding physical memory to a server results in a larger file system cache, which is generally more efficient. Defragmenting disks also helps, putting related pages in the cache together and thereby improving the cache hit rate.

Pin Read Hits %

The Pin Read Hits % statistic is the percentage of pin read requests that hit the file system cache and does not require a disk read in order to provide access to the page in the file system cache.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Metrics

Because this value reflects hits, it ideally should be close to 100%. An amount below 100% indicates misses.

Correction

Adding physical memory to a server results in a larger file system cache, which is generally more efficient. Defragmenting disks also helps, putting related pages in the cache together and thereby improving the cache hit rate.

Space Tab

The Space tab of the OS Detail page includes the following sections:

Disk Space Free

The Disk Space Free metric displays the amount of free disk space in megabytes.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Disk Space Detail

The Disk Space Detail section of the Space tab succinctly communicates the general overall performance levels of the server’s disks and space allotment. The table below describes the statistics in this section:

Statistic Description

Partition

The drive letter of the disk.

Local Filesystem

The name of the file system.

Type

The type of file system.

Total Space

Total size of the disk/device's capacity expressed in MBs.

Used Space

Amount of MBs currently allocated on the particular disk/device.

Free Space

Amount of MBs currently unallocated and free on the particular disk/device.

Capacity

The percentage of space used on the device.

Mounted On

The mount point of the device.


Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Device Summary

The Device Summary section of the Space tab displays a bar chart for all devices. The Device Summary section succinctly communicates the general overall performance levels of the server's disks and space allotment. The table below describes the statistics in this section:

Statistic Description

Used

Amount of MBs currently allocated on the particular disk/device.

Free

Amount of MBs currently unallocated and free on the particular disk/device.


Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Device Detail

The Device Detail section of the Space tab succinctly communicates the general overall performance levels of the server's disks and space allotment. The statistics available in this section depend on the platform of operating system. The table below describes the statistics in this section:

Statistic Description

Partition

The drive letter of the disk.

Local Filesystem

The name of the file system.

Type

The type of file system.

Total Space

Total size of the disk/device's capacity expressed in MBs.

Used Space

Amount of MBs currently allocated on the particular disk/device.

Free Space

Amount of MBs currently unallocated and free on the particular disk/device.

Capacity

The percentage of space used on the device.

Mounted On

The mount point of the device.


Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Memory Tab

Network Tab

The Network tab of the OS Detail page succinctly communicates the general overall performance levels of the server's networking. The Network tab of the OS Detail page includes the following sections:

Note: The sections available on this tab depend on the platform of operating system.

Network Details

The statistics available in this section depend on the platform of operating system. The table below describes the information available in this section:

Column Description

Network Interface

The name of network interface.

INET Address/Address

The IP address assigned to the network interface.

Pkts Sent/Sec

The number of packets sent per second.

Pkts Received/Sec

The number of packets received per second.

Sent (KB/Sec)

The number of bytes sent per second.

Received (KB/Sec)

The number of bytes received per second.

Out Pkts Discarded

The number of outbound packets discarded.

In Pkts Discarded

The number of inbound packets discarded.

Out Pkt Errors

The number of outbound packet errors.

In Pkt Errors

The number of inbound packet errors.

Queue Length

The queue length.

Collisions

The number of collisions.

Packets Discarded

The number of packets discarded.


Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Transmission Rate

The Transmission Rate section of the Network tab succinctly communicates the packet transmission rate. The following statistics are available in this section:

Packets Sent/Sec

The Packets Sent/Sec statistic is the number of packets sent over each network adapter per second.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Packets Received/Sec

The Packets Received/Sec statistic is the number of packets received over each network adapter per second.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Transmission Rate (Bytes)

The Transmission Rate (Bytes) section of the Network tab succinctly communicates the packet transmission rate. The following statistics are available in this section:

Sent (KB/Sec)

The Sent (KB/Sec) statistic is the rate at which bytes are sent over each network adapter including framing characters.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Received (KB/Sec)

The Received (KB/Sec) statistic is the rate at which bytes are received over each network adapter, including framing characters.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Transmission Queue Length

The number of pending outgoing packets on either the software transmit queue or the hardware transmit queue

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Packet Collisions

A collision occurs when two devices on the network sense that the network is idle and end up trying to send data at the same time. Some collisions are normal on a repeated network, but excess collisions can cause serious performance problems.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Correction

Contact your network administrator if there is high rate of persistent Packet Collisions.

Packet Discards

The Packet Discards section of the Network tab succinctly communicates the packet discard rate. The following statistics are available in this section:

Outbound Packets Discarded

The Outbound Packets Discarded statistic is the number of outbound packets that were discarded even though no errors had been detected to prevent from being transmitted.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Correction

A possible reason for discarding such a packet could be to free up buffer space.

Inbound Packets Discarded

Received Packets Discarded is the number of received packets that were discarded even though no errors had been detected to prevent from being transmitted.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Correction

A possible reason for discarding such a packet could be to free up buffer space.

Packet Discards

The Packet Discards statistic represents the number of network packets discarded.

LPacket Errors

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Correction

Contact your network administrator if there is high rate of persistent packet discards.

The Packet Errors section of the Network tab succinctly communicates the packet error rate. The following statistics are available in this section:

Outbound Packet Errors

The Outbound Packet Errors statistic is the outbound packets that contained errors preventing them from being deliverable to a higher-layer protocol.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab

Inbound Packet Errors

The Packets Received Errors statistic is the received packets that contained errors preventing them from being deliverable to a higher-layer protocol.

Location

Microsoft SQL Server Performance Analyst Statistics > OS View (DBArtisan - SQL Server Performance Analyst) > Network Tab