SQL Server Io subsystem II

Io subsystem counters in Performance Monitor

Enter "perfmon" in the operation and use the performance monitor to observe the current I/O performance and determine whether I/O problems exist.

On SQL Server, counters involving I/O are:
Average read Bytes/sec, average number of bytes read per second
Average write Bytes/sec, average number of bytes written per second
Reads/sec, number of reads per second
Writes/sec, number of writes per second
Disk Read Bytes/sec, read rate per second
Disk write Bytes/sec, data transmission rate per second
Average disk SEC/read, average time spent on each read, less than 10 ms indicates good performance, in 10 ms ~ 20 ms means the performance is acceptable. If it is greater than 20 ms, it indicates there is an I/O problem;
Average disk SEC/write, the average write time, less than 10 ms indicates good performance, in 10 ms ~ 20 ms means the performance is acceptable. If it is greater than 20 ms, it indicates there is an I/O problem.
Average disk queue length, the number of I/O wait on the disk, preferably within 1.5 and 2 times the number of axes (spindles). If it is higher than this value, it generally indicates that the IO subsystem has a certain problem, however, raid or disk virtualization is widely used. If you do not know the specific I/O Sub-system configuration, it is difficult to judge the problem from this counter.

Besides perfmon, you can use sqldiag.exe or perfstats scripts to obtain the overall system performance. Then, you can use pal to analyze the collected perfmon file. For details, go to Google.

Sqlio
As mentioned above, it is best to perform a full test when using the I/O subsystem. Sqlio is such a performance testing tool that simulates the read and write features of SQL Server. Using sqlio can quickly locate the bottleneck of the I/O sub-system, and it will be helpful for distributing SQL server files. The following describes how to use sqlio.

Sqlio for http://www.microsoft.com/downloads/details.aspx? Familyid = 9a8b005b-84e4-4f24-8d65-cb53442d9e19 & displaylang = en. After the installation is complete, there is a param.txt configuration file, which contains: C: \ testfile. DAT 2 0x0 100. Here we only need to pay attention to the first and last parameters.
The first parameter indicates the directory where the test file is located. The drive letter is changed based on the hard disk to be tested;
The last parameter indicates the size of the test file, in MB. It is generally the same as the actual database size. After the configuration is complete, enable the CMD command line tool to go to The sqlio installation directory and run the following command to generate a test file.

SQL code

sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10

Then you can use sqlio for testing. The general test statement format is as follows:

SQL code

sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat

Where
Parameter-KW dummy mode anthropomorphic write. If it is-KR, it indicates write;
Parameter-t2 indicates two threads
Parameter-s120 indicates 2 minutes of test
Parameter-DM indicates the specific drive letter
Parameter-O1 indicates the number of outstanding Io
Parameter-frandom indicates the random mode. The opposite is the sequential sequence mode.
Parameter-b64 indicates that each Io size is 64 K

You can save the command as a batch processing file, such as sqliobatch. bat. Then, under cmd, use sqliobatch. Bat> sqlioresult.txt to save the test result as a TXT file and analyze it again. The result after sqlio is run is as follows:
Sqlio v1.5.sg
Using system counter for latency timings, 14318180 counts per second
16 threads writing for 60 secs to file D: \ MSSQL \ testfile1.dat
Using 8kb random IOS
Enabling multiple I/OS per Thread with 8 outstanding
Buffering set to use hardware disk cache (but not File Cache)
Using current size: 2048 MB for file: D: \ MSSQL \ testfile1.dat
Initialization done
Cumulative data:
Throughput metrics:
IOS/sec: 18527.91
MBS/sec: 144.74
Latency metrics:
Min_latency (MS): 0
Avg_latency (MS): 6
Max_latency (MS): 4900
Histogram:
MS: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20 21 22 23 24 +
%: 78 6 8 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2

Among them, the key indicators are IOS/sec and MBS/sec.

This section introduces a powershell script for analyzing sqlio results. For more information, see.

For how to use sqlio, refer to the article on sqlserverpedia by Brent ozar, which also contains detailed sqlio test scripts. Http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

I/O Best Practices
Despite the complexity of the I/O subsystem, there are still some well-summarized practices that are worth learning from.

A. Use multiple data files

Using multiple data files can improve performance, but it depends on the situation. Paul Randal posted a test article on his blog using multiple data files. The result of the test is that as the data file increases, the performance will increase first. After a value is reached, the performance will continue to decrease. For example

We can see that the performance of 8 data files is greatly improved.

For more information, see http://sqlskills.com/BLOGS/PAUL/category/IO-Subsystems.aspx

B. Set a reasonable disk partition offset (disk partition alignment)

This was first raised by Jimmy may (http://blogs.msdn.com/ B /jimmymay. We also mentioned that before Windows 2003, The 63 sectors in front of the disk are set as hidden sectors by default to store MBR, so that the size of the file allocation unit starts after K, so that the original I/O operation is changed to two I/O. By setting this parameter, the performance can be effectively improved by 20% ~ 30%. I personally tested the parameter value of this adjustment, and it does have this effect.

To view your current disk partition alignment value, you can use
WMIC partition get blocksize, startingoffset, name, Index
The obtained results are similar to the following:
Blocksize index name startingoffse
512 0 disk #0, partition #0 32256
512 1 Disk #0, partition #1 48322068480

32256/124 = 31.5, which is a typical example of not setting a reasonable value. After windows 2008, this value has been modified during initialization, so this problem does not exist. However, you still need to manually set 2003 and XP through diskpart. However, after setting this value, you need to reformat the disk to use it !!

C: \> diskpart
Microsoft diskpart version 6.0.6001
Copyright (c) 1999-2007 Microsoft Corporation.
On computer: aspiringgeek
Diskpart> List Disk
Disk ### status Size Free Dyn GPT
--------------------------------------
Drive 0 online 186 GB 0 B
Disk 1 online 100 GB 0 B
Disk 2 online 120 GB 0 B
Disk 3 online 150 GB 150 GB
Diskpart> select Disk 3
Disk 3 is now the selected disk.
Diskpart> create partition primary align = 1024
Diskpart succeeded in creating the specified partition.
Diskpart> assign letter = f
Diskpart successfully assigned the drive letter or mount point.
Diskpart> Format FS = NTFS unit = 64 K label = "myfastdisk" Nowait

For more details, refer to the White Paper disk partition alignment best practices for SQL Server http://msdn.microsoft.com/en-us/library/dd758814.aspx

C. Other Best Practices

There are many such articles, such
Storage top 10 best practices http://msdn.microsoft.com/en-us/library/cc966534.aspx

Physical database storage design http://technet.microsoft.com/en-us/library/cc966414.aspx
There will be some instructions on I/O settings, which is worth your reference.

Summary
Through studying the I/O subsystem, I found that it involves a wide range of content. Here I just introduced some things that I think are important, and some aspects may be worth further consideration. You are welcome to discuss it together.

References
SQL Server I/O basics Chapter 1
Http://www.microsoft.com/technet/prodtechnol/ SQL /2000/maintain/sqlIObasics.mspx

SQL Server I/O basics Chapter 2
Http://www.microsoft.com/technet/prodtechnol/ SQL /2005/iobasics.mspx
Storage Performance for SQL Server
Http://sqlblog.com/blogs/joe_chang/archive/2008/03/04/storage-performance-for-sql-server.aspx