SQL Server Tuning Results in Cloud Licensing Reduction

August 18, 2019

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

0 Comments5 Minutes

David Klee Renewed as Microsoft MVP for 2019-2020

July 3, 2019

I am thrilled to announce that I have been renewed for the sixth time as a Microsoft Data Platform MVP for the calendar year of July 2019 to June 2020. The MVP award remains incredibly special to me, and I feel very lucky to be part of this program.

Congratulations to all new and renewed MVPs! Thanks Microsoft for awarding me this designation again this year. I'm thrilled to be a part of this community, and will continue to contribute to the technical communities around the world that I so dearly enjoy. I'm eager to contribute back to Microsoft to help make the data platform products stronger than ever and help people get the most out of them!

by David Klee

0 Comments1 Minutes

David Klee presenting at PASS Summit 2019

July 1, 2019

Heraflux is proud to announce that the founder David Klee is presenting at this year's PASS Summit conference, to be held in Seattle WA from November 5th to 8th at the Washington State Convention Center.

David is presenting a full-day preconference training session called "Advanced Virtual SQL Server Performance" as part of the Architecture stream.

Session Abstract: Virtualization is the new norm in datacenters. Even the ubiquitous “cloud” is nothing more than virtualized servers in someone else’s datacenter with advanced automation on top of it. However, just because it’s everywhere doesn’t mean it is implemented and managed correctly. Done incorrectly, your SQL Server performance can take a massive dive. Are your SQL Servers running at their best possible performance?

This full-day boot camp covers all aspects of performance engineering for virtualized SQL Servers. We will review critical topics such as virtualization layers, VM construction, NUMA, resource scheduling, query parallelization and physical server alignment, “right-sizing,” and storage performance-oriented presentation. Availability and disaster recovery guidelines will also be reviewed. Most importantly, this seminar informs you on methods and terminology to help you work better with your infrastructure team to streamline your critical SQL Server performance. You will understand how to review your critical SQL Servers for both their performance and availability as they relate to the virtualization and physical compute layers underneath, and work with your infrastructure team to implement and streamline any of your findings.

In addition to the preconference training day, David is presenting an abridged technical fire hose session on key topics in a session called "Virtual SQL Server Performance Deep Dive".  This session is designed to ramp up attendees on critical (and silent) performance killers in both cloud and on-premises virtual SQL Servers.

Session Abstract: Your business-critical SQL Servers should not suffer from virtualization-related bottlenecks. When properly architected and managed, the principal bottleneck of performance of these SQL Servers should be only limited by code. However, if not properly constructed, silent and seemingly random performance killers can strike and significantly hurt your database performance. Items such as CPU scheduling, storage queueing, and memory overcommitment can all penalize database performance, and are tough to identify.

This session is packed with many live examples of tips and tricks gained from years of experience in getting the most performance from your virtual SQL Servers. The major roadblocks to performance will be discussed, and tools, techniques, and processes will be demonstrated to help you measure and validate the system performance of the key components underneath your data.

If you've never attended the PASS Summit before, we urge you to go. This conference is the best Microsoft data-platform-centric conference there is, and the information you will gain is second to none. The people you meet networking lasts a lifetime. The content is guaranteed to improve your data platform operations. We won't miss it, and we hope to see you there!

by David Klee

0 Comments5 Minutes