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!

Register for this exciting preconference training session today! If you’re even remotely interested in the cloud, this session is for you!


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!


Live360 2018 Conference

Heraflux is proud to contribute to this year’s Live360 conference, to be held in Orlando Florida at the Royal Pacific Resort at Universal from December 2nd to 7th.

David Klee is presenting two sessions at this conference. The first is Thursday in room SQH01 called “Cosmos DB for SQL Server Pros” at 8:00am.

Microsoft’s Cosmos DB is the first globally distributed database service, allowing low-latency scalability across multiple geographical regions. As a SQL Server pro, why does this matter? SQL Server is a great multi-function database that can handle many of your tasks with ease. However, it’s not the only tool in the toolbox, and Cosmos DB is a tremendous alternative for some use cases. This introductory session explores Cosmos DB and walks you through the similarities and differences in the two platforms. We explore how to provision and design your database, insert sample data and query for specific records. We’ll review the scalability and throughput settings, and help you ramp up your DBA and development skills on this compelling new platform.

You will learn:

  • The core concepts of Cosmos DB and the reasons to consider it for data requirements
  • Key concepts around data design, placement and document construction
  • How to query for data and process client-side

Just after this session completes on Thursday, the next session is called “Level Up Your SQL Server Cloud Skills” in room SQLH05 at 9:30am.

Think infrastructure in the cloud is still just for sysadmins? Think again! As your organization moves into the cloud, infrastructure architecture skills are more important than ever for DBAs to master. Expert knowledge of cloud-related infrastructure will help you maintain performance and availability for databases in the cloud. For example, know what an IOP is? Should you use a Database as a Service or provision a cloud-based virtual machine? How many compute resources does your database consume during a given day? Can you secure it properly? Come learn many of the key cloud infrastructure points that you should master as the DBA role continues to evolve!

You will learn:

  • To identify key concepts around database platform selection, such as Azure SQL DB versus Infrastructure as a Service, and make educated recommendations for the appropriate platform
  • To identify key performance metrics, such as storage performance, vCPU/DTU requirements and so on, that help with properly sizing the destination resources
  • About Identifying and recognizing the key reasons to migrate databases to the cloud versus keeping them on-premises

Register for this exciting conference today! We look forward to seeing you there!


PASS Summit 2018

What a whirlwind year this has been! I’m thrilled to invite you to my sessions at this year’s PASS Summit, happening this week in Seattle WA.

Tuesday 11:30am to 1:00pm I’m part of the cloud availability panel over lunch at the SentryOne Data Day precon at the Summit. I hope you already registered for this exciting opportunity to learn more about performance and availability-related topics in this whole-day discussion.

Next, for those of you that still have your mission-critical workloads in your on-premises datacenter (or even for those who have them in the cloud, because almost all of the topics directly apply!), come to my session Wednesday morning at 10:45am entitled “On-Prem SQL Servers, Interstellar Performance“. This deep-dive on virtual SQL Server performance will help you squeeze the most performance from your virtualized SQL Servers, and will help you communicate with your infrastructure team on things that matter the most to performance-oriented SQL Servers. You’ll be sure to have quite a bit of real-world tips and tricks to take back to the office as soon as you return from the conference.

Thursday morning I’m part of the SIOS PASS the Bacon breakfast panel, where we’ll get a good chuckle while we discuss a number of topics pertinent to today’s DBA, such as availability both on-prem and in the cloud, disaster mitigation and preparation, and other availability topics. Come register here, get a great breakfast on the house, and learn while you laugh!

Next, Bob Pusateri and I have a session Thursday afternoon called “SELECT STARS: A SQL DBA’s Introduction to Azure Cosmos DB”, where we discuss and present Microsoft’s new Cosmos DB offering on the Azure platform. This platform is a completely different model of databases than what most SQL Server professionals are accustomed to, and we’ll give you the details you need to expand your horizons and learn about this exciting platform. This session is more of the ‘what’ session to get you amped to learn more about the platform on Friday.

Friday my final session of the conference is entitled “Cosmos DB for SQL Server Pros”, designed to give you more of the ‘how’ when continuing your exploration of Cosmos DB. Bob Pusateri and I will share more of the use cases, scalability details, and live code demos of how to access and alter data in a Cosmos DB database.

I hope to see you at the show! If you want to sync up and talk geek, make sure to ping me to set up some time over your favorite form of caffeine.


Bob Pusateri Presenting at PASS Summit 2018

I’ve been honored to present a session at PASS Summit for the past few years, but this year I am especially humbled as I had not one but two abstracts accepted! The first is an introduction to Azure Cosmos DB. The other is covering some of my favorite SSMS and T-SQL tips and tricks that can help you save time!

Select Stars: A SQL DBA’s Introduction to Azure Cosmos DB

Level: 200
Where: Skagit 4 (TCC Lower Level)
When: Thursday November 8, 2018 1:30-2:45pm

