December 12, 2015 at 5:04 pm
Hi Guys,
There is a scalar function inside a stored procedure. When the proc is called once, this function is executed multiple times (execution_count on one SP call is close to 2 Million) and the procedure overall execution time is taking longer and the cpu usage is high when this SP is called multiple times during the same time.
Tried to simulate this issue on a different environment with one day old data and the execution count of the function in one procedure call is 4500 which is much different.
The associated tables called from the function has no fragmented indexes and no outdated stats.
Cleared the plan cache/recompile the proc/function didn't help. Also cleared the procedure cache by running DBCC FREEPROCCACHE which didn't help either. Sometimes after running DBCC FREEPROCCACHE the proc execution is faster.
Can you guys share your thoughts on how to troubleshoot this.
Many thanks in advance!
December 12, 2015 at 5:22 pm
Someone is going to have to rewrite the proc to use iTVFs instead of scalar functions. It's the only way other than using something like a Temp Table to store the data using a single lookup that the scalar functions are calling.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2015 at 5:38 pm
thanks Jeff. We happened to see this issue after sql server was migrated from 2008 to 2014 version.
December 12, 2015 at 7:40 pm
Different plans can have different executions.
Take a look at the SQL Server MVP Deep Dives 2 book - GREAT resource on LOTS of useful topics! The best chapter in that book is entitled "Death by UDF", written by yours truly. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 12, 2015 at 8:24 pm
SQL!$@w$0ME (12/12/2015)
thanks Jeff. We happened to see this issue after sql server was migrated from 2008 to 2014 version.
Did you rebuild indexes and stats after the migration?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2015 at 9:09 pm
Yes it was done. It's been a while migration was done.
December 13, 2015 at 2:21 am
Jeff Moden (12/12/2015)
Someone is going to have to rewrite the proc to use iTVFs instead of scalar functions. It's the only way other than using something like a Temp Table to store the data using a single lookup that the scalar functions are calling.
+100
😎
Jeff is spot on, either rewrite the function as iTvfn or incorporate the logic in the stored procedure. I've seen the likes of 99.99% reductions several times by doing such changes, I.e. from 60 sec. for 50K rows down to 120ms.
December 13, 2015 at 8:10 pm
SQL!$@w$0ME (12/12/2015)
thanks Jeff. We happened to see this issue after sql server was migrated from 2008 to 2014 version.
Unfortunately, that doesn't change my recommendation.
Do you have any idea if this problem was beginning to fester on the 2008 box? I strongly suspect it was and some changes in the optimizer between 2008 and 2014 pushed it over the edge. Either that or someone changed something during the migration.
What does the function do and is it only used by the one stored procedure in question? Any chance of posting the code for the function and a snippet or two from the stored procedure where it's used?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2015 at 11:54 am
Thanks Jeff. This was not causing a performance issue when the same code was running on 2008.
This particular function is used only in one stored procedure.
Here is the tsql:
--FUNCTION
create Function [dbo].[Fn_CsStatus]
(
@CSID Int
)
Returns bit
AS
Begin
Declare @OpenStatus bit
Declare @StartDate Datetime, @OpenDate Datetime, @AdjustDate Datetime
Declare @StartStatusDate Datetime, @OpenStatusDate Datetime, @AdjustStatusDate Datetime
Set @OpenStatus = 0
Select @OpenDate = OpenReceivedDate,@OpenStatusDate = OpenStatusDate,
@StartDate = StartReceivedDate, @StartStatusDate = StartStatusDate,
@AdjustDate = AdjustReceivedDate, @AdjustStatusDate = AdjustStatusDate
From dbo.CaseStatusRev
Where Id = @CaseID
if Not @StartDate Is null and @StartStatusDate Is Null Begin
Set @OpenStatus = 1
End else begin
If Not @AdjustDate Is null and @AdjustStatusDate Is Null Begin
Set @OpenStatus = 1
End Else Begin
If Not @OpenDate Is Null and @OpenStatusDate Is Null Begin
Set @OpenStatus = 1
End
End
end
Return @OpenStatus
End
--STORED PROC (part where the function is called)
Update EmpOpenCases Set
EmpOpenCases.RevOpen = Result.RevOpen
From (
Select EmpID, Sum(RevOpen) As RevOpen
From (
Selectemployee.id As EmpID,
Count(*) As RevOpen
From Information
Inner Join dbo.crinfo On Information.cid = dbo.crinfo.cid
Inner Join dbo.CaseStatusRev On dbo.CaseStatusRev.RequestId = dbo.crinfo.RequestId
Inner Join employee On Information.casemgruid = employee.id
Left Join @empVac AS EmpVacations On Information.cid = EmpVacations.Cid
Inner Join @branches As Branches On employee.locID = Branches.LocID
Where Information.casestatus = 'O'
And dbo.Fn_CsStatus(dbo.CaseStatusRev.Id) = 1
And(Coalesce(EmpVacations.CoverageManagerId, 0) = 0 Or EmpVacations.CoverageManagerId = employee.id)
Group By employee.id
) Result
Group By EmpID
) Result
Inner Join @EmpOpenCases As EmpOpenCases
On Result.EmpID = EmpOpenCases.EmpID
Many thanks.
December 15, 2015 at 10:50 am
would this work?
CREATE FUNCTION dbo.TVF_CsStatus
(
@CSID INT
)
RETURNS table
Return
SELECT Case WHEN ( StartReceivedDate IS NOT NULL AND StartStatusDate IS NULL)
OR ( AdjustReceivedDate IS NOT NULL AND AdjustStatusDate IS NULL)
OR ( OpenReceivedDate IS NOT NULL AND OpenStatusDate IS NULL) THEN 1 ELSE 0 END as CaseStatusID
FROM dbo.CaseStatusRev
WHERE Id = @CSID
go
--STORED PROC (part where the function is called)
UPDATE EmpOpenCases
SET EmpOpenCases.RevOpen = Result.RevOpen
FROM
(
SELECT EmpID, SUM(RevOpen) AS RevOpen
FROM
(
SELECT employee.id AS EmpID, COUNT(*) AS RevOpen
FROM
Information
INNER JOIN dbo.crinfo ON Information.cid = dbo.crinfo.cid
INNER JOIN dbo.CaseStatusRev ON dbo.CaseStatusRev.RequestId = dbo.crinfo.RequestId
INNER JOIN employee ON Information.casemgruid = employee.id
LEFT JOIN @empVac AS EmpVacations ON Information.cid = EmpVacations.Cid
INNER JOIN @branches AS Branches ON employee.locID = Branches.LocID
cross apply dbo.TVF_CsStatus(dbo.CaseStatusRev.Id) CSStatus
WHERE Information.casestatus = 'O'
AND CSStatus.CaseStatusID= 1
AND (COALESCE(EmpVacations.CoverageManagerId, 0) = 0
OR EmpVacations.CoverageManagerId = employee.id)
GROUP BY employee.id
) AS Result
GROUP BY EmpID
) Result
INNER JOIN @EmpOpenCases AS EmpOpenCases ON Result.EmpID = EmpOpenCases.EmpID;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply