"Go" SQL Server disk principle

"Disclaimer: This blog post is reproduced from http://www.cnblogs.com/ljhdo/p/5149401.html"

The most recent work has been to deal with the SQL Server database, which requires a more comprehensive understanding of SQL Server. So also in the urgent to read the data, learning this classic relational database.

Performance counters (performance Counter) are a numerical value that quantifies the state or activity of a system, and Windows Performance Monitor acquires performance at a certain time interval (the default sampling interval is 15s) The current value of counter, recorded in the data collections, is a great tool for troubleshooting by being able to view the system's performance data through Performance Monitor. Performance the number of counter, if you do not understand the function of the counter, in the selection of counters, often overwhelmed. Because SQL Server is an IO-intensive application, it often requires a lot of read and write operations, reading data from disk to memory, writing in-memory data to disk, so disk and memory are the lifeblood of SQL Server, monitoring the performance of SQL Server, The frequently used performance counters are disk and memory.

One, disk performance monitoring

Structure of the 1,disk

Typical mechanical disk structures include: Head (head), track, Sector (sector), disk (platter), cylinder (cylinder) and cluster (cluster). ，

When the disk is rotated, if the head remains in one position, each head will draw a circular trajectory on the disk surface called the track. Each track on the disk is divided into segments, which are sectors of the disk, the number of sectors on each track is equal, each sector holds 512 bytes of information, and the disk drives are sector-based when reading and writing data to disk. Several contiguous sectors are grouped into one cluster, and file access is in clusters.

Hard disks are usually made up of overlapping sets of discs, each of which is divided into equal numbers of tracks and numbered from the "0" of the outer Rim to the center, with the same numbered tracks forming a cylinder called the disk's cylinder. The number of cylinders on a disk is equal to the number of tracks on a disk. Since each disc has its own head, the number of disks is equal to the total number of heads. The so-called CHS, refers to the cylinder (cylinder), head (head), Sector (sector), the capacity of the hard disk = number of cylinders x number of heads x sectors x512b.

A sector is the smallest unit that can be addressed independently, and a cluster is the smallest unit of resource allocation. Disk in a read and write operation, by the search, rotation delay and data transmission composition, because the seek and rotation delays occupy most of the read and write operations, disk in the execution of each read and write operation, take the nearest principle, read and write continuous n sectors, read and write the amount of data is an integer multiple of 4KB.

2, sequential read and write, Random Read and write

Random Read and write refers to the data distributed on different tracks, disk's head must move the track, in order to read the corresponding data, sequential read and write refers to the data distributed in the adjacent sectors of the same track, when reading and writing data, disk's head does not need to move the track. Because disk's track movement is mechanical movement, "very slow", accounting for the majority of the time spent reading and writing data, therefore, disk's sequential read and write speed is much higher than the random read and write speed, should try to avoid random read and write. Disk, SSD box memory read/write speed comparison:

3, HDD performance counter

On the OS level, Windows divides a physical hard disk into multiple logical partitions, each of which is called a logical disk, identified by a drive letter, and application is addressed using a disk character that runs on Windows. For logical partitions allocated on the same piece of physical hard disk, the read and write bandwidth of the shared physical hard disk is equivalent to working on a piece of physical hard disk. Therefore, the disk counter is divided into two groups: the PhysicalDisk and Logicaldisk,logcialdisk counters record the read-write count for each logical partition, which is used to analyze disk IO activity and performance parameters for specific application on different logical partitions The PhysicalDisk counter records the performance of the entire physical disk, which is used to understand disk response speed, mainly using the PhysicalDisk counter to analyze disk performance issues.

Disk performance counters that are frequently used at the system level are PhysicalDisk counters and logcialdisk are for informational purposes only:

• %disk time: Indicates how busy disk is, as a percentage of disk processing read-write requests, often greater than 100%, and recommends using%idletime to eject the percentage of disk in read-write status
• Disk Reads/sec: The number of read operations per second requested to disk
• Disk transfers/sec:disk number of read and write operations per second
• Disk Reads Bytes/sec: The number of bytes read from disk per second while disk is performing a read operation
• Disk BYTES/SEC: When disk performs read and write operations, the number of bytes per second read from disk to memory or from memory to disk, good disk, its value is between 20-40MB, the difference disk, its value is below 20MB.
• Avg. disk Queue Length: Provides the primary measure of disk blocking, indicating the average length of the IO request queue that disk waits to process during sample interval, that is, the number of IO requests waiting to be processed by disk, and the length of the queue to take into account the raid , if the storage background uses 100 physical disk, then the count value reaches 100, this value is normal, theoretically, the queue length of each physical disk should not be longer than 2.
• Avg. Disk Sec/transfer:disk Average Time spent per read and write operation
• Avg. Disk Sec/read:disk The average time spent on each read operation
• Avg. Disk Sec/write:disk The average time spent on each write operation

