Log in
E-mail
Password
Show password
Remember
Forgot password ?
Become a member for free
Sign up
Sign up
New member
Sign up for FREE
New customer
Discover our services
Settings
Settings
Dynamic quotes 
OFFON

VMWARE, INC.

(VMW)
  Report
SummaryQuotesChartsNewsRatingsCalendarCompanyFinancialsConsensusRevisions 
SummaryMost relevantAll NewsAnalyst Reco.Other languagesPress ReleasesOfficial PublicationsSector newsMarketScreener Strategies

VMware : Enhancing Microsoft SQL Server Performance on VMware Cloud on AWS and VMware vSAN with the SQL Server Trace Flag 1800

12/06/2021 | 11:22am EST

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:

  1. 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.
  2. 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.

Disclaimer

VMware Inc. published this content on 06 December 2021 and is solely responsible for the information contained therein. Distributed by Public, unedited and unaltered, on 06 December 2021 16:21:01 UTC.


ę Publicnow 2021
All news about VMWARE, INC.
01:25pVMWARE : Workspace ONE Enables BYO and COPE for Retail with Shift-Based Access Control
PU
08:15aVMWARE : The Future of Retail is Now. Are You Ready?
PU
01/16VMWARE : Leveraging Automation in VMware Skyline Advisor Pro
PU
01/14VMWARE : Photon OS 4.0 Rev 2 is now available
PU
01/14VMWARE ON VMWARE : an Important Partner Across the Entire Business
PU
01/14VMWARE : Feature Friday Episode 77 – DevOps Service Opportunity
PU
01/14VMWARE : Named One of America's Most JUST Companies for 5th Consecutive Year, Awarded Top ..
PU
01/13VMWARE : Attends White House Summit on Open Source Software Security
PU
01/13VMWARE : Announcing VMware vRealize Automation SaltStack SecOps Cloud
PU
01/13VMWARE : Navigating Change & Uncertainty Early in Your Career
PU
More news
Analyst Recommendations on VMWARE, INC.
More recommendations
Financials (USD)
Sales 2022 12 843 M - -
Net income 2022 1 736 M - -
Net Debt 2022 9 405 M - -
P/E ratio 2022 30,5x
Yield 2022 -
Capitalization 52 621 M 52 621 M -
EV / Sales 2022 4,83x
EV / Sales 2023 4,12x
Nbr of Employees 32 300
Free-Float 28,6%
Chart VMWARE, INC.
Duration : Period :
VMware, Inc. Technical Analysis Chart | MarketScreener
Full-screen chart
Technical analysis trends VMWARE, INC.
Short TermMid-TermLong Term
TrendsBullishBearishBearish
Income Statement Evolution
Consensus
Sell
Buy
Mean consensus OUTPERFORM
Number of Analysts 31
Last Close Price 125,18 $
Average target price 152,45 $
Spread / Average Target 21,8%
EPS Revisions
Managers and Directors
Rangarajan Govind Raghuram Chief Executive Officer & Director
Sumit Dhawan President
Zane C. Rowe Chief Financial Officer & Executive Vice President
Michael Saul Dell Chairman
Jason Conyard Chief Information Officer
Sector and Competitors
1st jan.Capi. (M$)
VMWARE, INC.8.03%52 621
ACCENTURE PLC-14.76%223 324
TATA CONSULTANCY SERVICES LTD.6.15%198 076
INTERNATIONAL BUSINESS MACHINES CORPORATION0.41%120 360
INFOSYS LIMITED2.20%109 118
AUTOMATIC DATA PROCESSING, INC.-7.23%96 392