What should we track in IT : Monitoring Microsoft SQL Layer
Sometimes it may be hard and complex to decide on monitoring parameters related with Microsoft SQL servers. These parameters may vary based on running application logic; however it will be the best solution to take advantage of best practices on this subject.
Where should we start?
Since inherently SQL servers run on hardware, it might not be sufficient to monitor requests only or operating system only. Therefore, it is necessary to monitor certain metrics among above mentioned layers and assess the results after relating them with each other.
The metrics tabulated below are recommended parameters to be monitored at initial phase. Inherently, there are many other metrics that should be monitored. You may contact us for detailed information.
Table 1. Recommended Performance Monitoring Metrics to analyse SQL Server Performance
Object
Metric
Description
Values
Memory
Available Bytes
Memory size at physical proper condition
Varies depending on the system. Generally, it should not be less than 5% of the sum
Pages/sec
Number of memory pages written on the disc or read from the disc for avoiding important page faults. It is the sum of Pages Input/sec and Pages Output/sec values.
Average value should be < 50.
Page Faults/sec
Total speed of read from the disc or writing on the disc speed of the pages to remove important page faults.
Average value should be analysed by trend value.
Physical disc (Data disc, Daily disc)
% Disc Time
Indicates ratio of the time during which physical disc is busy.
Average value should be < 50.
Current Disc Queue Length
Indicates number of system responses in the queue for disc access.
Average value should be between 0-2.
Disc Transfers/sec
Regardless of how much data is contained, indicates the speed of completed read and write operations per sec.
Average value should be < 50.
Disc Bytes/sec
Indicates the speed of data transfer from/to the disc during writing or reading operations.
As average increases, efficiency increases. Therefore, it should be analysed based on trend value.
Processor (_Total)
% Processor Time
Metric for the time that processor spends for running the programs. Indicates time ratio for running time of a task piece by the selected processor.
If for average value, %Processor Time values > %80 ise and simultaneously disc and network interface is not busy, it should be analyzed.
% Privileged Time
Indicates time ratio for running a task piece by selected processor at Privileged mode.
If average value is > %85 and simultaneously disc and network interface is not busy, it should be analyzed.
System
Processor Queue Length
Number of tasks in the queue for using processor time.
If this metric is always 2 or more, it should be analyzed.
Context Switches/sec
Indicates switching speed of all processors from a task to the other (Switch number/sec).
Average value is between 300 and 1000. A value equals or greater than 20.000 might mean a problem. In tis case, page fault value at memory should be correlated.
Network Interface
Bytes Total/sec
Speed values per second of entire network traffic passing through one card (send and receive).
It should be compared with network capacity.
Network segment
% Net Utilization
% ratio of network band width in the related network segment.
If SQL server is at a common network hub, suggested value is <%30. If there is designated full-duplex network component, this rate can be as high as %90.
SQLServer:Buffer Manager
Buffer cache hit ratio
It gives a ratio that expressing how many percent of incoming page requests already exist in the memory
Average value should be > %90.
Page Life Expectancy
The value as second showing that how long the pages in the memory stay in the memory
Average value should be > 300.
Free pages
It shows all number of pages within entire free lists.
Average value is expected to be > 640.
SQLServer:Access Methods
FreeSpace Scans/sec
Indicates the ratio per second of free space search number started to add a new registry fragment.
Average value should be analyzed along with trend value.
Full Scans/sec
This metric monitors the unlimited full scan number on basic tables or folders.
Average value should be analyzed along with trend value.There are some reasons for high value such as incomplete indexing and many row requests.
SQLServer:Latches
Total Latch Wait Time (ms)
Total latch wait time for latch requests that must wait during last second (milisecond).
If this metric is too high, that means SQL server spends many time for its internal synchronization mechanism. Average value should be analyzed along with trend value.
SQLServer:Locks(_Total)
Lock Timeouts/sec
Lock time exceedance speed per second.
This value is expected to be 0. If it is high, it means that there is an excess lock in the database.
Lock Wait Time (ms)
It indicates lock wait time.
This value is expected to be low. If it is high, it means that there is an excess lock in the database.
Number of Deadlocks/sec
It indicates speed of number of deadlocks per second.
This value is expected to be 0. If it is not, the request causing this should be investigated.
SQLServer:SQL Statistics
Batch Requests/sec
It shows that number of SQL request running per second.
This value and Web Request/sec value are related. Average value should be analyzed along with trend value.
SQL Re-Compilations/sec
This metric shows number of SQL re-compilation per second for re-compiled SPs.
This value is expected to be near 0.If this is high, SQL profiles should be analyzed to figure out what causes recompilation.
SQLServer:General Statistics
User Connections
It shows number of connected users.
This metric can be used for controlling user distribution especially in the environments where load balance is used. Average value should be analyzed along with trend value.
There are more than one methods and toold related with monitoring of these metrics. For instance, we can say Performance monitoring device in Windows 2008. There is a small sample for monitoring via SQL Studio.
You may contact us for further information about this subject.
What should we track in IT : Monitoring Microsoft SQL Layer
What should we track in IT : Monitoring Microsoft SQL Layer
Sometimes it may be hard and complex to decide on monitoring parameters related with Microsoft SQL servers. These parameters may vary based on running application logic; however it will be the best solution to take advantage of best practices on this subject.
Where should we start?
Since inherently SQL servers run on hardware, it might not be sufficient to monitor requests only or operating system only. Therefore, it is necessary to monitor certain metrics among above mentioned layers and assess the results after relating them with each other.
The metrics tabulated below are recommended parameters to be monitored at initial phase. Inherently, there are many other metrics that should be monitored. You may contact us for detailed information.
Table 1. Recommended Performance Monitoring Metrics to analyse SQL Server Performance
There are more than one methods and toold related with monitoring of these metrics. For instance, we can say Performance monitoring device in Windows 2008. There is a small sample for monitoring via SQL Studio.
You may contact us for further information about this subject.