Avg. Disk sec/(Transfer,read,write) is a good reflection of disk's IO speed, so these three-meter values are often used to measure disk IO speed:

• Very good: <10ms
• General: 10-20ms
• A little slow: 20-50ms
• Very slow: >50ms

Second, the system physical Memory performance counter

SQL Server continues to load large amounts of data into memory while it is running, and if the data resides in memory for a long time, SQL Server does not need to request disk IO to directly access the data and respond quickly to the user's request. If the data accessed by SQL Server is not in memory, a hard Page Fault will be generated, and SQL Server first instructs the storage engine to load the data page from disk into memory, resulting in pageiolatch wait until the data is loaded into memory, SQL Server accesses data in memory, processes user requests, because disk's IO speed is slow, latency is high, a large number of hard Page fault will severely affect the speed of SQL Server in response to user requests, so common system-level memory counters are related to page breaks:

• Memory:page Faults/sec: The number of page Fault that occur per second, page Fault including hard Fault and Soft Fault,hard Fault means that data pages need to be read from disk, Soft Fault indicates that data pages need to be read from physical memory, Soft fault does not affect performance, and because hard fault requires access to disk, there is a significant delay.
• Memory:pages Input/sec: The number of hard fault that occur per second that is used to calculate the percentage of hard fault: Pages input/page faults =% hard Page faults, if the percentages are often greater than 40%, This indicates that the system needs to access disk frequently to obtain data, and to some extent, it indicates that the system has memory pressure.
• Memory:pages/sec: The number of page reads or writes disk per second from disk, indicating the number of page interactions between memory and disk: The number of page that will store the page to disk or read data from disk to memory.

Third, the Buffer Manager counter for SQL Server

The Buffer Manager counter is used to monitor how SQL Server uses memory data pages and plan cache to read and write data pages when disk IO is used. Because buffer pool is the most active, most used part of SQL Server, it is also the most likely to have a performance bottleneck, which is especially important:

• Buffer Cache hit ration: Read directly from buffer pool, do not need to read the percentage of data pages from disk, also known as the ratio, this counter indicates that when SQL Server reads data, the data exists in memory, The amount of time and memory pressure that data resides in memory is not so much a reference.
• Page writes/sec: Number of pages written to disk per second, with little memory usage, related to the amount of data modified by the user
• CheckPoint Pages/sec: The number of dirty pages to refresh data to disk, and memory usage is not related to the amount of data modified by the user, if the user made a lot of modifications to the database, the memory of the modified data dirty pages will be more, The number of dirty pages will be larger each time you refresh
• Lazy writes/sec: The number of buffer that was refreshed by lazywriter, if it is dirty, write buffer to disk and mark the buffer space as free, if it is not a dirty page, The buffer space is also marked as free,lazywriter to maintain a certain number of free BUFFER,SQL servers using free buffer to load new data pages.
• page Life expectancy: PLE, the time that the data page resides in memory. If SQL Server does not have new memory requirements, or has free memory to complete the new memory requirements, then lazy writer will not be penalized and the page will remain in buffer pool, then page life expectancy will be maintained at a relatively high level If page life is always high and low, it indicates that SQL Server has memory pressure. The reference value for ple is: Max Server memory/4gb*300s, if the ple value is lower than the reference value for a long time, there may be a bottleneck in memory.
• Page reads/sec: The number of pages of data read from disk per second, which is physical reads, and SQL Server does not need to read the page from the physical disk if the data that the user accesses is slow to exist in memory. Because of the high overhead of physical IO, the Page reads operation must affect the performance of SQL Server.
• Free list stalls/sec: The number of requests to wait for a free page, SQL Server requests to load a page from disk into memory, a buffer,buffer must be allocated in memory The manager is responsible for maintaining the free buffer list, and if there is none in the list, the request must wait until an idle buffer is used in order to load the page in disk into memory.

Often used after four kinds of counters, detection system memory pressure, the first three kinds, for reference only, in this, thank wy123 help.

Iv. memory Manager counters for SQL Server

Memory manager counters are used to monitor the overall usage of server memories, and in a very busy system, lock memory and grant memory are common counters:

• Total Server Memory (KB): Amount of RAM currently used by SQL Server
• Target Server Memory (KB): Total amount of RAM that SQL Server can use
• Lock Memory (KB): Total amount of memory used by SQL Server for locks
• Grant Workspace Memory (KB): The amount of memory that SQL Server uses to perform hash, sort, and create index operations
• Memory Grants Pending (KB): The number of processes that are waiting to be granted, and if the process cannot get a specified amount of memory, the process will not start executing

Five, using performance counter to monitor the overall performance of the SQL Server database system

Create two data set:disk activities to monitor the activity of the physical disk, memory activity, hard fault for monitoring system memory, and RAM usage for SQL Server.

The following excerpt from the "hard disk read and write principle", the author is the real destination, written in very detailed:

