SQL Server – Dynamic SQL Queries – Duplicate Plans ?


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


Here is a slimmed down version of our cached Plans


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.



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


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 is here.

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




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


In Code


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 )

Connecting to %s