Azure Cosmos DB has quickly become a buzzword in database circles over the past year, but what exactly is it, and why does it matter? This session will cover the basics of Azure Cosmos DB, how it works, and what it can do for your organization. You will learn how it differs from SQL Server and Azure SQL Database, what its strengths are, and how to leverage them. We will also discuss Azure Cosmos DB’s partitioning, distribution, and consistency methods to gain an understanding of how they contribute to its unprecedented scalability. Finally, we will demonstrate how to provision, connect to, and query Azure Cosmos DB. If you’re wondering what Azure Cosmos DB is and why you should care, attend this session and learn why Azure Cosmos DB is an out-of-this-world tool you’ll want in your data toolbox!

SSMS & T-SQL Tricks: Working Smarter, Not Harder

Level: 100
Where: Room 6A
When: Wednesday November 7, 2018 4:45-6:00pm

Do you spend a considerable amount of time in SQL Server Management Studio? Are you always in search of ways to write better code with less effort? Attend this session to see demonstrations of all the helpful gems SSMS has hiding in plain sight that will save you keystrokes, mouse clicks, and time while getting your work done. We will also cover useful T-SQL Scripts to make common tasks easier and faster, and solutions to frequently-encountered coding problems. Whether you’re a developer or a DBA, whether you do your work on-premises or in the cloud, attend this session to learn how easy it is to write better T-SQL code and save the day, all while working smarter, not harder!

I hope to see you there!

PASS Summit starts in a little over a month, and if you’re thinking this sounds pretty awesome, registration is still open! The program committee has put together an incredible schedule combining top presenters from all aspects of the Microsoft Data Platform. On top of that, opportunities for networking abound. While the sessions are awesome, the community is PASS Summit’s greatest asset. You really don’t want to miss this.

Even if you can’t attend, be sure to follow me on Twitter (@SQLBob). I’ll be sharing plenty of updates and photos from throughout the conference. Even if you can’t experience PASS Summit in person, by following myself, other community members, and the #PASSsummit2018 hashtag, you can get a great virtual experience.

This post was originally posted on Bob’s personal blog over at bobpusateri.com.


Where in the World is Bob?

Our Bob Pusateri has been quite busy lately with SQL Server technical talks, and we wanted to highlight some of the in-person events that he’s spoken at.

We love presenting for local technical user groups. Back in August, Bob spoke at the Chicago SQL Server Users Group on August 9th. This session was his first session highlighting Microsoft’s global scale database – Cosmos DB – called “Select Stars: A SQL Server DBA’s Introduction to Azure Cosmos DB.” Bob also presented this session for the Chicago Suburban SQL Server Users Group on September 18th.

We are also proud to speak at a number of SQL Saturdays. Bob spoke at the SQL Saturday in Oslo Norway on September 1st! Bob presented this Cosmos DB session at this event. His slides are available for you to download.

Bob also presented at the SQL Saturday in Boston MA on September 22nd. This session was called “Supercharging Backups and Restores for Fun and Profit“, and his slides are available for you to download.

Bob will be presenting at the Red-Gate SQL in the City Summit in Chicago on October 26th. This session is called “Maximize Availability and More with SQL Server Estate Monitoring“.

Bob is also presenting at this year’s PASS Summit in Seattle during the week of November 6th. He is presenting two sessions – the Cosmos DB introductory session, and “SSMS & T-SQL Tricks: Working Smarter, Not Harder“. This conference is world-class and we want to see you there with us!

If you want Bob or David to speak for your user group or SQL Saturday, contact us!


Did VMware Just Reinvent Itself?

I was fortunate enough to speak at and attend this year’s VMworld USA conference in Las Vegas last week. I caught a small announcement there that I found absolutely intriguing, and feel it could be a revolution in the world of VMware and on-premises virtualization.

Last week, they quietly announced that Amazon Relational Database Services (RDS) will soon be able to be used on your on-premises VMware environment. Very few other details were released, but the RDS web page has a signup form for preview program.

The power of the cloud is largely in the automation that it provides. Our experience has shown that most organizations have fantastic on-prem virtualization but lack the automation to provide a solid end-user self-provisioning platform. The automation in the cloud is one of the primary drivers for cloud adoption.

Did VMware Corp. just expand to become the on-premises extension of Amazon to allow corporations to gain the power of Amazon cloud automation but on their existing on-prem IT investment?

Is this the first service of many to be offered?

Microsoft Azure Stack was an absolute game changer when it launched. I love every bit of it, and we use it in a number of shops to contribute to DevOps initiatives to help IT move as fast as the business. The only downside to Azure Stack is that it must be deployed for production purposes on certified hardware, and that list is small and rather pricey. Consistent hardware is a bonus when keeping the experience constant and predictable, but most organizations are not able to leverage their current IT platform investments to deploy Azure Stack because it is not completely certified for Azure Stack. The expense of a new investment in equipment is most likely hurting adoption.

I’ve been thinking about this for years now. I actually thought that this would happen in a daydream over two years ago. What if the Amazon automation deployment doesn’t require special hardware to run when on-prem? What if it will run on a certified and supported VMware deployment, of which there are millions of such deployments worldwide? How does this change the private cloud game?

Only time will tell. We’re eagerly watching this one. I can’t wait to see if anything new will be announced at Microsoft Ignite in a few weeks!