VMware Cloud on AWS delivers the consistent vSphere-based infrastructure that runs on Amazon EC2 elastic, bare-metal instances dedicated to each customer. VMware Cloud on AWS leverages VMware vSAN as the primary storage solution. Several configuration recommendations help you achieve the best performance experience for your Microsoft SQL Server databases. One of them is to use the SQL Server Trace Flag 1800 to improve your queries’ performance up to 300%.
VMware Cloud on AWS storage hardware capabilities
VMware Cloud on AWS now offers two host types – i3.metal and i3en.metal. Both instances use high-performance NVMe drives, featuring 4096 bytes physical sector size (4Kn advanced disk formatting). VMware vSAN supports 4Kn disks but exposes VMDKs to the Guest OS with the physical sector size of 512 bytes (512n formatting). You can find more details about disk formatting and VMware VSAN here.
Guest OS IO block size
We will differentiate between 4096 (4k) bytes and 512 bytes (512b) IO block size. We use the following examples to illustrate the difference:
- 4k IO block size: An IO which size is in multiples of 4096. For example, an IO of the size of 8192 bytes (8192 = 2x 4096) is a multiple of 4096 and hence is 4k-aligned. 4k-aligned IOs have no performance penalty on VMware Cloud on AWS or other VMware Cloud platforms.
- 512b IO block size: An IO which size is in multiples of 512. For example, an IO of the size of 5120 bytes (5129 = 10x 512)is a multiple of 512 and hence is 512b-aligned. 512b-aligned IOs can cause Read-Modify-Write (R-M-W) behavior affecting SQL Server database performance. While operating with 512b-aligned IO, some IOs might be also 4k-aligned; however, we can’t guarantee 4k-alignment of all of IOs in this case.
SQL Server transaction log IO block size
SQL Server performance is highly dependent on the efficient and low latency storage subsystem. SQL Server implements control to optimize the IO flow for different operation types (data, transaction log, backup, etc.). You can find more details in this 3d party blog post.
SQL Server uses an advanced mechanism to calculate the transaction log IO block size. Instead of using the file system (NTFS) allocation unit size (user-controlled), SQL Server takes the physical sector size of the disk where the transaction log files are placed to align the transaction log IO block size.
The figure above depicts the physical sector size of a VMDK used by SQL Server transaction log on VMware Cloud on AWS vSAN datastore. SQL Server will use 512b block size for transaction log IO block size residing on this disk. We can clearly see this using the processor monitor tool: the length of transactional log IOs is reported as 2048/3584 bytes (4x512b/7x512b). IOs of these sizes trigger R-M-W behavior.
Trace Flag 1800
SQL Server supports the user adjustment of the transaction log IO block size. Using the trace flag 1800, you can force SQL Server to always use 4096-byte block size for transaction log IOs, disregarding the underlying physical disk sector size. You can read more about the trace flag here. You can set the flag using the startup parameters of the SQL Server service.
Note: Trace Flag 1800 requires a restart of the SQL Server service. Applying the flag using the DBCC TRACE ON command has no effect.
After enabling the trace flag, we can observe that the IO size for the same query has changed to 4096 bytes. Such IO is aligned with the backend vSAN disk formatting and has no performance penalty on VMware Cloud on AWS.
Performance impact of Transaction log IO block size
To illustrate the performance difference, we use a sample T-SQL script inserting multiple rows in the database within a specified time interval. We run the following script for 30 seconds with different sizes of data per transaction and measure the number of inserted rows. Table 1 summarizes the difference between 512b/4k bytes aligned IO block size of the transaction log and indicates performance gain in the range of 270%-330% with 4k bytes aligned IO block size.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Use "YourTestDB" GO TRUNCATE table "YourTestTable" Go set nocount ON; declare @begintime datetime = getdate() declare @endtime datetime; select @endtime = DATEADD(SECOND,30,getdate()) while (DATEDIFF(SECOND,GETDATE(),@endtime) >= 0) BEGIN insert into tab1(col1,col2,col3) values(replicate('A',800)+ REPLICATE('B',200), REPLICATE('X',1000), REPLICATE('Z',2000)) END Select count(1) from YourTestTable |
Table 1. A normalized number of inserted rows in 30-second intervals with different payload sizes. Each row is an individual transaction.
| Transaction payload in bytes / Transaction Log IO block size | <4096 | 4096<>8192 | 8192<>16384 | 16384<>32768 |
| 512b (No flag) | 0.30* | 0.28 | 0.27 | 0.25 |
| 4096b (T1800) | 1.00 | 0.91 | 0.78 | 0.74 |
| Performance gain | 330% | 324% | 289% | 273% |
Note: * The data within the array x = (x1, …, xn) is normalized using the tranformation: xnew = xi/max(x), where x = (x1, …, xn).
Impact on the production workload
Table 1 above indicates the substantial positive impact of using 4096 bytes block size for SQL Server transaction log IO on VMware Cloud on AWS. The effect is evident for transactions with a small payload and decreases proportionally when we increase the payload size. The performance gain for your database depends on the data size of queries and the workload patterns (databases mainly used in read-mode would not benefit from this change).
Summary
Preventing misalignment IOs helps to significantly increase the performance of write-intensive SQL Server queries on VMware vSAN and on VMware Cloud on AWS. We recommend you evaluate the impact of the trace flag 1800 on your virtualized SQL Server databases running on VMware Cloud on AWS, VMware vSAN, and other VMware Cloud.
