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!
SQL Saturday Atlanta 2019 Precon
We are proud to announce that David Klee will be presenting a SQL Saturday preconference training session called “Level Up Your SQL Server Cloud Infrastructure Skills” at this year’s SQL Saturday in Atlanta on Friday, May 17th! More details are posted at the SQL Saturday event page here.
SQL Saturday Atlanta Precon May 17
I am very proud to announce that I have been selected to present an exciting preconference training session at this year’s SQL Saturday Atlanta on May 17th, 2019, called “Level Up Your Data Platform Cloud Infrastructure“. If you are in the cloud or even remotely considering it, I urge you to attend this session!
We also have a discount code for those interested. It’s PAPERJAM, and enter it at the EventBrite checkout page.
Designed to help people that are starting to consider cloud for their data platforms ramp up quickly on all of the items that they need to hit the ground running and make the best choices while selecting and using the platform.
Session Abstract: While your on-premises infrastructure is likely maintained by another team, in the cloud this responsibility falls on you. Whether your organization is just planning for the cloud, all-in, or leveraging a hybrid architecture, this course is designed to ramp you up on maximizing your investment in the cloud components that you utilize.
Infrastructure skills are essential when managing your environment in the cloud. Knowledge is power; key items such as firewalls and connectivity, storage presentation and performance, baselining, and even platform selection are all necessary areas of concern. Capacity management is also crucial, as the old ways of “throwing hardware” at a performance problem will translate into a larger bill each month.
Attend this session to learn how to efficiently manage cloud resources, set yourself apart from your peers, improve the performance of your mission-critical workloads, and potentially save your employer money!
Webinar - The Top Four Data Technology Trends Every DBA Should Know About in 2019
The Information Technology world was stable for decades, but since the arrival of the 21st century, new paradigms have emerged and technologies are advancing and evolving at record speed.
How can you keep up? And which trends should you focus on?
Join Kevin Kline and a panel of distinguished experts as they provide an overview of the top four data technology trends that will advance your business, amplify your productivity, and expand your career opportunities:
- DevOps
- Cloud Computing
- Machine Learning (ML) and Artificial Intelligence (AI)
- Containers/Docker/Kubernetes
Get details on the most useful resources for learning about and implementing each of these trends. And they’ll discuss a few technologic trends that aren’t yet fully baked or simply aren’t worth your time.
Register for this webinar now!
P21 Connect 2018
Heraflux is proud to sponsor this year’s P21 Connect Conference in Washington DC from August 19 to 21, 2018. David Klee is presenting five technical sessions around SQL Server and infrastructure / cloud technologies!
Register for this exciting event today!
Podcast - Database Protection in the Cloud Era
We’re proud to announce that we were part of a podcast recording with Rubrik recently where we discuss database protection in the public cloud era.
Today, companies are on a journey to modernize their infrastructures and meet the changing demands of businesses. The cloud is becoming a strategic part of this initiative due to its potential to unlock cost savings and increase agility. However, the cloud also brings a new set of migration and data protection challenges.
In this episode, Michael Fal (Rubrik), David Klee (Heraflux Technologies), and John Morehouse (Denny Cherry & Associates) explore the database landscape in the cloud era and share protection best practices.
The podcast is hosted over at SoundCloud. Check it out!
SQL Saturday 2018 Iowa City Precon
David Klee is proud to announce that he will be delivering a new preconference training the Friday before the SQL Saturday in Iowa City, June 22nd, called “SQL Server Infrastructure in the Cloud“.
If you consider that the on-premises infrastructure underneath your SQL Servers is someone else’s responsibility, think again when your systems are in the cloud. Infrastructure skills are essential when planning a cloud migration and managing the environment once the data is in the cloud. Knowledge is power, and key items such as firewalls and connectivity, storage performance, baselining, and even the differences between infrastructure and applications as a service are all necessary areas of concern. Capacity management is also crucial, as the old ways of “throwing hardware” at a performance problem have evolved into a larger bill for cloud resources at the end of each month, and effective handling of the allocated resources will set you apart from the rest. Come learn more about all the areas you need to master as your organization embraces the cloud!
RSVP today at SQLSaturday.com!