What are SQL Server fragments: What are the differences between physical disk fragments and database fragments?

SQLServer fragmentation: The difference between physical disk fragmentation and database fragmentation. Every time we mention "Fragmentation", we naturally think of the disk fragmentation in our computer. In the database, we mentioned fragmentation, which is often confused because we have heard of index fragmentation in some places. Finally, I

What are the fragments of SQL Server: The difference between physical disk fragments and database fragments. Every time we mention "fragments", we naturally think of the disk fragments in our computer. In the database, we mentioned fragmentation, which is often confused because we have heard of index fragmentation in some places. Finally, I

What are the fragments of SQL Server: The difference between physical disk fragments and database fragments?

Every time we mention "Fragmentation", we naturally think of the disk fragmentation in our computer. In the database, we mentioned fragmentation, which is often confused because we have heard of index fragmentation in some places. Finally, we started to think that the "Fragmentation" in the "Fragmentation" of the index is similar to or even the same as the "Fragmentation" in the "disk fragmentation.

Some may analyze the database storage mechanism, but the final analysis may clarify what the index fragmentation is like. It is unknown whether it is the same as the disk fragmentation.

In fact, the problems mentioned above are also the problems that our team encountered when solving problems for customers, and many silly things have been done, in the end, I gradually understood what was going on.

Of course, since I wrote this article, it indicates that they are not the same. Not all "fragments" are the same, just like not all long-haired people are women.

Let's look at their differences.

In fact, physical disk fragmentation is a side effect of Windows working on a physical disk. We also know that clearing disk fragments can make our computer run faster, and Windows is also a built-in tool for clearing disk fragments.

Disk fragmentation degrades the performance of our computer mainly because it increases the latency of the head to read data. Here I use an image from CareySon To illustrate:

We know that Windows will save the files to the free space on the disk. If the available space is insufficient to store all the file data, the file will be saved to multiple locations on the disk in segments. For now, we call these segments as data blocks or the "Sector" in the disk. Each sector has a size. Then, when reading data, the disk head will be located in different sectors of the disk.

Generally, the time when the head moves data blocks is 3-4 times the time when the data blocks are read. In other words, the seek time is generally 3-4 times the data read time.

If there are fragments in the disk of the computer at this time (as for how fragments are generated, I will not spend any time to talk about them here, but it is not difficult to emphasize them here ), the data read operation changes. The original operation for Data Reading is "seek-read data ...", Because there are no fragments, the data is in a continuous sector. If shards exist, the read operation is changed to "seek-read-seek-read". Obviously, Data Reading is delayed. Let's show you a more vivid illustration:

20120821113810. png(20.76 K)
8/21/2012 11:38:40 AM

It can be seen that the time required to read two sectors from 6 ms to 18 ms due to disk fragmentation. Imagine: if a file has hundreds of thousands of slices, the read latency is very serious. Although we don't feel it, because the reading time is still very short, how can we find multiple users, in the case of high concurrency, the consequences of latency can be imagined. Of course, more measures and policies can be adopted at this time.

There are already a lot of tools available for disk fragment cleaning, including Microsoft's built-in and third-party tools. Here I just want to list them a bit: Windows defrag, Power Defra, and Page Defrag.

After the disk fragmentation is completed, let's take a look at the fragmentation problem of SQL Server. The first thing we should talk about is: they are different.

SQL Server uses a relatively advanced storage system (or its storage mechanism design is different from the storage mechanism for storing files in Windows), so that multiple disks can work together in tandem, it also changes the way files are read and stored. The fragmentation of the physical disk is finally solved from the hardware, and cannot be solved by running scripts. However, fragments in SQL Server can be solved.

The SQL Server storage mechanism allows it to use multiple disk storage devices, such as RAID, SAN, and NAS. The disk controller controls Data Reading in these settings. In these devices, data is distributed across multiple disk drives to form blocks, and strip. Because the data is stored in a distributed manner, the data is read from multiple disks in parallel. Finally, each read distributed data is combined to form a large data and transmitted to the above. Here we will not go into depth, because it will involve those N complex storage structures, I/O bus.

I only want to show you the following figures:

20120821113459. png(40.41 K)
8/21/2012 11:38:40 AM


20120821113542. png(25.66 K)
8/21/2012 11:38:40 AM

In fact, I just want to tell my friends that the data stored here is inconsistent because of the design. This is different from the inconsistency of the data stored in the disk fragment mentioned earlier.

You may not understand it at the beginning. You may want to ask:Although the data is stored on multiple disks in this way, fragments still exist on each disk.?

If you have any questions, it means you are thinking about it.

An important concept to be understood here is the disk Controller in a multi-disk storage system. This controller also provides the shard clearing function and coordinates data read/write operations. That is to say, the Controller has been cleaned up at the disk level. For SQL Server, you only need to clear the fragments. Only SQL Server itself knows how to clean up fragments generated by SQL Server. This is its internal mechanism and cannot be operated by the Controller.