SQL Server Performance Counter schema

SQL Server Performance Counter schema

1. Introduction to Windows Performance counters

The operating system can use the Performance Monitor graphical tool to measure performance information for systems, applications, services, and drivers. Performance counters allow you to view the behavior of SQL Server objects, performance counters, and other objects, including processors, memory, caches, threads, and processes. Each object has an associated counter set that measures device usage, queue length, and latency.

To facilitate batch management, you can view and add counters through command statements. You can view performance objects, performance counters, and object instances in PerfMon.exe by adding counters to view related descriptive information. TypePerf.exe can output the performance counter data of the Windows operating system to a command window or to a log file format that supports that feature.

1. SQL Server counter collection, statistics schema diagram

Can see

1. The production server writes data to the database through ODBC Access monitoring Server Sqlperfdata.
2. Data written to Sqlperfdata is written to the Sqlperfdatastat database through job calculations.
   1. SQL Server Counter Deployment Scenarios

We direct access to the Monitoring Server database through ODBC, data transfer to the monitoring server every 15 seconds, the SQL Server database needs to monitor the performance including: CPU, IO, Memory, network, SQL Server blocking, concurrency, caching, etc. we have selected 64 counters, The number of counter instances per server is different, filtered to the end of each server to more than hundred counter instances, the detailed properties and usefulness of the counter to see the counter properties in detail. With so many counters, we use a script batch deployment to view the document "SQL Server Performance counter deployment _huangch_20141202"

1. SQL Server Counter collection Rollup check scenario

As shown, the counter schema stores three databases Sqlperfdata, Sqlperfdatastat, Sqlperfdatastat_histroy respectively; The functions of these three counters are as follows:

1. Sqlperfdata Receiving counter Information

Displaytoid: Counter automatically generated, record counter start information

Counterdetails: Counter is generated automatically, record the basic information of counter

Counterdata: Record counter incoming data

?

?

1. Sqlperfdatastat Statistics Counter Information

Basic Information module:

Counterdetails_collect: Used to synchronize counterdetails table data for rotation statistics

Counterdetails_collect_bak: Useless counter backup

Counterdetails_dts: Statistics Transmission Control Table, in the server unit, records the server statistics transmission time.

Counterdetails_report: Synchronization of Counterdetails_collect table information, through optimization, for the report display role.

?

Data module:

Counterdata_fifteenseconds:15-SEC data collection for synchronizing Counterdata table data.

Counterdata_oneminute: One-minute data collection, 15-second data is calculated as the average value, suitable for viewing the day data. Keep the data for 7 days.

Counterdata_fiveminute: Five minutes data collection, one minute data mean value, suitable for viewing data within 4 days. Keep the data for 7 days.

COUNTERDATA_HALFHOUR:30 minute data collection, one minute data is calculated; Suitable for viewing data within one week. Keep the data for 7 days.

Counterdata_onehour: One-hour data collection, one minute data is calculated; Suitable for viewing data within one months. Retain Permanent

Counterdata_sixhour: Six-hour data collection, one minute data is calculated; Suitable for viewing data within one year. Retain Permanent

?

Baseline module:

Cbasecounterdata_oneminute: Baseline, you can calculate the next week's data movement. Keep the data for 7 days.

Rbasecounterdata_oneminute: You can calculate the data movement of the next day by the chain baseline. Keep the data for 7 days.

?

Manual Maintenance Module:

Countertypedetails: Counter type and description information record table.

MONITORCONTORL: Manual maintenance, counter Alarm monitoring control table.

3. Sqlperfdatastat_histroy Archive counter Information

Counterdata_oneminute_bak: One minute data backup, reserved permanently.

Counterdata_fiveminute_bak: Five minutes data backup, reserved permanently.

COUNTERDATA_HALFHOUR_BAK:30 minute data backup, reserved permanently.

Baseline module:

Cbasecounterdata_oneminute_bak: Baseline Backup, reserved for 7 days.

Rbasecounterdata_oneminute_bak: The baseline backup, reserved for 7 days.

1. SQL Server Baseline algorithm

The SQL Server counter algorithm uses the year and the two different ways.

YoY: You can calculate the baseline data for the next week and take the average of the same time in the same day for nearly eight weeks. Can eliminate the fluctuations caused by business changes, with alarm contrast value. The Red line data indicates that there was an unusually high period in the history.

Chain: You can calculate the baseline data for the next day, and take the average of the same time for nearly two weeks each day. The data can be described in the recent trend of data. With optimized contrast value. From the previous chart can be seen 117-27-139-238 server CPU in the history of abnormal rise, red line than the data, but with the Blue Line synchronization, indicating that this anomaly has been normal in the near future.

?

1. SQL Server Performance Alarm Monitoring

Performance currently monitors CPU, disk space, user concurrency counter value of three, as shown, see the document:

Monitoring objects	Counter Name	Monitoring frequency	Compare data
Cpu	% Processor Time	1 minutes	YoY
Disk	% Free Space;free Megabytes	5 minutes	Valve value
User Connections	User Connections	5 minutes	Chain

?

1. Detailed Counter Properties

General counters

Collects server performance information for operating system servers, including processor, disk, network, memory

2. Processor processor
   1. % Processor time refers to the percentage that the processor uses to perform non-idle threads. This counter allows you to determine the current percentage of CPU usage for the server.
   2. % Privileged Time is the percentage of the process thread executing code in privileged mode. When Windows system Services is invoked, this service often runs in privileged mode to gain access to system-specific data.
