Lesson Learned #530: Comparing Execution Plans to Expose a Hidden Performance Anti-Pattern

One of the most powerful features of SSMS Copilot is how it lets you compare execution plans and immediately show you performance issues.

In this case, I would like to share with you my lesson learned comparing two queries and how they behave very differently inside the engine.

We have the following queries, these are using a table _x_y_z_MS_HighCPU that contains 4 millon of rows. The column TextToSearch is a varchar(200) datatype.

— Query 1
SELECT COUNT(*)
FROM [MSxyzTest].[_x_y_z_MS_HighCPU]
WHERE TextToSearch = N’Value: 9′;

— Query 2
SELECT COUNT(*)
FROM [MSxyzTest].[_x_y_z_MS_HighCPU]
WHERE TextToSearch = ‘Value: 9’;

Since the query texts are different, each will have a different query ID in Query Store. By running the following T-SQL, for example, I can identify the query IDs.

SELECT
qsqt.query_sql_text,
qsq.query_id,
qsp.plan_id,
qsp.query_plan_hash,
qsp.last_execution_time
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query qsq
ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id = qsp.query_id
WHERE qsqt.query_sql_text LIKE ‘%SELECT COUNT(*)%’ — FROM [[MSxyzTest]].[[_x_y_z_MS_HighCPU]]%’
ORDER BY qsp.last_execution_time DESC;

Queries 1 and 2 can be compared directly. Using Copilot, I ran the following prompt: Compare the execution plans for the two queries (query id 1 and query id 2 using Query Store. Highlight any differences in operators, estimated vs actual row counts, or implicit conversions.

Running the following prompt : CPU Usage: Please, show the top resource-consuming queries in the current database using Query Store data. Include query text, execution count, duration, CPU time, and logical reads.

We could see the impact of using an antipattern:

This article was originally published by Microsoft's Azure Database Support Blog. You can find the original article here.