Tom Norman shares a case study of using Query Store to fix a nasty implicit conversion problem:
A while ago, we contracted with a third party to start using their software and database with our product. We put the database in Azure but within a year, the database grew to over 250 gigs and we had to keep raising the Azure SQL Database to handle performance issues. Due to the cost of Azure, a decision was made to bring the database back on-premise. Before putting the database on the on-premise SQL Server, the server was running with eight CPUs. In production, we are running SQL Server 2016 Enterprise Edition. When we put the vendor database into production, we had to dramatically increase our CPUs in production, ending up with twenty-eight CPUs. Even with twenty-eight CPUs, during most of the production day, CPUs were running persistently at seventy-five percent. But why?
Tom takes us from symptom (high CPU utilization) to diagnosis and is able to provide the third-party vendor enough information to improve their product.