4. PhysicalDisk Physical Disk (one raid)
   1. AVG. Disk Queue Length waits for the requested queue size, if this value is too large, indicates that there is a bottleneck on the disk, and that the value of natural disk latency is too large.
   2. Avg. Disk Sec/read;avg. Disk Sec/transfer;avg. Disk Sec/write These three counters indicate how much time it takes each time a disk reads and writes, indicating the disk's latency, in seconds, if the delay exceeds 25mm indicates poor disk performance.
6. LogicalDisk Logical Disk (a volume)
   1. % Free space A disk's idle percentage; Megabytes disk remaining size MB
   2. Disk read Bytes/sec;disk Transfers/sec;disk write bytes/sec These three counters represent the read and write of disks per second, indicating disk IO performance (IOPS)
8. memory Ram
   1. available MBytes can be memory size MB, this memory can be immediately allocated to a process or system for use. It is equal to the sum of memory allocated to the standby (cached), idle, and 0 paged list.
   2. cache Bytes system memory used
   3. commit limit can request, the amount of memory (virtual memory and physical memory) that the Committed bytes has used, in bytes.
   4. free system page table entries refers to the number of page table entries that are not currently in use by the system. Indicates whether memory is idle.
   5. page faults/sec the number of pages that have failed per second, that is, the number of pages that have not been hit in memory. As a result, the pages/sec counter is the speed at which pages are read from disk by an error page. A larger value indicates that there may be a bottleneck in memory or that there is a large amount of data access.
10. Network Interface Networking Interface
    1. Current bandwidth refers to the present bandwidth of a network interface that is estimated in bits per second. For interfaces with constant bandwidth or inability to estimate accurate data, this value is nominal bandwidth.
    2. Bytes received/sec;bytes Sent/sec;bytes total/sec The number of bytes sent and received per second, indicating the current network usage.
    3. Output Queue Length outputs a packet queue with a value greater than 2 indicating a delay in transmission.
    4. Packets Outbound Discarded is selected as the number of dropped packets, the reason for discarding may be to free buffer space. Packets Outbound Errors Number of packets that cannot be transmitted due to errors
    5. Packets Received Discarded is selected as the number of dropped packets, the reason for discarding may be to free buffer space. Packets Received Errors Number of packets that cannot be transmitted due to errors

SQL Server Counters

The Access Methods object in SQL Server provides a counter that monitors access to logical data in the database, and the Buffer Manager counter monitors physical access to the database on disk.

1. Access Methods access mode

Access Methods monitors the methods used to access data stored in the database to help determine whether you can improve query performance by adding or modifying indexes, adding or moving partitions, adding files or filegroups, defragmenting indexes, or overriding queries. The Access Methods counter can also be used to monitor the number of data, indexes, and free space in the database to indicate data volumes and fragmentation for each server instance. Excessive fragmentation of the index can degrade performance.

1. Page Splits/sec The paging score per second due to an overflow in the index page. The larger this data indicates that a table is modified more frequently, and to confirm that the index is suitable for establishing the field.
2. Workfiles created/sec number of working files created per second
3. Worktables created/sec Number of worksheets created per second

2. Buffer Manager Cache Management

   monitors the memory used to store data pages, the physical IO when reading and writing database pages, and so on, this object can determine whether there is a physical memory bottleneck to understand the server's query performance and so on.

   1. buffer cache Hit ratio hits the percentage of the Cache area page. If this value is too low it indicates that there may be a bottleneck in memory
   3. page Life expectancy page indicates the time, in seconds, that the page will stay in the buffer pool of this node without reference. If this value is lowered, the server frequently queries the data in the database.
   4. checkpoint pages/sec Checkpoint The frequency of execution, if the frequency is too high. Indicates that the server is modifying data too frequently.
   5. lazy writes/sec if lazy writing is too frequent, there is no free buffer for use, and there may be some bottleneck in memory. The lazy writer is a system process for batch flushing of dirty expired buffers that contain changed buffers, which must be rewritten back to disk to reuse buffers for other pages, and make them available to user processes.
4. Memory Manager management?

   Monitor overall server memory usage to estimate user activity and resource usage to help pinpoint performance bottlenecks.

   1. Memory Grants pending Specifies the total number of processes waiting for the workspace memory authorization. Refers to the number of processes waiting to allocate memory, if this value is too high or there is a bottleneck in memory or disk.
   2. Target Server Memory (KB), which is ideally capable of using the amount of RAM that is committed by the memory manager. If there is a difference between these two values, the memory exception is indicated.
6. General Statistics Statistics
   1. User Connections;logins/sec;logouts/sec concurrency situation
   2. Processes blocked current blocked quantity
   3. Temp Tables for destruction waits for the number of temporary table/table variables that are destroyed by the purge system thread. Useless temporary tables and table variables.
8. SQL Statistics; Wait Statistics request and wait statistics
   1. batch requests/sec SQL Compilations/sec;sql re-compilations/sec The number of requests, compilations, and recompilation per second that are optimized for reference.
   2. lock waits the number of processes waiting to be locked.
   3. log write waits number of processes waiting to be written to the log cache
   4. page IO latch Waits statistics related to page I/O latches. Used to determine memory or disk bottlenecks
   5. page latch waits used to determine memory or disk bottlenecks
10. Databases for database level
    1. Log flushwait time; logflushes/sec logs flush waits and frequency, if data is abnormal there may be a disk bottleneck
    2. Transactions/sec Number of transactions
12. Locks Lock
    1. Lock Requests/sec;lock timeouts/sec locking request frequency; lock Timeout frequency
    2. Number of deadlocks/sec deadlock condition.

SQL Server Performance Counter schema