July 12, 2010 at 5:07 am
I have identified some memory issues on a production server (windows 2008 std 64 bit SP2 with SQL 2005 enterprise 64-bit SP3 build 4053). I have identified one of the contributing factors to be the amount memory used to hold query plans. There is a stored procedure calling sp_executeSQL with a parametised string of the form
set @strSql = N'select @strFunctionResult = ' + isnull(@strRuleFunction, '')
+ '( ' + isnull('''' + @strOrderNo + '''', 'null')
+ ', ' + isnull('''' + @strPropertyValue + '''', 'null') + ')'
There are various functions that are used instead of @strFunction depending on the business function. I would prefer to use the application rather than T-SQL to implement the business logic but since I can't change the application my goal is to improve plan reuse. Three of the functions called appear 17,327 times and use 674 MB of memory each when I last checked. They appear in sys.dm_exec_cached_plans with objType of Prepared and cacheobjType of Compiled Plan.
The functions are themselves unpleasant and the changes I can make are limited. They reference a view "create view vwgetdate() as select getdate()" to make date comparisons. An example of one problem function (greatly simplified) is below
ALTER FUNCTION[dbo].[udf_exampleFunction] (@strOrderNo varchar(20), @strExpectedValue varchar(256))
returns varchar(256) as
begin
declare @dtmCurrentDateTime datetime
declare @strResult varchar(256)
select @dtmCurrentDateTime = dtmNow from dbo.vwGetDate
if convert(datetime, @strExpectedValue) > @dtmCurrentDateTime
begin
set @strResult = null
end
else
begin
set @strResult = 'Expected before ''' + isnull(@strExpectedValue, '') + ''' found ''' + isnull(convert(varchar, @dtmCurrentDateTime, 113), '') + '''.'
end
return @strResult
end
Can anyone help me identify why SQL is not re-using these plans? I have a few ideas but I was hoping someone might be able to reduce the number of test that I need to perform. PS if i was to change the functions to accept GETDATE() as a parameter, all rule functions would need to accept a new parameter to handle this input.
July 12, 2010 at 6:47 am
If you're building a custom string every time you execute, that's why you're seeing thousands of execution plans. You simply can't do that. Very, very small changes to the text can cause new execution plans. To get plan reuse, you need to stop generating everything in an ad hoc manner.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2010 at 7:05 am
j_baldwinson (7/12/2010)
There is a stored procedure calling sp_executeSQL with a parametised string of the form
set @strSql = N'select @strFunctionResult = ' + isnull(@strRuleFunction, '')
+ '( ' + isnull('''' + @strOrderNo + '''', 'null')
+ ', ' + isnull('''' + @strPropertyValue + '''', 'null') + ')'
That is not a parameterised SQL statement. The only parameter that's in there is the output parameter. As such, just about every single call to that will have a different plan. I really doubt that can be auto-parameterised.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2010 at 9:00 am
Sorry I haven't been very clear. I was trying to leave the question open for other ideas which I guess is leaving it a bit too open. I was meaning to ask if the query was parameterised correctly with sp_executeSQL as below
SELECT @strSql = N'SELECT @strFunctionResult = @strRuleFunction(@strOrderNo,@strPropertyValue);'
@params = N'@strRuleFunction SYSNAME,' +
N'@strOrderNo VARCHAR(20),' +
N'@strPropertyValue VARCHAR(50),' +
N'@strFunctionResult VARCHAR(50) OUTPUT'
EXEC sp_executesql @strSql, @params, 'dbo.udfFunction1', 'A12345678', 'PropertyValue', @strFunctionResult = @funcResult OUTPUT
1. would the plans ever get reused if one of the parameters is the function name? In other words is it possible to dynamically create the two statements here and achieve plan reuse without branching logic to handle udfFunction1 and udfFunction2...?
select @strFunctionResult = dbo.udfFunction1('A12345678','PropertyValue')
select @strFunctionResult = dbo.udfFunction1('B12345678','PropertyValue')
2. would the use of GETDATE() in a view cause recompilation with different plans. i.e. does a non-deterministic function in a view definition make the view plan inconsistent and therefore all query plans that reference the view unsuitable for reuse or require recompilation?
3. Does anyone know any other method of doing this kind of thing without using sp_executeSQL but still within SQL server 2005. CLR maybe?
From GilaMonster and Grant's responses so far I think the answer is what I already suspected - don't try anything this "flexible"!
July 12, 2010 at 11:47 am
sp_ExecuteSQL is the right way to do what you're trying to do. Since the string itself doesn't change (only the parameters), it should not regenerate query plans. It may generate one plan per distinct function call, but even if it does you're better off than what you had before.
There's nothing wrong with trying to be this 'flexible'; just be careful. Make sure the users calling the function don't have access to any functions they don't need access to (because they could call whatever they want with this). Even better, compare the function name provided against a finite list of acceptable functions and only run if it belongs to that list.
--J
July 12, 2010 at 11:57 am
Actually, I was thinking about it, and what you want to do is this:
SELECT @strSql = N'SELECT @strFunctionResult = ' + @strRuleFunction +
'(@strOrderNo,@strPropertyValue);'
@params = N'@strOrderNo VARCHAR(20),' +
N'@strPropertyValue VARCHAR(50),' +
N'@strFunctionResult VARCHAR(50) OUTPUT'
EXEC sp_executesql @strSql, @params, 'A12345678', 'PropertyValue',
@strFunctionResult = @funcResult OUTPUT
Primarily because calling a variable as a function doesn't work. You'd have to use dynamic SQL to do that anyways.
Also, you want one execution plan per function called. Anything else would be horribly inefficient. What you don't want is a seperate execution plan for each variation in any of the other parameters.
--J
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply