Application Taking More Time(Hang)

Question

Application Taking More Time(Hang)

mohan_padekal

Valued Member

Points: 72
More actions
August 8, 2015 at 4:03 am

#306579

We are facing issues in our Coding application and database.Our coding application having front end asp.net 3.5 and back end SQL Server 2008 R2.Based on project requirement we doing fields setting for coding.We facing issues of coding application & database getting more time (hang) during the coding process. Below mentioned testing observation
TestBatchRecordsCoding CompletedQC CompletedUsersFieldsRecord Saving TimeRemarks
1Batch143743743715-2042 - 5 SecNo Issue
2Batch288288288215-2042 - 5 SecNo Issue
3Batch314261426142615-2052 - 5 SecNo Issue
4Batch45761125253115-2012 - 5 SecNo Issue
5Batch5167544835715-2052 - 5 SecNo Issue
6Batch624724724715-2042 - 5 SecNo Issue
7Batch766566566515-2042 - 5 SecNo Issue
8Batch822422422415-202First 2-5 Sec After 200 takes 5 - 10 SecNo Issue
9Batch979025025015-202First 2-5 Sec After 200 takes 5 - 10 SecHanging start
10Batch1057328227315-201First 2-5 Sec After 200 takes 5 - 10 SecHanging start after 250 records
We run the trace and found sp FetchDocumentID taking more time. We execute the sp in SQL Server by selecting Executing plan and not found any missing Indexes.
Below mention Sp structure
CREATE PROCEDURE [dbo].[FetchDocumentID]
(@i_UserId int,
@i_ProjectId int)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Error INT = 0
, @ErrorLine INT
, @ErrorDesc NVARCHAR(1000)
, @Message NVARCHAR(1000)
, @ProcName NVARCHAR(128) = N'Project.dbo.FetchDocumentID.procedure.sql'
, @VALUES VARCHAR(128)
, @UserDetailId BIGINT
, @RoleName VARCHAR(150)
BEGIN TRY
CREATE TAble #Document (Id INT IDENTITY(1,1) PRIMARY KEY
,FieldId INT
, DocumentID INT
, Rejected INT )
IF NOT EXISTS(SELECT UA.AccessId
FROM dbo.UserAccess UA
WHERE UA.ProjectMasterId = @i_ProjectId
AND UA.AccessId = 4
)
BEGIN
INSERT #Document (DocumentID,Rejected )
SELECT TOP 1 [DM].[DocumentId],1
FROM [dbo].[DataMaster] DM WITH(NOLOCK)
LEFT JOIN [dbo].[DocumentMaster] DC WITH(NOLOCK)
ON [DM].[DocumentId] = [DC].[DocumentMasterId]
WHERE [DM].[IsCoded] = 1
AND [DM].[IsRejected]=1
AND [DM].CoderId = @i_UserId
AND [DC].[ProjectId] = @i_ProjectId
AND (([DC].[IsLocked] = 1 and [DC].[Status] = @i_UserId)
OR ([DC].[IsLocked] = 0 AND [DC].[IsChecked] = 1 ))
GROUP BY [DM].[DocumentId]
ORDER BY [DM].[DocumentId]
END
IF NOT EXISTS(SELECT * FROM #Document)
BEGIN
INSERT #Document (FieldId
,DocumentID)
SELECT A.FieldId
, B.DocumentMasterId
FROM dbo.UserAccess A WITH(NOLOCK)
INNER JOIN dbo.DocumentMaster B WITH(NOLOCK)
ON A.ProjectMasterId = b.ProjectId
WHERE A.[UserMasterId] =@i_UserId
AND A.ProjectMasterId=@i_ProjectId
AND (B.IsLocked = 0 OR (B.IsLocked = 1 AND B.[STATUS] = @i_UserId) )
CREATE TABLE #temp1(DocID INT )
INSERT #temp1
SELECT TOP 1 A.DocumentID
FROM #Document A
LEFT JOIN DataMaster b
ON a.FieldId = b.FieldId
AND a.DocumentID = b.DocumentId
WHERE b.DocumentId IS NULL
GROUP BY A.DocumentID
ORDER BY a.DocumentID
if EXISTS(select * from #temp1)
Begin
UPDATE [dbo].[DocumentMaster]
SET [IsLocked] = 1
,[LockedTime] = SYSDATETIME()
,[Status] = @i_UserId
WHERE [DocumentMasterId] = (select DocID from #temp1)
End
SELECT TOP 1 A.DocumentID
, 0 AS Rejected
FROM #Document A
LEFT JOIN DataMaster b
ON a.FieldId = b.FieldId
AND a.DocumentID = b.DocumentId
WHERE b.DocumentId IS NULL
GROUP BY A.DocumentID
ORDER BY a.DocumentID
END
ELSE
BEGIN
UPDATE [dbo].[DocumentMaster]
SET [IsLocked] = 1
,[LockedTime] = SYSDATETIME()
,[Status] = @i_UserId
WHERE [DocumentMasterId] = (select DocumentID from #Document)
SELECT A.DocumentID,Rejected
FROM #Document A
ORDER BY A.DocumentID
END
END TRY
BEGIN CATCH
SELECT @Error = ERROR_NUMBER()
, @ErrorLine = ERROR_LINE()
, @ErrorDesc = ERROR_MESSAGE()
SET @Message = N'Application of the procedure, %s, failed at line, %d, due to error no. %d.: %s'
RAISERROR(@Message, 11, 1, @ProcName, @ErrorLine, @Error, @ErrorDesc) WITH NOWAIT
END CATCH
END_THIS:
RETURN (@Error)
END--FetchDocumentID
Can anyone help me to resolve hang issue.
Thank you in advance

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 1

Can you post the execution plan? On an initial read through the procedure I didn't spot any immediate issues, so it's a question of understanding how each of those actions is getting resolved within the procedure.

"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

mohan_padekal Valued Member Points: 72 More actions · Answer 2

Here is the Execution Plan

<?xml version="1.0" encoding="utf-16"?>

<Batch>

</OutputList>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValues>

</ProbeColumn>

</RunTimeInformation>

</RelOp>

</RunTimeInformation>

</RangeColumns>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</ComputeScalar>

</RelOp>

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

</RunTimeInformation>

</Identifier>

</ScalarOperator>

</Assign>

</Identifier>

</ScalarOperator>

</Assign>

</Identifier>

</ScalarOperator>

</Assign>

</ScalarOperator>

</Assign>

</MultipleAssign>

</ScalarOperator>

</ScalarExpressionList>

</ScalarOperator>

</SetPredicate>

</OutputList>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</ScalarOperator>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</ScalarOperator>

</TopExpression>

</OutputList>

</RunTimeInformation>

</OrderByColumn>

</OrderBy>

</OutputList>

</RunTimeInformation>

</OuterReferences>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</ScalarOperator>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</RunTimeInformation>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Sort>

</RelOp>

</Top>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</Update>

</RelOp>

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

</OutputList>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValues>

</ProbeColumn>

</RunTimeInformation>

</RelOp>

</RunTimeInformation>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</ComputeScalar>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

</RunTimeInformation>

</Identifier>

</ScalarOperator>

</Assign>

</Identifier>

</ScalarOperator>

</Assign>

</Identifier>

</ScalarOperator>

</Assign>

</ScalarOperator>

</Assign>

</MultipleAssign>

</ScalarOperator>

</ScalarExpressionList>

</ScalarOperator>

</SetPredicate>

</OutputList>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</ScalarOperator>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</ScalarOperator>

</TopExpression>

</OutputList>

</RunTimeInformation>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValues>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Top>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</Update>

</RelOp>

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

</RunTimeInformation>

</Identifier>

</ScalarOperator>

</Assign>

</MultipleAssign>

</ScalarOperator>

</ScalarExpressionList>

</ScalarOperator>

</SetPredicate>

</OutputList>

</RunTimeInformation>

</ScalarOperator>

</TopExpression>

</OutputList>

</RunTimeInformation>

</GroupBy>

</OutputList>

</RunTimeInformation>

</OutputList>

</RunTimeInformation>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</OutputList>

</RunTimeInformation>

</OrderByColumn>

</OrderBy>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Sort>

</RelOp>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</Filter>

</RelOp>

</StreamAggregate>

</RelOp>

</Top>

</RelOp>

</Update>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

</OutputList>

<IF>

</Identifier>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValues>

</ProbeColumn>

</RunTimeInformation>

</RelOp>

</RunTimeInformation>

</TableScan>

</RelOp>

</NestedLoops>

</RelOp>

</ComputeScalar>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

</RunTimeInformation>

</Identifier>

</ScalarOperator>

</Assign>

</Identifier>

</ScalarOperator>

</Assign>

</Identifier>

</ScalarOperator>

</Assign>

</MultipleAssign>

</ScalarOperator>

</ScalarExpressionList>

</ScalarOperator>

</SetPredicate>

</OutputList>

</RunTimeInformation>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

<IF>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</ScalarOperator>

</DefinedValue>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</DefinedValue>

<IF>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</OuterReferences>

</OutputList>

</RunTimeInformation>

</OutputList>

</RunTimeInformation>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValues>

</TableScan>

</RelOp>

</StreamAggregate>

</RelOp>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Predicate>

</Assert>

</RelOp>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</Update>

</RelOp>

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RunTimeInformation>

</ScalarOperator>

</TopExpression>

</OutputList>

</RunTimeInformation>

</GroupBy>

</OutputList>

</RunTimeInformation>

</OutputList>

</RunTimeInformation>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</OutputList>

</RunTimeInformation>

</OrderByColumn>

</OrderBy>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Sort>

</RelOp>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</Filter>

</RelOp>

</StreamAggregate>

</RelOp>

</Top>

</RelOp>

</ComputeScalar>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

mohan_padekal (8/10/2015)
Here is the Execution Plan

How about as an attachment instead? I doubt anybody is going to waste their time piecing that back together into something usable.

_______________________________________________________________

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/

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 4

Sean Lange (8/10/2015)
mohan_padekal (8/10/2015)
Here is the Execution Plan
How about as an attachment instead? I doubt anybody is going to waste their time piecing that back together into something usable.

+100

Please, attach the .SQLPLAN file.

"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

sgmunson SSC Guru Points: 110651 More actions · Answer 5

I copied and pasted into Notepad, then saved it as TEST.SQLPLAN. It's attached, and it did successfully open in SSMS for SQL 2008 R2.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 6

Might be a good idea to put an index on your temporary table and ensure that the statistics are up to date. This query is the most costly:

SELECT TOP 1

A.DocumentID

FROM #Document A

LEFT JOIN DataMaster b

ON A.FieldId = b.FieldId

AND A.DocumentID = b.DocumentId

WHERE b.DocumentId IS NULL

GROUP BY A.DocumentID

ORDER BY A.DocumentID;

It's estimating 2 rows, but actually returning over 1000. Then, the nested loop join that is being used to run the scans against the DataMaster table gets executed 1000 times. Also, the query there is somewhat problematic. You're trying to do a JOIN on DocumentID, but, you have a WHERE clause that finds where DocumentID IS NULL. The JOIN is going to be returning some odd data. You have the same problem again i the last query.

"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

sgmunson SSC Guru Points: 110651 More actions · Answer 7

Grant Fritchey (8/10/2015)
Might be a good idea to put an index on your temporary table and ensure that the statistics are up to date. This query is the most costly:
SELECT TOP 1
A.DocumentID
FROM #Document A
LEFT JOIN DataMaster b
ON A.FieldId = b.FieldId
AND A.DocumentID = b.DocumentId
WHERE b.DocumentId IS NULL
GROUP BY A.DocumentID
ORDER BY A.DocumentID;
It's estimating 2 rows, but actually returning over 1000. Then, the nested loop join that is being used to run the scans against the DataMaster table gets executed 1000 times. Also, the query there is somewhat problematic. You're trying to do a JOIN on DocumentID, but, you have a WHERE clause that finds where DocumentID IS NULL. The JOIN is going to be returning some odd data. You have the same problem again i the last query.

Grant,

Would using NOT EXISTS be better than the IS NULL and LEFT JOIN, or would an index, or lack thereof, be the larger factor? Also, given the TOP 1, is GROUP BY serving any purpose at all?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sean Lange SSC Guru Points: 286609 More actions · Answer 8

When is the last time you updated your statistics? The stats on DataMaster seem to be very stale. The plan estimated 1094 rows but found 1.1 Million when it executed.

Try running this (you do have a copy of this on a test server right?).

update statistics DataMaster

I am curious why you seem to just randomly throw the NOLOCK hint in. You seem to have it in some queries but no others even when the queries are looking at the same tables. This is a clear indication that there is a lack of understanding what that hint does. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/%5B/url%5D

_______________________________________________________________

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/

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 9

sgmunson (8/10/2015)
Grant Fritchey (8/10/2015)
Might be a good idea to put an index on your temporary table and ensure that the statistics are up to date. This query is the most costly:
SELECT TOP 1
A.DocumentID
FROM #Document A
LEFT JOIN DataMaster b
ON A.FieldId = b.FieldId
AND A.DocumentID = b.DocumentId
WHERE b.DocumentId IS NULL
GROUP BY A.DocumentID
ORDER BY A.DocumentID;
It's estimating 2 rows, but actually returning over 1000. Then, the nested loop join that is being used to run the scans against the DataMaster table gets executed 1000 times. Also, the query there is somewhat problematic. You're trying to do a JOIN on DocumentID, but, you have a WHERE clause that finds where DocumentID IS NULL. The JOIN is going to be returning some odd data. You have the same problem again i the last query.
Grant,
Would using NOT EXISTS be better than the IS NULL and LEFT JOIN, or would an index, or lack thereof, be the larger factor? Also, given the TOP 1, is GROUP BY serving any purpose at all?

Chances are good that the GROUP BY isn't doing anything in terms of data, but you can see in the plan that it's causing work. Test using NOT EXISTS. It can be better, depending. Stats seem to be way off though. That's causing poor choices in the optimizer.

"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

Eric M Russell SSC Guru Points: 125598 More actions · Answer 10

The following script will return, for each statement in the specified stored procedure:

sql text, last_elapsed_time, last_worker_time (CPU), last_blocked_time (waiting on lock), last_reads, last_writes, last_rows returned, and plan text, ... etc.

For procedures with a large number of statements, you can order by max_elapsed_time easily see which are the bottleneck. For situations where a procedure runs faster on some occasions or circumstances than others, you can order by statement_text, take two samples (one fast and one slow), and then determine the difference between two results by comparing side by side using a text difference tool like WinMerge or Beyond Compare.

SELECT

db_name(qt.dbid)database_name

,OBJECT_NAME(qt.objectid, qt.dbid) object_name

,case when qs.statement_start_offset is not null

then

substring(char(13)+SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,

((

CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset

END

- qs.statement_start_offset)/2) + 1),1,8000)

else qt.text

end as statement_text

,creation_time

,last_execution_time

,execution_count

,convert(varchar,dateadd(ms,(last_elapsed_time / 1000),getdate())-getdate(),108)last_elapsed_time

,convert(varchar,dateadd(ms,(last_worker_time / 1000),getdate())-getdate(),108)last_worker_time

,convert(varchar,dateadd(ms,(last_elapsed_time / 1000) - (last_worker_time / 1000),getdate())-getdate(),108)last_blocked_time

,last_logical_reads

,last_physical_reads

,last_logical_writes

,last_rows

,query_plan

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) qt

cross apply sys.dm_exec_query_plan(qs.plan_handle) qp

-- where qt.text like '%insert%MyTable%'

where OBJECT_NAME(qt.objectid) = 'FetchDocumentID'

--order by (qs.max_elapsed_time) desc;

order by statement_text;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

mohan_padekal Valued Member Points: 72 More actions · Answer 11

Getting this error while execute the script

Msg 207, Level 16, State 1, Line 24

Invalid column name 'last_rows'.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 12

run update stats on the data master table first , it seems to be the root cause as mentioned above. Your optimizer if way off target when estimating the number of rows in that table.

Jayanth Kurup[/url]

mohan_padekal Valued Member Points: 72 More actions · Answer 13

First i updated Statistics of DataMaster table then try to execute the script that time getting error

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 14

mohan_padekal (8/11/2015)
First i updated Statistics of DataMaster table then try to execute the script that time getting error

Updating statistics won't change the names of columns. Have you made other changes to the script or your tables?

"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