May 23, 2011 at 4:25 am
I’ve got performance issue in the customer database- a lot aghoc compiled plan for the same UDF in Memory cache
I reoccurred this on my local DB:
I created the simple UDF as:
I’ve got performance issue in the customer database- a lot aghoc compiled plan for the same UDF in Memory cache
I reoccurred this on my local DB:
I created the simple UDF as:
CREATE FUNCTION [dbo].[fn_test] ( @UTC_time varchar(100)) RETURNS varchar(100)
AS
BEGIN
declare @user_tmp_time varchar(100)
SET @user_tmp_time= '05/18/2011 05:59:00:100'
RETURN @user_tmp_time
END
and executed it in the batches as 2 tests
test1- the same as UDF called from SP
checkpoint
dbcc freeproccache
DBCC DROPCLEANBUFFERS
checkpoint
GO
go
declare @a varchar(100)
set @a = '05/18/2011 05:59:00:100'
SELECT dbo.[fn_test] (@a)
go
declare @a varchar(100)
set @a = '05/18/2011 05:40:22:963'
SELECT dbo.[fn_test](@a)
go
declare @a varchar(100)
set @a = '05/18/2011 05:59:00:300'
SELECT dbo.[fn_test] (@a)
go
declare @a varchar(100)
set @a = '05/18/2011 05:59:10:100'
SELECT dbo.[fn_test] (@a)
go
select sql,* from master..syscacheobjects where sql like'declare @a%'
as execute results I've got adhoc plan in cache - number rows the same as number batches, so the plan doesn't reused
test2 - prepare dynamic SQL
checkpoint
dbcc freeproccache
DBCC DROPCLEANBUFFERS
checkpoint
GO
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = N'SELECT dbo.[fn_test] (@a)'
EXEC sp_executesql @ExecStr, N'@a varchar(100)', '05/18/2011 05:59:00:100'
go
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = N'SELECT dbo.[fn_test] (@a)'
EXEC sp_executesql @ExecStr, N'@a varchar(100)', '05/18/2011 05:40:22:963'
go
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = N'SELECT dbo.[fn_test] (@a)'
EXEC sp_executesql @ExecStr, N'@a varchar(100)', '05/18/2011 05:59:00:300'
go
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = N'SELECT dbo.[fn_test] (@a)'
EXEC sp_executesql @ExecStr, N'@a varchar(100)', '05/18/2011 10:59:00:300'
go
select sql,* from master..syscacheobjects
as execute results I've got prepared plan in cache - only 1 row for all batches, so plan is reused.
Someone can explain me this behavior of SQL SERVER?
and executed it in the batches as 2 tests
test1- the same as UDF called from SP
as execute results I've got adhoc plan in cache - number rows the same as number batches, so the plan doesn't reused
test2 - prepare dynamic SQL
as execute results I've got prepared plan in cache - only 1 row for all batches, so plan is reused.
Someone can explain me this behavior of SQL SERVER?
May 23, 2011 at 4:34 am
Please don't cross post. It just wastes people's time and fragments replies
No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1113142-149-1.aspx
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply