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!
David Klee Renewed as Microsoft MVP for 2019-2020
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!
David Klee presenting at PASS Summit 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!
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!
Heraflux awarded VMware vExpert for 2019
Heraflux is proud to announce that David Klee has been awarded the VMware vExpert community award for this calendar year. The VMware vExpert program is VMware‘s global evangelism and advocacy program. The vExpert award is for individuals, not companies, and last one year in duration.
To see the full list of this year’s vExperts, click here.
Thanks VMware! We really appreciate it!
SentryOne announces SQL Sentry Essentials
Today SentryOne, one of the few SQL Server monitoring platforms that we are proud to stand behind, announced a new version of their monitoring platform called SQL Sentry Essentials. It’s for monitoring SQL Server environments at a smaller scale, and provides a good balance of features that these platforms need without the overhead of unnecessary features that these organizations do not need. Read their full press release here and if you’re interested in exploring this platform, contact us for an introduction!
Join Me for PASS Data Expert Series!
I’m very honored to have one of my PASS Summit sessions chosen for the PASS Data Expert Series, taking place this Thursday, February 7th!
This is an awesome opportunity to see some of the top-rated sessions from this year’s PASS Summit. Included is my session on SQL Server Management Studio, which ended up being the second-best attended session of the entire conference. I will be available to answer any questions that come up during the presentation.
Sound awesome? Register today! I hope to see you there!
Speaking at SQL Saturday Chicago 2019
Heraflux is proud to announce that we are speaking and helping to coordinate this year’s SQL Saturday in Chicago, to be held at Benedictine University in Lisle, Illinois, on March 23rd.
Bob Pusateri is one of the event coordinators, and is active in the Chicago Suburban SQL Server Users Group.
David Klee is presenting a new session at 11:10am in room A called “Modern CPU Architecture and SQL Server Performance“.
Session Abstract: Modern CPU architectures are complex and misunderstood, especially as they relate to SQL Server instance configuration and database usage patterns. Default values in virtualization and the SQL Server instance can cause misalignments and improper balance in the way the SQL Server lines up with the CPUs and memory, which results in an immediate (and silent) performance penalty. Come learn as we discuss topics such as physical and virtual NUMA, hyperthreading, query parallelism, and instance settings, and show you how to validate your SQL Server architecture and improve the performance of your critical SQL Servers.
If you’d like to meet up at the event, contact us and we’ll find a quiet spot and talk tech!
Bob Pusateri - Friend of Redgate
Bob Pusateri, our Solutions Architect and SQL Server MCM, has just been named Friend of Redgate for the sixth year in a row!
If you’re not familiar with the Friends of Redgate program, associates work with the Redgate development and product teams to provide feedback on existing products, new tools, and feature enhancements. You can also find us doing things like speaking at events, writing articles on Redgate tools, and participating on the Redgate forums. Last year, Bob even got to present at SQL in the City Summit in Chicago as a result of being in this program.
Redgate is a wonderful organization to be associated with. Not only do they make great tools and publish some very helpful books, but they’re also extremely community-oriented. They sponsor many functions such as user group meetings and SQL Saturdays, and also put on their own events, such as SQL in the City Streamed and SQL in the City Summit.
Thank you so much, Redgate. Bob is certainly looking forward to another awesome year!
The Top 4 Data Technology Trends Every DBA Should Know About in 2019
Join Heraflux’s David Klee, along with Kevin Kline from Sentry One and others in an exciting webinar discussing industry trends that matter to you – the DBA – on Wednesday January 9th at 2pm ET!
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 exciting free webinar here!