SQL Server – Dynamic SQL Queries – Duplicate Plans ?

Background

In the last couple of weeks we have taken a sure aim at some of our dynamic queries.

This morning came in and started reviewing our cached plans and noticed that we might have duplicates.

 

Cached Plans

Image

Here is a slimmed down version of our cached Plans

Explanation

I was wondering why it seems I have duplicate entries filtering on timePeriod, stateLimit, and Phone.

The first entry has 2040 hits and the other 134 matches.

 

Comparison

SQL Server Management Studio ( SSMS )

I tried to compare the plans using SQL Server Management Studio v2017, but nothing jumped at me.

 

Dynamic Management Views

sys.dm_exec_plan_attributes

I also looked at the normal causes such as:

  1. userid
    • attribute user_id
  2. Set Options
    • Attribute set_options
      • Quoted Identifier
      • Ansi Nulls

 

Text File Comparison

I then saved the query plans into text files and used an online text comparison tool.

DiffNow

DiffNow is here.

From the few differences between the files, here is why my eyes finally rested.

Image

 

Explanation

  1. Saw that @stateLimit only appears in the second plan

 

In Code

Output

Using XQuery was able to confirm that different queries is indeed attributable to slight variation in filtering columns.

Parameter List

Sample 1

 

Sample 2

 

Take away

Thankfully considering text file comparison got me closer to identifying the causation of the “duplicate” plans.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s