Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Multiple query plans for the same query?

A while back i was creating a stored procedure to be used for pulling data from the SQL Server into cacti. The procedure was pulling data from a rather large table, which I had made sure was indexed properly for my query. When I executed the stored procedure from SQL Server Management Studio it ran okay –  ~100ms if I remember correctly. That was fine because the query would only be executed once every 5 minutes.

When the stored procedure was called from the cacti tool it took forever to run, and it used up a LOT of resources on the SQL Server. I looked for the generated execution plans using this query:

SELECT *
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_query_plan(plan_handle)
WHERE objectid = object_id('GetData')

 

The weird thing was, that the stored procedure had two different execution plans. I expanded my query to also include query statistics, to see how each of them performed:

SELECT *
FROM sys.dm_exec_cached_plans t1
INNER JOIN sys.dm_exec_query_stats t2 on t1.plan_handle = t2.plan_handle
OUTER APPLY sys.dm_exec_query_plan(t1.plan_handle)
WHERE objectid = object_id('GetData')

 

It was clear, that one of the plans performed well, and the other very poorly. I simply tried to force a recompile of the procedure with this:

exec sp_recompile GetData

But that didn’t seem to change anything. I then manually removed both cached plans:

DBCC FREEPROCCACHE(<plan handle>)

 

But again two plans was created.

I finally identified the issue being related to set options on the connection. When I connect to the SQL Server using SSMS, it uses one set of set options but the linux server hosting the cacti used a different set of default set options. This could be identified using the sys.dm_exec_plan_attributes table valued function:

 

SELECT *
FROM sys.dm_exec_plan_attributes(<plan handle>)

The plan_handle generated by executing the procedure from SSMS gave me this info:
image
 

Notice the set_options value of 251. The documentation of sys.dm_exec_plan_attributes states how to identify which set options this corresponds to. To make it a bit easier to find the options, I used this little query:

DECLARE @set_options_value INT = 251
PRINT 'Set options for value 251:'
IF @set_options_value & 1 = 1 PRINT 'ANSI_PADDING'
IF @set_options_value & 2 = 1 PRINT 'Parallel Plan'
IF @set_options_value & 4 = 4 PRINT 'FORCEPLAN'
IF @set_options_value & 8 = 8 PRINT 'CONCAT_NULL_YIELDS_NULL'
IF @set_options_value & 16 = 16 PRINT 'ANSI_WARNINGS'
IF @set_options_value & 32 = 32 PRINT 'ANSI_NULLS'
IF @set_options_value & 64 = 64 PRINT 'QUOTED_IDENTIFIER'
IF @set_options_value & 128 = 128 PRINT 'ANSI_NULL_DFLT_ON'
IF @set_options_value & 256 = 256 PRINT 'ANSI_NULL_DFLT_OFF'
IF @set_options_value & 512 = 512 PRINT 'NoBrowseTable'
IF @set_options_value & 1024 = 1024 PRINT 'TriggerOneRow'
IF @set_options_value & 2048 = 2048 PRINT 'ResyncQuery'
IF @set_options_value & 4096 = 4096 PRINT 'ARITHABORT'
IF @set_options_value & 8192 = 8192 PRINT 'NUMERIC_ROUNDABORT'
IF @set_options_value & 16384 = 16384 PRINT 'DATEFIRST'
IF @set_options_value & 32768 = 32768 PRINT 'DATEFORMAT'
IF @set_options_value & 65536 = 65536 PRINT 'LanguageId'
IF @set_options_value & 131072 = 131072 PRINT 'UPON'

 

Given the value 251 for @set_options_value, it outputs this:

image

The plan generated from the cacti tool had a different set of options enabled. The documentation also states, that different set options causes different execution plans – which was exactly what happened in this case. Either you need to set the options on the connection client side, or you can simply manually set the options on or off inside the stored procedure. Because i’m not exactly a wiz when it comes to linux/perl scripts, I chose to control the set options within the procedure:

 

ALTER PROCEDURE GetData
AS
SET ANSI_PADDING ON
SET CONCAT_NULL_YELDS_NULL ON
SET ANSI_WARNING ON
...

 

After this change only one execution plan was generated, and the execution from cacti stopped exhausting my resources => I was happy again Smiley

@geniiiuscom

Comments

Posted by Anonymous on 26 October 2011

Pingback from  Dew Drop &ndash; October 26, 2011 | Alvin Ashcraft&#039;s Morning Dew

Posted by Matt Penner on 31 October 2011

Wow!  I just ran into this over the weekend causing hours of headache.  I had a similar situation where a query over SSMS ran in <400ms, yet it took anywhere from 2-10 minutes depending on the parameters from the ASP.Net website.  SSMS uses a SQL Batch statement whereas my website used RPC.  By all accounts the RPC should have been just as fast, if not faster since it was using a cached plan.

I looked at the connection parameters (through the connection logs in SQL Profiler) yet they seemed exactly the same, so I wrote that off as a possibility.

Also, when I manually set the parameters in my website inline with the query, forcing a SQL Batch call, it performed just the same as SSMS.  This was not viable of course as I take parameters from the user so it wouldn't be an inline SQL call and ADO.Net will always create an RPC call when this happens.

While I had it indexed pretty well, SSMS still suggested I add an additional index on one of the core join table columns.  Once I did that the RPC query performed as well as the SSMS one (note that the SSMS query didn't perform any better with the new index).

I didn't know how to easily look up the plans like you just did so thanks for the awesome tips.  I suspect that there were additional connection options that perhaps SQL Profiler didn't show.  If not I have no idea why the RCP call would generate a different plan than the SQL Batch.

Too bad your article wasn't 3 days sooner! ;)

Posted by aahmadi 38489 on 2 November 2011

Doesn't this line have a typo?

IF @set_options_value & 2 = 1 PRINT 'Parallel Plan'

shouldn't 1 be 2, since the sum of that output comes to 249.

Posted by arthur.teter on 2 November 2011

Good article, and very timely.  I am encountering this very problem with a stored procedure that runs in a couple of seconds, and a Crystal report that uses the SP for it's dataset but occasionally takes more than an hour to run when scheduled in Business Objects.

Posted by Anonymous on 3 November 2011

Pingback from  Multiple query plans for the same query? | SQL Server | Syngu

Posted by F. van Ruyven on 3 November 2011

This issue (and others) is also explained in this article: www.sommarskog.se/query-plan-mysteries.html

I think it is worth reading it.

Posted by Geniiius on 3 November 2011

@aahmadi 38489

Yes, it's a typo

Posted by NitinPatel31 on 25 October 2012

I had same issue but resolution was similar to what Matt Penner said. I rebuilt clustered index and it started executing in same time from SSMS and from RPC call.

My scenario was bit different. I had database from SQL server 2000, which I restored on SQL server 2008 R2. And didn't bothered to rebuild indexes. But now after rebulding clustered indexes everything started working as expected.

-Nitin

Posted by NitinPatel31 on 25 October 2012

I had same issue but resolution was similar to what Matt Penner said. I rebuilt clustered index and it started executing in same time from SSMS and from RPC call.

My scenario was bit different. I had database from SQL server 2000, which I restored on SQL server 2008 R2. And didn't bothered to rebuild indexes. But now after rebulding clustered indexes everything started working as expected.

-Nitin

Leave a Comment

Please register or log in to leave a comment.