sql server

SQL Server Tuning Results in Cloud Licensing Reduction

We have a Client that recently expressed interest to make their SQL Servers more efficient so that they can establish a standardized scale-out model for their hosted software platform. The objective was to allow a certain number of 'normal' sized customers to coexist on the same SQL Server instance in a VM in the cloud. The  challenging situation for this Client is that the licensing cost of a cloud-based SQL Server deployment was beginning to become cost prohibitive. The SQL Server usage patterns were pretty standard for an in-house developed software package, where the developers are great at what they do, but are not trained in SQL Server internals.

We did a tremendous amount of sampling with Windows Perfmon to get a baseline of resource consumption across what the Client would consider a business cycle. Check out the CPU consumption patterns by CPU core.

That's a moderately active machine, and the concern was that if it became much more active, especially during the morning, they were not going to achieve the customer density per instance that they were looking  for.

We went in and reviewed the infrastructure stack top to bottom, from the VM disk type and disk presentation all the way up to the SQL Server table and index structures, and the way that the application was querying for the data. Many of the items were fantastic and well managed, but a number of items (mostly platform default settings) were getting in the way of scalability. We accessed the system  and tuned the following items without modifying anything that would require significant regression testing of the application.

• Multiple disk controllers and virtual disks, and better distributed the SQL Server objects to more appropriately load balance the OS-layer I/O queues
• Enabled Windows Receive Side Scaling for the network adapter to reduce the CPU concentration for network traffic handling
• Introduced SQL Server Read Committed Snapshot Isolation so that we can achieve better concurrency for read workloads within their databases
• Tuned the SQL Server parallelism settings (Cost Threshold of Parallelism and Max Degree of Parallelism) to better align the application demand with the CPU architecture
• Significant amounts of missing and unused index tuning within the customer databases, and created a standard tuning model to be used across all customers

After tuning for just one week and two maintenance windows, look at the next business cycle CPU activity pattern.

The difference is stark. The CPU consumption across all instances is much lower than before, and so low that based on the projections, they can handle double the customer load per instance on a cloud-based SQL Server instance size half of what they anticipated. Plus, performance dramatically improved for each click of the user in their web application.

The results are clear. The customer is able to improve their customer experience while chopping their deployed SQL Server virtual machine costs by over 75%. Immediately!

I’ll repeat that! The customer just needs to schedule a maintenance window for us to reconfigure the virtual machine. We can apply the indexes and other changes with only a brief interruption in service. Monitor the resource consumption until we see how the machine settles in. One more short interruption in service to change the resource assignments to the VM is all we need to start saving on the cloud deployment costs.

We can do this for your SQL Server databases and help you save on your associated cloud costs . Contact us to learn more!

by David Klee