Using Query Store To Diagnose Implicit Conversion Issues

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.

Related Posts

Recompile Hints and Query Store

Erin Stellato answers two questions relating to recompile hints and whether those queries show up in Query Store: Last week in our IEPTO2 class I was asked about queries with OPTION (RECOMPILE) and Query Store. Specifically: Do queries that have the OPTION (RECOMPILE) hint go into Query Store, AND do queries in a stored procedure created with the RECOMPILE […]

Read More

Forcing Plans with Table Variables

Grant Fritchey takes us through an interesting scenario around Query Store: This weekend I was in Stockholm in Sweden, talking Query Store and plan forcing with Steinar Anderson, when he mentioned the problems he had while forcing plans that had table variables in them. Don’t panic. Of course you can force a plan with a table […]

Read More

Categories

November 2018
MTWTFSS
« Oct Dec »
 1234
567891011
12131415161718
19202122232425
2627282930