Request completion process of the visit

When data needs to be read from disk, the system will pass the logical address of the data to the disk, and the control circuit of the disk will translate the logical address into the physical address according to the addressing logic, that is, determine which track and which sector the data is to be read. In order to read the data in this sector, the head needs to be placed above this sector, in order to achieve this, the head needs to move to the corresponding track, this process is called seek, the time spent is called seek time, and then the disk rotation of the target sector is rotated to the head, the process of time is called rotation time.

That is, a request for a visit (read/write) The completion process consists of three actions:

• Seek (Time): The head movement is positioned to the specified track
• Rotation delay (time): Waits for the specified sector to be rotated from the head
• Data transfer (TIME): The actual transmission between disk and memory

Therefore, the time required to read the sector data (piece of data) on disk:Ti/o= seek time + rotation time + N * Transfer time

How disk reads and writes

When the system stores the files on disk, in the manner of cylinder, head, and sector, i.e. all sectors that are first under the first head of the 1th track (that is, the first track of the 1th disc), and then the next head of the same cylinder, ..., a cylinder is stored full and then pushed to the next cylinder until the contents of the file are written to disk. The system also reads the data in the same order. read out the data by telling the disk controller to read out the cylinder number, the number of magnets, and the sector area code (three components of the physical address) of the sector. The disk controller moves the head assembly directly to the corresponding cylinder, selecting the corresponding head and waiting for the required sector to move to the head. When a sector arrives, the disk controller reads and writes to the sector.

Principle of locality and disk pre-reading

Due to the characteristics of the storage media, the disk itself is much slower than main memory, coupled with mechanical movement, disk access speed is often one of the hundreds of of main memory, so in order to improve efficiency, to minimize disk I/O. To do this, the disk is often not read strictly on-demand, but is read-ahead every time, even if only one byte is required, and the disk starts from this location, sequentially reading a certain length of data into memory. The rationale for this is the well-known local principle in computer science:

• When a data is used, the data around it is usually used immediately.
• The data that is required during the program run is usually relatively centralized.
• Due to the high efficiency of disk sequential reads (no seek time required and minimal rotational time), pre-reading can improve I/O efficiency for programs with locality.

The length of the read-ahead is generally the full multiples of the page, which is the logical block of the computer's management memory, and the hardware and the operating system often divide the main memory and the disk storage into contiguous chunks of equal size, each of which is called a page (in many operating systems, the page size is typically 4k), and the main memory and disk Exchange data When the program to read the data is not in main memory, will trigger a page fault, the system will send a read signal to the disk, the disk will find the starting position of the data and sequentially read one or several pages back into memory, and then return unexpectedly, the program continues to run.

Extended Reading : Description of commonly used system memory performance counters

Page faults/sec is the average number of pages faulted per second. It is measured in number of pages faulted per second because only one page are faulted in each fault operation, hence this is also equal to the number of page fault operations. This counter includes both hard faults (those, require disk access) and soft faults (where the faulted page is found E Lsewhere in physical memory.) Most processors can handle large numbers of soft faults without significant consequence. However, hard faults, which require disk access, can cause significant delays.

Page reads/sec  is the rate at which the disk is read to resolve hard Page faults. It shows the number of reads operations, without regard to the number of pages retrieved in each operation. Hard page faults occur if a process references a page in virtual memory that's not in working set or elsewhere in Physi Cal memory, and must is retrieved from disk. This counter was a primary indicator of the kinds of faults that cause system-wide delays. It includes read operations to satisfy faults in the file system cache (usually requested by applications) and in Non-cach Ed mapped memory files. Compare the value of Memory\\pages reads/sec to the value of Memory\\pages input/sec to determine the average number of PA GES read during each operation.

pages Input/sec  is the which pages are read from disk to resolve hard page Faul Ts. Hard page faults occur when a process refers to a page in virtual memory the is not in its working set or elsewhere in ph Ysical memory, and must is retrieved from disk. When a page was faulted, the system tries to read multiple contiguous pages into memory to maximize the benefit of the read Operation. Compare the value of Memory\\pages input/sec to the value of  memory\\page reads/sec to determine the average number of pages read to memory during each read operation.

pages/sec is the "rate" at which Pages for read from or written to disk to resolve hard page faults.  This counter was a primary indicator of the kinds of faults that cause system-wide delays.  It is the sum of memory\\pages input/sec and Memory\\pages output/sec. It is counted in numbers of pages, so it can be compared to other counts of pages, such as Memory\\page faults/sec, Withou T conversion. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) non-cached Mapp Ed memory files.

Reference Documentation:

Measuring Disk Latency with Windows performance Monitor (Perfmon)

SQL Server Disk Performance Metrics–part 1–the Most important disk performance metrics

Performance Monitor Counters

The reading and writing principle of hard disk

Great SQL Server Debates:buffer Cache hit Ratio

"Go" SQL Server disk principle