Loading

×Sorry to interrupt
CSS Error

Refresh

Cookie Preference Center

General Information on cookies

When you visit our website, we use cookies to ensure that we give you the best experience. This information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience on the site and the services we are able to offer. Further information can be found in our

Cookie Policy.

Strictly Necessary

Strictly Necessary

Always Active

Strictly Necessary

Strictly necessary cookies are always enabled since they are essential for our website to function. They enable core functionality such as security, network management, and website accessibility. You can set your browser to block or alert you about these cookies, but this may affect how the website functions. For more information please visit www.aboutcookies.org or www.allaboutcookies.org.

Cookie Details‎

Performance

Performance

Performance

Performance cookies are used to analyze the user experience to improve our website by collecting and reporting information on how you use it. They allow us to know which pages are the most and least popular, see how visitors move around the site, optimize our website and make it easier to navigate.

Cookie Details‎

Please confirm your settings by refreshing the page.

Functional

Functional

Functional

Functional cookies help us keep track of your past browsing choices so we can improve usability and customize your experience. These cookies enable the website to remember your preferred settings, language preferences, location and other customizable elements such as font or text size. If you do not allow these cookies, then some or all of these services may not function properly.

Cookie Details‎

Advertising

Advertising

Advertising

Advertising cookies are used to send you relevant advertising and promotional information. They may be set through our site by third parties to build a profile of your interests and show you relevant advertisements on other sites. These cookies do not directly store personal information, but their function is based on uniquely identifying your browser and internet device.

Cookie Details‎

Advertising Cookies

Consent Leg.Interest

Select All Vendors

Select All Vendors

Select All Hosts

Select All

Clear Filters

Information storage and access

Apply

Confirm My Choices Reject All Allow All

Skip to main content

Products and Accounts

Support

Knowledge

Communities

Success

Learning

My Subscriptions

Loading

Knowledge Base

Tips for configuring Microsoft SQL Server in a virtual machine (1002951)

Last Updated: 9/8/2020Categories: InformationalTotal Views: 22079Language:
Chinese (Simplified)JapaneseEnglish

32 like this article

Subscribe to get an email
newsletter with new updates

32 people found this helpful Please provide article feedback:

Details

This article provides a list of points to consider when configuring Microsoft SQL Server in a virtual machine.

Solution

RDBMS performance characteristics:

Traditionally CPU and Disk I/O intensive.

Require adequate amounts of CPU power to prevent SQL scheduler thread queuing.

Require fast I/O throughput to write logs without affecting query performance.

From a performance perspective, these issues arise:

CPU contention with other processes and virtual machines.

Disk I/O contention when writing logs or dealing with write-intensive database activity.

Use uni-processor virtual machines to begin with, upgrading to VSMP virtual machines only if necessary.

It is easy to move from a UP HAL to a SMP HAL, but not vice-versa.

Upgrading from UP to SMP mode requires a compulsive HAL change which can result in HLT related issues after the upgrade when using Win2k virtual machines.

Re-installation is required only when moving between ACPI and non-ACPI configurations. Virtual machines with two VCPUs require two PCPUs to be available to service a request. Systems with few PCPUs or many VSMP.

Virtual machines may see poor performance.

Adjusting CPU resources from uni-processor to SNP when needed:

Database performance suffers if CPU resources are constrained.

Consider increasing minimum and maximum CPU resource allocations to reduce this queue length.

Separate data and logs on different physical disks:

A standard RDBMS best practice is to place SQL data and logs onto separate physical disks. This ensures that I/O intensive logging does not interfere with data queries.

In a virtual machine, create one (or more) .vmdk(s) for data and another for logs. Pre-allocate disk space when you create the virtual disks.

Note: An auto-growing .vmdk sacrifices some performance for the added flexibility.

Put the .vmdk on a different LUN to spread activity across multiple spindles.

This allows sequential writes to occur as fast as possible, helping to boost I/O performance.

Consider RAID 10 for write intensive databases:

RAID 10 is more efficient than RAID 5 for writes, but requires more disks.

Calculate your read/write ratio to determine if your database is write intensive.

After your database has been running for a few days, run Windows Task Manager and view the total number of I/O Read Bytes and I/O Write Bytes for the sqlservr.exe process.

This shows you the ratio of SQL Server reads to writes since the SQL Server service was last restarted.

Disk provisioning and alignment:

Configure the vmdks as eagerzeroedthick to avoid overhead I/O for space provisioning.

Systems prior to Server 2008 may need the partitions aligned for additional performance. See manufacturer for details on this process.

Disable screen savers and unused devices:

Screen savers and devices like floppy drives, CD-ROM drives, serial ports, etc. consume resources, even when they are not in use.

Summary:

For additional information, see Virtualizing Enterprise Applications: SQL Server.

Allocate adequate CPU resources.

Layout disks for maximum I/O throughput.

Additional Information

For translated versions of this article, see:

Español:

Português:

日本語: Microsoft SQL Server を仮想マシンで構成する場合のヒント (2080949)

简体中文: 在虚拟机中配置 Microsoft SQL Server 的提示 (2086207)

Detectable by VMware SkylineTM

Actions

Copy

link to clipboard
copied!

Print

Language:
Chinese (Simplified)JapaneseEnglish

Attachments

Additional Resources

KB
•
End Of General Availability of the Free vSphere Hypervisor (ESXi 7.x and 8.x) (2107518)

KB
•
Download and Install VMware Fusion (2014097)

KB
•
No se puede actualizar el VMware Tools existente (1001354)

Results 1-3 of 3

Ask The Community

Get answers quickly from VMware experts in the community

Post Subject

CONTINUE IN COMMUNITIES

Related Products:

VMware vSphere ESXi

VMware ESX Server

Related Versions:

Take Our Survey

Was this article helpful?

YES

NO

Copyright © 2005-2024 Broadcom. All Rights Reserved. The term “Broadcom” refers to Broadcom Inc. and/or its subsidiaries.

Terms of Use

Your California Privacy Rights

Privacy

Accessibility

Cookie Settings

Support Assistant