May 22, 2009 at 7:36 am
I suspect it's the temp table in the query, but you know what, instead of both of us guess, run the query with a Profiler trace going and look for the SQL:StmtRecompile event. That will tell you what's causing the recompile.
"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
May 22, 2009 at 9:12 am
Yep, mixing DDL with DML in stored procedures is a sure-fire way to get a re-compile. Best practices mentioned grouping all DDL (like your Create table statement for the temp table) to the beginning of the proc as to avoid multiple recompiles.
A.J.
DBA with an attitude
May 24, 2009 at 5:02 am
A.J. Wilbur (5/22/2009)
Yep, mixing DDL with DML in stored procedures is a sure-fire way to get a re-compile. Best practices mentioned grouping all DDL (like your Create table statement for the temp table) to the beginning of the proc as to avoid multiple recompiles.
Well that was true in SQL Server 2000, but a number of changes and optimizations in SQL2K5 change all that.
Firstly, statement-level compilation means that it doesn't really matter where you put your CREATE TABLE statements.
Secondly, SQL Server caches 1 data page and and 1 IAM page with the execution plan for a temporary table created in a stored procedure (with a few caveats - see link).
See http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx for details from the SQL Server Storage Engine guys, and see http://www.sqlservercentral.com/Forums/Topic707743-338-1.aspx for a practical example of it in action from a recent thread on this site.
My suspicion in this case is that a recompile is being triggered when a sufficient number of new rows is added to the temporary table. The SQL posted is complex though, so Grant is absolutely right - run a profiler trace and look for SQL:StmtRecompile events (not the old SQL2K SP:Recompile stuff).
Cheers,
Paul
May 25, 2009 at 2:03 am
Another question. If the information in one of the table in the sp is updated pretty often, like few hundreds in an hour, is this a possible cause for recompilation? the sp I'm talking about is recompiling about 200 times a day.
I run a profiler trace and the statements that recompile are mainly the ones with temporary tables.I'm trying to fix them and maybe put some questions after that.
10q
May 25, 2009 at 2:11 am
I found an article that says : : The KEEP PLAN query hint changes the recompilation thresholds for temporary tables, and makes them identical to those for permanent tables
Should I use OPTION (KEEP PLAN) to change the recompilation threshold of the temporary table or there are side effects?
Again 10q
May 25, 2009 at 2:24 am
shnex (5/25/2009)
Should I use OPTION (KEEP PLAN) to change the recompilation threshold of the temporary table or there are side effects?
There are side effects. Because the plan won't be recompiled when the rows in the temp table change, the plan will be less accurate and may be slower. Maybe much slower.
Test it, try it and see.
Have you looked at how long the recompiles are? (profiler) If they're quick, they may not be causing a problem.
While we're optimising, what is this piece of code supposed to do?
insert into #nums values(1);
while @rc * 2 <= @max
begin
insert into #nums SELECT n + @rc from #nums;
set @rc = @rc * 2;
end;
insert into #nums
select n + @rc
from #nums where n + @rc <= @max;
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
May 25, 2009 at 2:54 am
Unfortunately I don't really know the logic of the sp.It's the first time I see most of the big ones, and this is one.
About the recompiling the problem is that some sp are recompiled a lot, more than 1000 and even more than 2000 times a day.For the sp in cause the biggest time to be completed after recompile was about 2000 (ms) in duration - profiler. I think that for a big number of recompilations this might be a problem.
I was thinking of using the KEEP PLAN option because I don't think that the differences in the temporary table will be so important. And the threshold for the temp tables I think is too small.
10q for the answer
May 25, 2009 at 5:29 am
But there is a question: how do I check the recompilation time? because in profiler I don't have an option for duration for this. I verified until now the duration of the recompiled sp on the next run, but I don't think this is ok ...
May 25, 2009 at 3:03 pm
You can get a general idea of the optimization time for all queries from the dynamic view: sys.dm_exec_query_optimizer_info
If you are experiencing delays due to compilation, you may see a number of entries in Activity Monitor waiting on a waitresource of the form: TAB: dbid:object_id [[COMPILE]]
You might also like to monitor the SQL compilations / sec counter in performance monitor.
If you look at the output of sys.dm_os_wait_stats, an entry of RESOURCE_SEMAPHORE_QUERY_COMPILE indicates that SQL Server is limiting (throttling) the number of compilations to limit the memory consumed by this activity. This would be a good indicator that compilations are flooding the system. You may also see RESOURCE_SEMAPHORE_MUTEX, though this can be due to pending memory grants in general.
For a single procedure or SQL batch, you can see the compilation time by executing SET STATISTICS TIME ON beforehand. The compilation time is included in the figure output to the messages pane: SQL Server parse and compile time: 6 ms.
By the way, you can see the most significant wait types on your system by running:
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' -- filter out additional irrelevant waits
)
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
ORDER BY W1.rn;
May 25, 2009 at 3:06 pm
Also take a look at:
May 26, 2009 at 1:02 am
I'm using profiler to dig into this and I used OPTION (KEEP PLAN) to "eliminate" the temporary table recompile but no success. It shows me that the next statement is still recompiling.can you tell me why?
thanks
insert into #tab_ordered_result
select
[Activity_Id],
[Nome],
[Id_Function],
[Entity_Id],
[Descr],
[Username],
[Start_Date],
[Destination_User_Id],
[Priority],
[Roles],
[PortName],
[Work_Date],
[CortesiaUser],
[SenderUserName],
[Tooltips],
[Statuses],
[IconPaths],
[Nome1],
[Color],
[IsDocument]
from #results
where
(len([Work_Date]) 0 or [Work_Date] is not null) order by Start_Date DESC OPTION (KEEP PLAN)
and this one
select [Activity_Id],
[Nome],
[Id_Function],
[Entity_Id],
[Descr],
[Username],
[Start_Date],
[Destination_User_Id],
[Priority],
[Roles],
[PortName],
[Work_Date],
[CortesiaUser],
[SenderUserName],
[Tooltips],
[Statuses],
[IconPaths],
[Nome1],
[Color],
[IsDocument]
from #tab_ordered_result
WHERE (RowNumber > @PageLowerBound and RowNumber < @PageUpperBound)
OPTION (KEEP PLAN)
I must say that in the table #results I have only one record for the example I tested and that this two statements are executed dynamic, as strings, but I have other dynamic sql statements that do not recompile...
I think this option is not really "working", because even the next statement is recompiling :
select count(1)
from #results
OPTION (KEEP PLAN)
May 26, 2009 at 2:21 am
shnex (5/26/2009)
I'm using profiler to dig into this and I used OPTION (KEEP PLAN) to "eliminate" the temporary table recompile but no success.
Keep plan does not eliminate temp table recompiles. It just changed the threshold at which changes in row numbers trigger a recompile. It's normally lower for temp tables than permanent tables. Keep Plan makes the thresholds the same for both.
Books online [br]KEEP PLAN
Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes have been made to a table by running UPDATE, DELETE, MERGE, or INSERT statements. Specifying KEEP PLAN makes sure that a query will not be recompiled as frequently when there are multiple updates to a table.
The plan will still recompile if the no of rows changes significantly.
Perhaps, instead of adding hints, it might be an option to take a look at the proc in totality and see if the temp tables are necessary or if the whole thing can be simplified.
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
May 26, 2009 at 2:25 am
this is a pretty big one too
ALTER PROCEDURE [dbo].[List_ByPages]
(
@User_Id uniqueidentifier,
@Id_Organizationunit uniqueidentifier,
@whereClause Nvarchar(3000),
@PageIndex int,
@PageSize int,
@orderClause nvarchar(50),
@work_Date bit
)
AS
CREATE TABLE #temp_tab
(
[Activity_Id] uniqueidentifier,
[Nome] varchar(255),
[Id_Function] uniqueidentifier,
[Entity_Id] uniqueidentifier,
[Descr] varchar(255),
[Username] varchar(50),
[Start_Date] datetime,
[Destination_User_Id] uniqueidentifier,
[Priority] tinyint,
[Roles] varchar(255),
[PortName] int,
[Work_Date] datetime,
[CortesiaUser] varchar(255),
[SenderUserName] varchar(255),
[Tooltips] varchar(8000) ,
[Statuses] varchar(8000),
[IconPaths] varchar(8000),
[Nome1] varchar(25),
[Color] varchar(25),
[IsDocument] bit
)
CREATE TABLE #tab_ordered_result
(
[RowNumber] int identity(1,1),
[Activity_Id] uniqueidentifier,
[Nome] varchar(255),
[Id_Function] uniqueidentifier,
[Entity_Id] uniqueidentifier,
[Descr] varchar(255),
[Username] varchar(50),
[Start_Date] datetime,
[Destination_User_Id] uniqueidentifier,
[Priority] tinyint,
[Roles] varchar(255),
[PortName] int,
[Work_Date] datetime,
[CortesiaUser] varchar(255),
[SenderUserName] varchar(255),
[Tooltips] varchar(8000) ,
[Statuses] varchar(8000),
[IconPaths] varchar(8000),
[Nome1] varchar(25),
[Color] varchar(25),
[IsDocument] bit
)
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1;
if (@PageLowerBound is null or @PageUpperBound is null )
begin
set @PageLowerBound = isnull(@PageLowerBound,0)
set @PageUpperBound = isnull(@PageUpperBound,10000)
end
declare @string nvarchar(max),
@final_string nvarchar(max);
set @string = 'insert into #temp_tab
select
Wf_Activity.Activity_Id,
Sst_Organizationunit.Nome,
Wf_Activity.Id_Function,
Wf_Activity.Entity_Id,
Wf_Activity.Descr,
Sst_User.Username,
Wf_Activity.Start_Date,
Wf_Activity.Destination_User_Id,
Wf_Activity.Priority,
Wf_Activity.Roles,
Wf_Activity.PortName,
Wf_Activity.Work_Date,
case
when len(usercortesia.Cognome + '' '' + usercortesia.Nome) < 25 then usercortesia.Cognome + '' '' + usercortesia.Nome
else substring(usercortesia.Cognome + '' '' + usercortesia.Nome,1,18)
end as [CortesiaUser],
sender_U.Cognome + '' '' + sender_U.Nome as [SenderUserName],
dbo.GetEntityTooltips(Entity_Id) as Tooltips,
dbo.GetEntityStatuses(Entity_Id) as Statuses,
dbo.GetEntityIconPaths(Entity_Id) as IconPaths,
case
when len(Sst_Organizationunit.Nome) < 25 then Sst_Organizationunit.Nome
else substring(Sst_Organizationunit.Nome,1,18)
end as Nome1,
case
when (Wf_Activity.Work_Date is null or len(Wf_Activity.Work_Date) = 0) then ''color:Red''
else ''''
end as Color,
case
when Wf_Activity.Descr like ''%Doc.%'' then 1
else 0
end as [IsDocument]
from
Wf_Activity
left outer join Sst_User as sender_U on Wf_Activity.Sender_User_Id = sender_U.[User_Id]
left outer join Sst_User on Wf_Activity.Current_User_Id = Sst_User.[User_Id]
left outer join Sst_Organizationunit on Wf_Activity.Sender_Id_Organizationunit = Sst_Organizationunit.Id_Organizationunit
left outer join Sst_Organizationunit_User on Wf_Activity.Destination_Id_Organizationunit = Sst_Organizationunit_User.Id_Organizationunit
left outer join Opt_Protocollo on Wf_Activity.Entity_Id = Opt_Protocollo.Protocollo_Id
left outer join Sst_User as usercortesia on Opt_Protocollo.CortesiaUser = usercortesia.[User_Id]
left outer join Sst_User as dest_user on Sst_Organizationunit_User.[User_Id] = dest_user.[User_Id]
where
(
Wf_Activity.Destination_User_Id is null
or
Wf_Activity.Destination_User_Id = Sst_Organizationunit_User.[User_Id]
)
and
(
Wf_Activity.Roles is null
or
Wf_Activity.Roles ''True''
or
(
Wf_Activity.Roles = ''True''
and
(
Sst_Organizationunit_User.FlagResp in (1, 2)
or
(dest_user.ImpersonateResponsabile = 1 and Sst_Organizationunit_User.FlagIncaricoPrincipale = 1)
)
)
)
and
(
exists (select 1 from
dbo.Mt_Pratiche as prt
inner join dbo.StatiPratica_Role as strl on strl.Stato_Id = prt.Stato_Id
inner join dbo.Sst_Role_User as rus on rus.Id_Role = strl.Id_Role
where prt.Pratica_Id = Wf_Activity.Entity_Id
and rus.Id_Organizationunit_User = Sst_Organizationunit_User.Id_Organizationunit_User
)
or
exists (select 1 from
dbo.Mt_Pratiche as prt
inner join dbo.StatiPratica_Role as strl on strl.Stato_Id = prt.Stato_Id
inner join dbo.Sst_Profile_Duty_Roles as pdr on (pdr.Id_Role = strl.Id_Role and pdr.logicaldelete = 0)
inner join dbo.Sst_Organizationunit_User as ous on (ous.Id_Duty = pdr.Id_Duty and ous.logicaldelete = 0)
inner join dbo.Sst_Organizationunit as ou on ou.Id_Profile = pdr.Id_Profile and ou.Id_Organizationunit = ous.Id_Organizationunit
where prt.Pratica_Id = Wf_Activity.Entity_Id
and ous.Id_Organizationunit_User = Sst_Organizationunit_User.Id_Organizationunit_User
)
or
not exists (select 1 from dbo.Mt_Pratiche as prt where prt.Pratica_Id = Wf_Activity.Entity_Id)
)';
if (@whereClause is null or len(@whereClause) = 0)
begin
set @whereClause = ' '
end
set @string = @string + ' ' + @whereClause
exec sp_executesql @string;
with rights as
(
select distinct
[Sst_Function].[Id_Function],
[Sst_RolePrivilege].[Executive],
[Sst_RolePrivilege].[Applicative],
[Sst_Function].[ToDo_Id_Function],
[Sst_Function].[Path]
from
[Sst_Function] inner join
[Sst_RolePrivilege] on [Sst_Function].[Id_Function] = [Sst_RolePrivilege].[Id_Function] inner join
[Sst_Profile_Duty_Roles] on [Sst_RolePrivilege].[Id_Role] = [Sst_Profile_Duty_Roles].[Id_Role] inner join
[Sst_Organizationunit_User] on [Sst_Profile_Duty_Roles].[Id_Duty] = [Sst_Organizationunit_User].[Id_Duty] inner join
[Sst_Organizationunit] on [Sst_Profile_Duty_Roles].[Id_Profile] = [Sst_Organizationunit].[Id_Profile] and [Sst_Organizationunit].[Id_Organizationunit] = [Sst_Organizationunit_User].[Id_Organizationunit]
where
[Sst_Organizationunit].[Id_Organizationunit] = @Id_Organizationunit
and [Sst_Organizationunit_User].[User_Id] = @User_Id
and ([Sst_Function].[ApplicativeValue] is null or [Sst_Function].[ApplicativeValue] 1)
and [Sst_Organizationunit].[LogicalDelete] = 0
and [Sst_Organizationunit_User].[LogicalDelete] = 0
and [Sst_Function].[LogicalDelete] = 0
and [Sst_RolePrivilege].[LogicalDelete] = 0
and [Sst_Profile_Duty_Roles].[LogicalDelete] = 0
union
select distinct
f.[Id_Function],
rp.[Executive],
rp.[Applicative],
f.[ToDo_Id_Function],
f.[Path]
from
[Sst_Function] f,
[Sst_RolePrivilege] rp,
[Sst_Role_User] ru,
[Sst_Organizationunit_User] ou
where
ou.Id_Organizationunit = @Id_Organizationunit
and ou.[User_Id] = @User_Id
and ru.Id_Organizationunit_User = ou.Id_Organizationunit_User
and (f.[ApplicativeValue] is null or f.[ApplicativeValue] 1)
and ru.Id_Role = rp.Id_Role
and rp.Id_Function = f.Id_Function
and [f].[LogicalDelete] = 0
and [rp].[LogicalDelete] = 0
and [ru].[LogicalDelete] = 0
and [ou].[LogicalDelete] = 0
),
rights2 as
(
select
[Id_Function],
max(case
when [Executive] = 'A' then 3
when [Executive] = 'W' then 2
when [Executive] = 'R' then 1
else 1
end) as [Executive],
[ToDo_Id_Function],
[Path]
from rights
group by [Id_Function], , [ToDo_Id_Function], [Path]
)
,rights3
as
(
select
rights2.[Id_Function]
from
rights2
inner join rights on rights2.[Id_Function] = rights.[Id_Function]
and case
when rights2.[Executive] = 3 then 'A'
when rights2.[Executive] = 2 then 'W'
when rights2.[Executive] = 1 then 'R'
else 'R'
end = rights.[Executive]
)
SELECT DISTINCT
[Activity_Id],
[Nome],
#temp_tab.[Id_Function],
[Entity_Id],
[Descr],
[Username],
[Start_Date],
[Destination_User_Id],
[Priority],
[Roles],
[PortName],
[Work_Date],
[CortesiaUser],
[SenderUserName],
[Tooltips],
[Statuses],
[IconPaths],
[Nome1],
[Color],
[IsDocument]
into #results
FROM #temp_tab
INNER JOIN rights3 on rights3.[Id_Function] = #temp_tab.[Id_Function];
if (@orderClause is null or len(@orderClause) = 0)
begin
set @orderClause = 'order by [Start_Date] '
end
else
begin
set @orderClause = ' order by ' + @orderClause + ' '
end
set @final_string = '
insert into #tab_ordered_result
select
[Activity_Id],
[Nome],
[Id_Function],
[Entity_Id],
[Descr],
[Username],
[Start_Date],
[Destination_User_Id],
[Priority],
[Roles],
[PortName],
[Work_Date],
[CortesiaUser],
[SenderUserName],
[Tooltips],
[Statuses],
[IconPaths],
[Nome1],
[Color],
[IsDocument]
from #results '
if @work_Date = 1
begin
set @final_string = @final_string + 'where
(len([Work_Date]) = 0 or [Work_Date] is null) ' + @orderClause
set @final_string = @final_string +'
insert into #tab_ordered_result
select
[Activity_Id],
[Nome],
[Id_Function],
[Entity_Id],
[Descr],
[Username],
[Start_Date],
[Destination_User_Id],
[Priority],
[Roles],
[PortName],
[Work_Date],
[CortesiaUser],
[SenderUserName],
[Tooltips],
[Statuses],
[IconPaths],
[Nome1],
[Color],
[IsDocument]
from #results
where
(len([Work_Date]) 0 or [Work_Date] is not null) ' + @orderClause
end
else
begin
set @final_string = @final_string + @orderClause
end
exec sp_executesql @final_string
select [Activity_Id],
[Nome],
[Id_Function],
[Entity_Id],
[Descr],
[Username],
[Start_Date],
[Destination_User_Id],
[Priority],
[Roles],
[PortName],
[Work_Date],
[CortesiaUser],
[SenderUserName],
[Tooltips],
[Statuses],
[IconPaths],
[Nome1],
[Color],
[IsDocument]
from #tab_ordered_result
WHERE (RowNumber > @PageLowerBound and RowNumber < @PageUpperBound)
select count(1)
from #results
drop table #temp_tab
drop table #tab_ordered_result
If you have any questions about the logic...I can help you this time 🙂
May 26, 2009 at 2:28 am
shnex,
Have you done any of the analysis work using the suggestions already posted to see if re-compilation is even an issue on this server?
The use of dynamic SQL will not be helping matters much - in fact it means that SQL Server can't use some of the new optimizations for temporary tables created in procedures.
My feeling is that the use of dynamic SQL is causing the compilations, but whether that is significant or not depends on the results of the analysis which you haven't posted yet.
It is likely that the effort being put into this might be better directed to rewriting the procedure as Gail suggested.
Paul
May 26, 2009 at 2:37 am
Yes , I used your suggestions , and the sp I just posted is recompiled 40 000 times a day. And I think this is a problem. I know that the option modifies the threshold of the temp tables making it like for normal tables.And analyzing the profiler, I saw that in all the recompiled statements I had temporary tables, so I'm trying as I can to remove them. I posted the sp because I'm sure that the dynamic sql is the big problem, but I don't think I can remove it. At least I hope to reduce the number of statements that recompile, so any suggestion about rewriting the sp is accepted gladly :-D.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply