April 11, 2016 at 5:32 am
I have a stored procedure (ApsSchedularCompletdSp) which we run everyday. Most of the time this procedure runs indefinitely.
I have attached the details of Activity Monitor here.
http://screencast.com/t/4bvOeyHIMaL
The command is INSERT.
The procedure we follow to have this procedure runs properly in a time frame is.
Reindex Job, Jobroute, job_sch, jrt_sch table.
Most of the time when we reindex Job table it gets stuck and we have to kill the blocking process and reindexing finishes.
After reindexing when we run the our SP ApsSchedularCompletdSp, it gets completed in couple of minits.
following are the INSERT Statements in the Stored Procedure. Any INSERT statement which is using job table maybe causing the problem.
---------------------------------------------------------------------------------------------------------
insert into @PlanJob
select job, suffix, item,
dbo.ApsPlannerNeedsBom(job,suffix),
dbo.ApsPlannerNeedsRoute(job,suffix)
from job with (nolock)
where
job.stat not in ('H','C')
and job.type not in ('S','P')
--------------------------------------------------------------------------------------------------------------
insert into TrackRows (
SessionId, RowPointer, TrackedOperType)
select
@Partition
,jobmatl.rowpointer
,'Sync jobmatl'
from jobmatl WITH (READUNCOMMITTED)
join @PlanJob planjob on
planjob.job = jobmatl.job
and planjob.suffix = jobmatl.suffix
and planjob.bom = 1
where not exists( select * from TrackRows where SessionId = @Partition and RowPointer = jobmatl.RowPointer)
--------------------------------------------------------------------------------------------------------------
insert into TrackRows (
SessionId, RowPointer, TrackedOperType)
select
@Partition
,jobroute.rowpointer
,'Sync jobroute'
from jobroute with (nolock)
join @PlanJob planjob on
planjob.job = jobroute.job
and planjob.suffix = jobroute.suffix
and planjob.route = 1
join job with (nolock) on job.job = jobroute.job and job.suffix = jobroute.suffix
join item WITH (READUNCOMMITTED) on item.item = job.item and item.mrp_part = 0
where not exists( select * from TrackRows where SessionId = @Partition and RowPointer = jobroute.RowPointer)
---------------------------------------------------------------------------------------------------------------------------------------------------
insert into TrackRows (
SessionId, RowPointer, TrackedOperType)
select
@Partition
,job.rowpointer
,'Sync job'
from job with (nolock)
where not exists( select * from TrackRows where SessionId = @Partition and RowPointer = job.RowPointer)
and job.type not in ('S', 'P')
and job.stat in ('F', 'R', 'S')
--------------------------------------------------------------------------------------------------------------------------------------------------
Insert into TrackRows (
SessionId, RowPointer, TrackedOperType)
select
@Partition
,jobitem.rowpointer
,'Sync jobitem'
from jobitem with (nolock)
join job with (nolock) on
job.job = jobitem.job
and job.suffix = jobitem.suffix
where not exists( select * from TrackRows where SessionId = @Partition and RowPointer = jobitem.RowPointer)
and job.type not in ('S', 'P')
and job.stat not in ('C', 'H')
------------------------------------------------------------------------------------------------------------------------------------------------------------
April 11, 2016 at 5:55 am
Here is the code for the Functions used in first INSERT statement
ALTER FUNCTION [dbo].[ApsPlannerNeedsBom] (
@pJob JobType
,@pSuffix SuffixType
)
RETURNS Flag
AS BEGIN
DECLARE @f Flag
-- Jobs for which we need boms
SET @f =
CASE
WHEN EXISTS(
SELECT *
FROM job WITH (READUNCOMMITTED)
JOIN item WITH (READUNCOMMITTED) ON item.item = job.item
WHERE
job.job = @pJob
AND job.suffix = @pSuffix
-- Only create a bom if we will actually use it
AND job.type <> 'S'
AND job.stat NOT IN ('C', 'H')
)
THEN 1
ELSE 0
END
RETURN @f
END
GO
_------------------------------------------------------------------------------------------------------------------------------
ALTER FUNCTION [dbo].[ApsPlannerNeedsRoute] (
@pJob JobType
,@pSuffix SuffixType
)
RETURNS Flag
AS BEGIN
DECLARE @f Flag
/* NOTE!!! Changes to this SP should also be correspondingly done to ApsResyncRouteOprSp */
-- Jobs for which we need routes
SET @f =
CASE
WHEN EXISTS(
SELECT *
FROM job WITH (READUNCOMMITTED)
JOIN item WITH (READUNCOMMITTED) ON item.item = job.item AND item.mrp_part = 0
WHERE
job.job = @pJob
AND job.suffix = @pSuffix
-- Only create a route if we will actually use it
AND (job.qty_released - job.qty_complete - job.qty_scrapped > 0 OR job.type = 'P')
AND job.type NOT IN ('S')
AND job.stat NOT IN ('C', 'H')
)
THEN 1
ELSE 0
END
RETURN @f
END
GO
April 11, 2016 at 6:09 am
Why Readuncommitted? Is incorrect data acceptable here?
Try changing the table variable to a temp table, see if that gets you any improvement.
Also see if you can get rid of those scalar functions and write the CASE in-line in the query. Scalar functions used in other queries are very slow.
Can you post execution plans?
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
April 11, 2016 at 6:50 am
Why Readuncommitted?
- I don't know. The code is a part our ERP system which came from our vendor.
Is incorrect data acceptable here?
- I don't know what that means.
Try changing the table variable to a temp table, see if that gets you any improvement.
- I can try that
Also see if you can get rid of those scalar functions and write the CASE in-line in the query. Scalar functions used in other queries are very slow.
- I can try that
Can you post execution plans?
- Execution plan says. Query Cost 'Relative to batch) : 50%
April 11, 2016 at 7:21 am
The code posted seems to have both READUNCOMMITTED uncommitted and NOLOCK thrown around all over the place. I realize some of this is vendor code but you should read up about those two hints (they are really the same thing). Queries using those hints can and will return missing and/or duplicate rows along with a host of other unique challenges. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2016 at 7:24 am
skb 44459 (4/11/2016)
Can you post execution plans?
- Execution plan says. Query Cost 'Relative to batch) : 50%
Stating the relative cost of the batch when compared to the contents of the actual execution plan is like asking somebody to read you a book and you only read the title. You need to capture the execution plan and post it. It has tons of valuable information. Take a peek at this article for things you should post for performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2016 at 11:16 am
Sean Lange (4/11/2016)
skb 44459 (4/11/2016)
Can you post execution plans?
- Execution plan says. Query Cost 'Relative to batch) : 50%
Stating the relative cost of the batch when compared to the contents of the actual execution plan is like asking somebody to read you a book and you only read the title. You need to capture the execution plan and post it. It has tons of valuable information. Take a peek at this article for things you should post for performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
I don't even think that's the books title. More like the genre & nothing else.
There just isn't enough with simple query listings to go on to make suggestions for performance improvement.
"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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply