Recently, but not for the first time, I was confronted with a server administrator that didn’t want to allocate more than one vCPU to a SQL Server machine in a development environment. His exact words ring over and over in my head.
Do we really care about development environment performance?
The short answer is, YES!
Two environments always tend to pull the short straw when it comes to resources, the development environments and the disaster recovery environments. The DEV environment is often referred to the sand box environment because things can get messy, and that is OK. Unlike a sand box, however, it is a critical part of your systems and is not there so that the developers can have recess. It has a very real purpose with true business value.
Customers demand fast because they want to be productive, because that directly affects their life. You want your IT staff to be productive and not waste time as well. It is my opinion that most people underestimate the effect of interruptions and distractions in the work place.
The risk of distraction elevates sharply for every second that someone stares at a screen without something to do, such as waiting 10 seconds for a query to finish, waiting 15-30 seconds for a connection to time out, or 6 minutes for a report to load. Naturally, a single event of this type will not cause any measurable loss of productivity. On a poor performing server or environment, however, these events are common and can occur regularly.
As the risk of distraction grows the bored developer or DBA is likely to take one of two routes. He / she may open some form of entertainment such as social media or he / she may attempt to tackle a small task which has been nagging them. Either way you lose productivity even though only the entertainment route is obvious. The attempt at multi-tasking will cause the developer or DBA to ramp up their mind-set on the new task. This will take precious minutes in most cases. By this time, the original delay likely has already elapsed. Snapping back to the original task leaves the mind fragmented and the secondary task incomplete. If the secondary task is completed instead, reverting back to the original task also takes ramp-up time. This waste of time continues to compound every time the developer or DBA has to run another query or connect to another server.
At a former organization, I used to tell my co-workers that every time they shoulder-tapped rather than sending me an email, they were taking a minimum of 15 minutes of my time. That 15 minutes was for simple, 2 minute, questions or comments. The distraction could extend if a full conversation was required. After fending off actual lines of people outside my cubicle, I began posting signs announcing whether I was observing office-hours or not.
This reason alone costs more than any hardware required to beef up your DEV environment and that is without multiplying the effect by every developer in the department.
Physicals & Virtuals
When it comes to hardware utilization the argument takes different shades depending upon whether you are talking about cold-iron compared with virtual servers. Physical servers tend to be underutilized. That is one of the major benefits of virtualizing your servers, for consolidation. Given that physicals are going to be underutilized until they are about to need upgrading, you will need to consider the life of a machine and size appropriately for growth. Purchasing new DEV servers will never be highest on an infrastructure team’s priority list which is why I recommend purchasing enough resources to last 3-5 years, factoring in the amount of new projects year over year and feature additions to existing software. The alternative is that you spend years with hardware which becomes more and more severely over-utilized and productivity continues to decline.
Virtual servers are more fun to discuss. The amount of times that I have seen SQL Server instances with a single vCPU and less than 4 GB of memory is astonishing. The SQL Server storage engine is high-end software with powerful parallel processing capabilities. Allowing it to run with only one vCPU is nearly a sin. When sizing a DEV virtual machine, I recommend over committing the host with vCPUs. Part of the arguments I hear on resource allocation on DEV VMs is that the resources will not be used consistently. This argument, however, includes an argument in favor of added vCPUs. If multiple DEV servers are not being used simultaneously, then over committing the host’s processors will allow the few active DEV servers to respond faster without pegging the host’s processors.
Host memory utilization is a bit of a harder sell, unfortunately. You can use dynamic memory allocations but, with SQL Server, this is not nearly as effective as it is with vCPUs. What does not help the case is the fact that DEV environments are often sitting on a relatively cheap I/O subsystem such as the local disks of the host or lower tier SAN storage. Going light on the memory makes you ride the I/O subsystem harder. In this case all I can recommend is to fight for the right amount of memory. If your production system can fit 20% of its data pages in memory, shoot for the same percentage in the DEV environment. This might go over reasonably well with the infrastructure team if the data set in DEV is smaller than the production environment. You are trying to avoid productivity resistance but you are also trying to avoid problems where the developers begin to believe that you can only performance test in production because the lower environment’s hardware is too different. Once that mentality sets in you are asking for trouble, but if the DEV environment’s memory allocation is proportional to production, you can drive optimization efforts in DEV more effectively.
For the development environment, it is most cost-effective to buy licenses for SQL Server Developer edition. Surprisingly though, I have seen organizations who did not want to worry about a client access list (CAL) or did not want to pay for MSDN subscriptions for all of their developers. For those organizations, standard edition ends up on their DEV environment with the below sticker price.
With physical servers you are going to be using the per core licensing model. With virtual servers you can either license the entire host as enterprise edition or each VM individually. One fact that seems not to be widely known is that, for a physical server or virtual server, the smallest number of core licenses that you can buy for SQL Server is four.
With SQL Server Standard edition’s price being north of $3700.00 and minimum purchases of four cores, spinning up a DEV VM with less than four cores is simply a waste of money.
The smallest SQL Server you should build
|Memory||4 GB||increase based on total database size|
You don’t waste dry cleaning money on your dad jeans, and you don’t run $14k worth of software on $3k worth of hardware.
For more on the topic of sizing read Brent Ozar’s post here.