April 17, 2013 at 5:24 am
While checking our production plan cache, I noticed that over half of the entries were like the following:
exec mt_amstask7 'AMS','78609072','1045458320','20130417 05:05','AMH','20130417 05:07','U'
exec mt_amstask7 'AMS','78609072','1045458304','20130417 05:05','AMH','20130417 05:07','U'
exec mt_amstask7 'AMS','78609072','1045458320','20130417 05:05','AMH','20130417 05:06','U'
etc.
Of the 20,563 cached plans, 11,449 were of this type. It is obvious to me that the only difference is the value(s) of the parameters.
My question: Is it possible to execute a stored procedure with parameters using sp_executesql? I haven't been able to make this work. I've tried this:
DECLARE @SQLNVARCHAR(2048);
DECLARE @SQLParms NVARCHAR(2048);
DECLARE @SQLParm1 INT;
DECLARE @Parm1 INT;
DECLARE @SQLParm2 VARCHAR(10);
DECLARE @Parm2 VARCHAR(10);
DECLARE @SQLParm3 DATETIME;
DECLARE @Parm3 DATETIME;
SET @Parm1 = 1;
SET @Parm2 = 'ABC';
SET @Parm3 = '1958-11-14 04:25';
SET @SQL = N'TestProc @SQLParm1=@Parm1,@SQLParm2=@Parm2,@SQLParm3=@Parm3;';
EXECUTE sys.sp_executesql @SQL,@SQLParms,@SQLParm1,@SQLParm2,@SQLParm3;
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Parm1".
I'm thinking the DECLAREd parameters are out of scope for the execution.
Any help would be appreciated.
~ Jeff
April 17, 2013 at 5:37 am
SQL Server 2008 has an option called "optimize for ad hoc workloads"
Please see whether is helpful to you.
April 17, 2013 at 6:15 am
I checked out the MSDN article on that setting; it certainly looks promising for our current environment.
Checking on raw numbers from our plan cache, grouping by distinct usecounts:
Total plans: 28,376
usecount = 1: 21,682 (76%)
I think this makes a strong case for using the "optimize for ad hoc workloads" setting as you suggest.
Thanks for your help.
~ Jeff
April 17, 2013 at 6:37 am
jhager (4/17/2013)
While checking our production plan cache, I noticed that over half of the entries were like the following:exec mt_amstask7 'AMS','78609072','1045458320','20130417 05:05','AMH','20130417 05:07','U'
exec mt_amstask7 'AMS','78609072','1045458304','20130417 05:05','AMH','20130417 05:07','U'
exec mt_amstask7 'AMS','78609072','1045458320','20130417 05:05','AMH','20130417 05:06','U'
etc.
Of the 20,563 cached plans, 11,449 were of this type. It is obvious to me that the only difference is the value(s) of the parameters.
My question: Is it possible to execute a stored procedure with parameters using sp_executesql? I haven't been able to make this work. I've tried this:
DECLARE @SQLNVARCHAR(2048);
DECLARE @SQLParms NVARCHAR(2048);
DECLARE @SQLParm1 INT;
DECLARE @Parm1 INT;
DECLARE @SQLParm2 VARCHAR(10);
DECLARE @Parm2 VARCHAR(10);
DECLARE @SQLParm3 DATETIME;
DECLARE @Parm3 DATETIME;
SET @Parm1 = 1;
SET @Parm2 = 'ABC';
SET @Parm3 = '1958-11-14 04:25';
SET @SQL = N'TestProc @SQLParm1=@Parm1,@SQLParm2=@Parm2,@SQLParm3=@Parm3;';
EXECUTE sys.sp_executesql @SQL,@SQLParms,@SQLParm1,@SQLParm2,@SQLParm3;
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Parm1".
I'm thinking the DECLAREd parameters are out of scope for the execution.
Any help would be appreciated.
~ Jeff
I'm sorry, I see where you declare @SQLParms but I don't see you setting it to any anything before you use it in the exec sp_executesql.
April 17, 2013 at 6:58 am
Am I right in thinking that the optimise for ad hoc workloads option is preferred for high usage of non-parameterised queries?
'Only he who wanders finds new paths'
April 17, 2013 at 7:33 am
I added this:
SET @SQLParms = N'@SQLParm1 INT,@SQLParm2 VARCHAR(10),@SQLParm3 DATETIME';
Still get the same error.
April 17, 2013 at 8:32 am
slightly edited the query from msdn, hopefully will help you out:
DECLARE @IntVariable varchar(10);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
SET @IntVariable = 'somevalue';
SET @SQLString = N'SELECT *
FROM [database].dbo.
WHERE [field] = @level';
SET @ParmDefinition = N'@level varchar(10)';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable;
'Only he who wanders finds new paths'
April 17, 2013 at 8:45 am
Thanks...I've been able to use sp_executesql to execute SELECT, INSERT, etc., but NOT execute a procedure. Maybe it's not possible because of the parameter scope.
I appreciate the response, though.
~ Jeff
April 17, 2013 at 9:02 am
Sorry Jeff, misunderstood!
Just to run a sproc you can of course edit it to look like:
SET @Param1 = 'somevalue';
SET @SQLString = N'EXEC [db].dbo.
' + @Param1
EXECUTE sp_executesql @SQLString
But I dont think that will have the desired effect on how the plans are stored and utilised.
'Only he who wanders finds new paths'
April 17, 2013 at 9:26 am
jhager (4/17/2013)
Thanks...I've been able to use sp_executesql to execute SELECT, INSERT, etc., but NOT execute a procedure. Maybe it's not possible because of the parameter scope.I appreciate the response, though.
~ Jeff
Actually, you may want to read the sp_executesql entry in Books Online a little more carefully, it shows you exactly how to use it. Assuming that the @SQLParmN variables are the parameters being used in the dynamic sql, this is how it should look (hopefully, since I have nothing with which to test):
DECLARE @SQL NVARCHAR(2048);
DECLARE @SQLParms NVARCHAR(2048);
DECLARE @Parm1 INT;
DECLARE @Parm2 VARCHAR(10);
DECLARE @Parm3 DATETIME;
SET @Parm1 = 1;
SET @Parm2 = 'ABC';
SET @Parm3 = '1958-11-14 04:25';
SET @SQL = N'TestProc @SQLParm1, @SQLParm2, @SQLParm3;'; -- The Dynamic SQL
SET @SQLParams = N'@SQLParm1 INT, @SQLParm2 VARCHAR(10), @SQLParm3 DATETIME'; -- Define the variables used in the Dynamic SQL
EXECUTE sys.sp_executesql @SQL, @SQLParms, @SQLParm1 = @Parm1, @SQLParm2 = @Parm2, @SQLParm3 = @Parm3;
April 17, 2013 at 9:47 am
Yup, just had a play...
DECLARE @TestClient varchar(11);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
SET @TestClient = 'somevalue';
SET @SQLString = N'EXEC [database].dbo.Test @Client'
SET @ParmDefinition = N'@Client varchar(10)';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @Client = @TestClient;
'Only he who wanders finds new paths'
April 17, 2013 at 10:18 am
Thanks, Lynn, the syntax you suggested works. I seem to have confused how the parameters were defined / used. I've not used this dynamic method before, only EXECUTE('CREATE VIEW ...'), etc. to script an object creation list.
~ Jeff
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply