Everyone knows me as an Oracle, Linux and VMware Expert. Few know me as a Certified Microsoft SQL Server expert from days of old. I am venturing into the SQL Server world again and plan on leveraging my expertise from the Oracle database world. I love the fact that Microsoft ported their SQL Server database to Linux. Stay tuned as I write future articles on how to deploy SQL Server on Linux and expose best practices to scale a SQL Server database on Linux.

For the first part of many series on virtualizing Microsoft SQL Server on VMware, let’s focus on the storage aspect of the virtalized infrastructure.

Improper storage configuration is often the culprit with performance issues. Majority of the SQL Server performance issues can be correlated back to storage configuration. Typically, relational databases, especially in the production workloads, produce heavy I/O workloads. When storage is misconfigured, performance degradations and additional latency can be introduced especially during heavy I/O workloads.

Storage is always about understanding throughput (IOPS) and disk latency. Understand your workload I/O usage patterns, thresholds, and times of high activity; benchmark and confirm you are achieving the true throughput of your hardware. Bad settings and incorrect configurations will keep the true throughput of the system from being achieved. It is important to understand the total IOPS your disk system can handle from the following formulas.

  • Total Raw IOPS = disk IOPS x number of disks
  • Functional IOPS = (disk IOPS x write%)/ (RAID overhead) + (Raw IOPS x Read%)

You need to find a balance between performance and capacity. Larger drives typically correlates to less performance. The more spindles you have, the more IOPS you can generate. Keep in mind that ESXi host demand is an aggregate demand of all VM’s residing on that host at that time. Low latency high I/O SQL Server databases are very sensitive to the latency of the I/O operations. Storage configurations are very important in achieving an optimal database configuration.

Recommendation for the best performance is always Eager Zeroes thick vmdk’s created in an Independent Persistent mode, to avoid any performance issues. Thick provisioned Lazy Zeroed vmdk’s or Thin provisioned vmdk’s can be used, as long as the Storage array is VAAI capable, improving the performance for first-time-write performance for these two types. Vmdk’s created in an Independent Persistent mode, i.e. Persistent refers to changes persistently written to disks. Independent refers to the vmdk being independent of VM based snapshots.

vAdmins can thinly provision a virtual disk. Thinly provisioned disks equate to storage on demand. Thin provisioning at the storage level and at the virtualization layer is commonly practiced in many companies, as it is a technique used to save space and to over-commit space on the storage array. Make sure how your storage is layed out for your SQL Server environments.

Development databases an be provisioned on thinly provisioned disks and can grow on-demand; however, for production workloads, make sure that you are always leveraging Eager Zeroed Thick VMDK.

This blog post touches on one of the key elements of virtualization to successfully deploy a highly performant SQL Server environment. For more details, sign up for one of my upcoming webinars on “Ten Surprising Performance Killers on Microsoft SQL Server” on Oct 12 at 1:00 PM CST.

http://info.sentryone.com/partner-webinar-surprising-performance-killers


Join Oracle’s Andy Rivenes along with three Oracle ACE Directors next week for the IOUG Master Class: Oracle Database 12c Release 2!

Register today for this on-site jammed packed technology day of learning where we will focus on two of the most compelling features – Multitenancy and Database In-Memory (DBIM) – with ton of discussions on all the new features. Come see:

Jim Czuprynski, Oracle Enterprise Architect, ViON Corporation
Rich Niemiec, Chief Innovation Officer, Viscosity North America
Charles Kim, Founder and President, Viscosity North America

You’ll also have the opportunity to win some great prizes from our event sponsors, ViON Corporation and Viscosity North America. And the best part – there is no cost to you! We have our sponsors to thank for covering all attendee costs for the day. All attendees need to do is register and attend! After registering, you will receive a confirmation of your registration. Location details and the full agenda can be found here.

Here’s the detailed agenda:
8:00 a.m – 9:00 a.m.: Breakfast, registration, networking
9:00 a.m. – 9:15 a.m.: Welcome
9:15 a.m. – 10:00 a.m.: Keynote on Oracle Future: 12cR2, Multitenant, Database In-Memory, Cloud
10:00 a.m. – 12:30 p.m.: Track 1 – PDB Me, ASAP! Oracle 12cR2 Multitenant HOL, Pt 1

Track 2 – Oracle Cloud On the Horizon and Oracle Database In-Memory Deep Dive
12:30 p.m. – 1:30 p.m.: Lunch and networking
1:30 p.m. – 4:00 p.m.: Track 1 – PDB Me, ASAP! Oracle 12cR2 Multitenant HOL, Pt 2

Track 2 – Oracle Database In-Memory By Example and Analytic Views
4:00 p.m. – 4:45 p.m.: 12cR2 Experts Panel Discussion
4:45 p.m. – 5:00 p.m.: Closing Remarks and Prize Giveaways

Click here for details and to register for this incredible event