May 23, 2011 at 1:01 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:44 am
You're seeing the difference between dynamic tsql and prepared statements. You'd see the same thing if you put the code into a stored procedure instead of building the TSQL statement.
Run this at the end of the first test:
SELECT deqp.query_plan,
dest.text,
deqs.execution_count,
deqs.query_hash,
deqs.query_plan_hash
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
You'll see that each statement is different because each one is setting the values in the code with different strings and you can see them in the dest.text column. It's exactly what SQL Server is expected to do. This is why so many of us recommend against dynamic TSQL. It really can be problematic as you're seeing.
"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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply