Stored Procedure execution

Question

Stored Procedure execution

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
July 4, 2016 at 7:01 am

#400351

Hi Team,
In one of our prod server one stored procedure is taking long time to execute than normal execution.
It is a select query and fetching the data from other database.
In UAT it is executing within 2 secs but in prod even after 1 hour also its still in process.
I tried to run the select command which is in the stored procedure but it also in process even after 1 hour.
I have updated statistics of both the databases, checked the index level which is good.
I tried to execute the select query using the Option(MAXDOP 1) also not helpful.
when I checked the activity monitor wait type is CXPACKET and wait resource is exchange eventid ----.
Please help me in this
Thank You.
Regards,
Raghavender Chavva

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Use sp_whoisactive to determine how much work has been done, query plan, tempdb usage, blocking, etc. You can also run it in differential mode and see how much work is being done in N seconds.

When you say a different database, is that on the same server or a linked server? If the latter, those can be horribly bad for performance. There are some things that can be done, but each solution (if there is one) depends on the circumstances.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 2

TheSQLGuru (7/4/2016)
Use sp_whoisactive to determine how much work has been done, query plan, tempdb usage, blocking, etc. You can also run it in differential mode and see how much work is being done in N seconds.
When you say a different database, is that on the same server or a linked server? If the latter, those can be horribly bad for performance. There are some things that can be done, but each solution (if there is one) depends on the circumstances.

I have already checked blockings, no blockings. It is showing 0% completed when I checked from dm_exec_requests. Other database is in same instance.

CPU usage and Memory usage is normal.

I tried to see the actual execution plan it not at all popping up even after 1 hour of execution.

I tried to trace it from Profiler also it's showing nothing except task started and existing connection.not more than that.

Thank You.

Regards,
Raghavender Chavva

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 3

I have executed updatestats and updateusage on both the databases.

One observation is it is blocking it self.

I can see n number of processes with same SPID. In that where ever I see CXPACKET wait type, it is bloking it self.

Thank You.

Regards,
Raghavender Chavva

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Raghavender (7/4/2016)
I have executed updatestats and updateusage on both the databases.
One observation is it is blocking it self.
I can see n number of processes with same SPID. In that where ever I see CXPACKET wait type, it is bloking it self.

Can you post the estimated plans as .sqlplan file attachments? For the fast and slow queries. Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

sp_whoisactive has an option to show you the executing query plan. It is a MARVELOUS FREE tool you really should read up on. SQLBlog.com has a 30-day series on it from Adam Machanic (author of the script).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 6

Please find the attachment.

1027.sqlplan is test server where query executing with in 3 sec.

1048.sqlplan is production where we have the issue.

Thank You.

Regards,
Raghavender Chavva

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 7

Raghavender (7/4/2016)

TheSQLGuru (7/4/2016)
It is showing 0% completed when I checked from dm_exec_requests.

sys.dm_exec_requests only shows percent_complete for a limited subset of queries. The full list is on the MSDN documentation for sys.dm_exec_requests, but it's basically DDL-type queries (alter index, DBCC, backup, restore) rather than DML queries (SELECT, UPDATE...)

Thomas Rushton
blog: https://thelonedba.wordpress.com

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 8

TheSQLGuru (7/4/2016)
sp_whoisactive has an option to show you the executing query plan. It is a MARVELOUS FREE tool you really should read up on. SQLBlog.com has a 30-day series on it from Adam Machanic (author of the script).

I have executed sp_whoisactive.

It showing 0 reads, 0 writes, 0 blocking, cpu usage is keep on increasing. No tempdb usage.

FYI: Database is on SQL Server 2014.

Thank You.

Regards,
Raghavender Chavva

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Raghavender (7/5/2016)
Please find the attachment.
1027.sqlplan is test server where query executing with in 3 sec.
1048.sqlplan is production where we have the issue.

Can you please add OPTION(MAXDOP 1) to the query in production and obtain the estimated execution plan? Then - attempt to run it. If it completes execution without you having to go away for coffee, then please capture and post the actual plan for the execution.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 10

ChrisM@Work (7/5/2016)
Raghavender (7/5/2016)
Please find the attachment.
1027.sqlplan is test server where query executing with in 3 sec.
1048.sqlplan is production where we have the issue.
Can you please add OPTION(MAXDOP 1) to the query in production and obtain the estimated execution plan? Then - attempt to run it. If it completes execution without you having to go away for coffee, then please capture and post the actual plan for the execution.

I tried that option also, but no use.

Thank You.

Regards,
Raghavender Chavva

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Raghavender (7/5/2016)
ChrisM@Work (7/5/2016)
Raghavender (7/5/2016)
Please find the attachment.
1027.sqlplan is test server where query executing with in 3 sec.
1048.sqlplan is production where we have the issue.
Can you please add OPTION(MAXDOP 1) to the query in production and obtain the estimated execution plan? Then - attempt to run it. If it completes execution without you having to go away for coffee, then please capture and post the actual plan for the execution.
I tried that option also, but no use.

Sure but can you please post the estimated plan? Thanks.

There are some differences between the two platforms - indexes, data types.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 12

Please find the attachement with MAXDOP 1

Thank You.

Regards,
Raghavender Chavva

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Can we have an actual plan from the fast query please? Thanks.

Here are some suggested improvements for your WHERE clause:

WHERE UD.EmployeeType IN (0,1)

AND NL.NetworkLogonId LIKE 'UD1\%'

--AND SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1) = 'UD1'

--AND UD.EmployeeID IS NOT NULL -- redundant, satisfied by inner join above

AND UD.HasLeft = 0

AND UD.DeletedDate IS NULL

AND (

(UD1.OU ='Contacts' AND NL.IsPrimaryLogon=1)

OR

UD1.OU ='Users'

--AND SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1) ='UD1' -- this already exists as a filter

)

--AND LEFT(SUBSTRING(NL.NetworkLogonId, CHARINDEX('\', NL.NetworkLogonId)+1, LEN(NL.NetworkLogonId)),1) <> '$'

AND SUBSTRING(@NetworkLogonId, CHARINDEX('\', @NetworkLogonId)+1, 1) <> '$'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 14

Thank You Chris. You and Me are on same page. 🙂

I was dividing the query and trying to execut it.

After changing the logic to

AND NL.NetworkLogonId LIKE 'UD1\%'

query completed with in 3 sec.

Thank You.

Regards,
Raghavender Chavva