We have been loud with saying SQL Server workloads can be virtualized rather gracefully. There is a myriad of documentation out there talking about how best to virtualize many different workloads, including SQL workloads. We all know that, SQL might get a little tricky though.

 

As with any solution that contains a SQL instance as a backend, planning is the key to make your system shine. If the SQL has to be virtualized, good planning and testing will definitely get you there.

 

CPU and memory sizing are two important aspects to look at, and both of them are easily controllable in a virtual environment. Therefore testing could be done to understand how well our SQL workload will behave. For example, if I ask, what would happen if I allocate  8 GB of memory instead of 16 GB? This is a breeze to implement and subsequently to test. Pretty much, you will figure out what the results would be like, in a short round of testing.

 

Up till now, though, we could not say the same in storage behavior, and capabilities. We never had the methodology to test for storage behavior, if storage was slower or faster. This is a real problem if we would like to virtualize multiple workloads and SQL will happen to be one of them. Will heavy activity in other workloads hinder my SQL performance or my SQL hitting the disks too much, will hinder all other workloads' performance? These are very valid questions, that need a lot of cumbersome testing.

 

Today, we actually have two methods to make our testing easier, and I am sure that we will see a lot of people playing around with these methods.

One method is Storage QoS that comes with Windows Server 2012 R2 Hyper-V, a method to throttle I/O for the virtualized workloads. You may read about it in detail here:

http://technet.microsoft.com/en-us/library/dn282281.aspx

 

 

The other method, that pertains to SQL is in fact Resource Governor in SQL Server 2014, which now has I/O throttling as well. It is a good idea to have a look at page 11 on the following paper:

http://download.microsoft.com/download/2/C/B/2CB311D6-EC00-428C-80F3-03E7369013DD/SQL_Server_Mission_Critical_Performance_TDM_White_Paper.pdf

You may download the CTP2 of SQL Server 2014 and test it out yourself, which I am sure, will be real fun. Windows Server 2012 R2 is also available as an evaluation from Microsoft Web Site.

 

 

Thanks for reading and please do post comments. Appreciate more if anyone posts about such performance tests they might have run, and any challenges they might have faced.