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

Table Variables and Parallelism

Erik Darling shows off a neat trick for inserting with parallelism into a table variable: One of the many current downsides of @table variables is that modifying them inhibits parallelism, which is a problem #temp tables don’t have. While updating and deleting from @table variables is fairly rare (I’ve seen it, but not too often), you at minimum need an insert […]

Read More

Changing Query Store Report Interval

Arun Sirpal wants to change the report interval for a Query Store report: While not specific to SQL Server 2019 (I was using this version to do some testing) I was struggling to find how to change the time period of analysis for the Query Store reports within SSMS. This is not a ground breaking […]

Read More

Categories

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