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:

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:

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

@geniiiuscom