Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

stored proc occasionally hangs? Expand / Collapse
Author
Message
Posted Saturday, June 4, 2011 10:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991

i have seen some strange behaviour with this sproc...this sproc has bunch of select/insert/ update statements. Occasionally this sproc hangs at one particular place (select statement) and does nothing. There is no blocking at all. When this proc is hung, if i just run the select statement itself results come back very quickly. I am so lost as to why does it occasionally hung at this select statement? Can someone please advice as to where else should i look?
Post #1119921
Posted Sunday, June 5, 2011 2:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:26 PM
Points: 5,446, Visits: 7,614
If the spid isn't blocked, it's in the tempdb spooling. Look for contention on the raid for the tempdb.

I realize that's kind of a leap, but it's the only case I can think of when you can't see the actual hang on the spid. The spid's not hung, but tempdb is being a pain in the butt.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1119927
Posted Sunday, June 5, 2011 8:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991
Craig Farrell (6/5/2011)
If the spid isn't blocked, it's in the tempdb spooling. Look for contention on the raid for the tempdb.

I realize that's kind of a leap, but it's the only case I can think of when you can't see the actual hang on the spid. The spid's not hung, but tempdb is being a pain in the butt.


could because select actually creates a new temp table and inserts data into in it. But the confusing part is that it happens only once in while randomly..how do i see if there is a tempdb pressure going on?
Post #1119955
Posted Monday, June 6, 2011 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:32 AM
Points: 15, Visits: 296
A similar thing was happeing to one of my sql server.
The reason was that the sproc was reading from a huge heap table (no primary key - clustered idnex) a lot of defragmentation. The delay was happening not entirely randomly but on the first 2-3 days of a new month. (the querries were manipulating data based on months)
The sollution was to create a Primary key (This took several hours and for an 100GB table the database grew 100Gb duting the process and afterwards it released those 100GB - internally not to the os , wich means that we needed to Shrink Datab se also afterwards).
In order to troubleshoot , use perfmon to setup some basic counters focusing mostly on I/O read write. We saw that on the days the sproc hang the I/O time was 100% for more than 10 times the average...
Post #1120050
Posted Monday, June 6, 2011 8:09 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 4,436, Visits: 6,338
Get a copy of sp_whoisactive (amazing freebie from Adam Machanic found on sqlblog.com) and check for tempdb usage, IOs, query plan, etc. Parameter sniffing could be at fault here since you mention it runs fast directly from SSMS. PFS/SGAM latching could be to blame if you have tempdb object creation.

Honestly there are 1001 things that could be at fault here.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1120286
Posted Monday, June 6, 2011 9:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991

Thanks everyone. I don't think it is parameter sniffing issue because it doesn't take any parameters at all. Mentioned below is the query where it occasionally hangs.


sELECT Row_number() OVER ( PARTITION BY c.rid ORDER BY HEID ASC, CovStat DESC ) AS ident ,
t.rid ,
t.h_cdr AS cdr ,
c.id AS MCDI ,
t.id AS TempCoverageId ,
c.FPC AS C_FPC ,
pp_a.PYC AS C_PYC ,
t.h_cdr AS C_cdr
INTO #TempTable_2
FROM TMPCOV t
INNER JOIN COV c ON t.rid = c.rid
LEFT OUTER JOIN PPYS pp_a ON pp_a.FPC = c.FPC
LEFT OUTER JOIN PPYS pp_h ON pp_h.FPC = t.h_FPC
LEFT OUTER JOIN TMPCOV tempcovjoin ON c.id = tempcovjoin.MCDI
WHERE t.h_cdr = 2
AND tempcovjoin.MCDI IS NULL
AND ( ( t.h_SubC = c.SubC
AND pp_a.PYC IS NOT NULL
AND pp_a.PYC = pp_h.PYC
)
OR ( c.FPC = t.h_FPC )
OR ( pp_h.ptype = pp_a.ptype
AND pp_a.ptype = 'SLF'
)
)
AND NOT EXISTS ( SELECT *
FROM TMPCOV
WHERE MCDI = c.id )

Also when it hangs it is blocking other processes which use Tempdb. I am leaning more towards something related to tempdb. The indexes look fine from the source tables.
Post #1120343
Posted Monday, June 6, 2011 10:20 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 4,436, Visits: 6,338
sqldba_icon (6/6/2011)

Thanks everyone. I don't think it is parameter sniffing issue because it doesn't take any parameters at all. Mentioned below is the query where it occasionally hangs.


sELECT Row_number() OVER ( PARTITION BY c.rid ORDER BY HEID ASC, CovStat DESC ) AS ident ,
t.rid ,
t.h_cdr AS cdr ,
c.id AS MCDI ,
t.id AS TempCoverageId ,
c.FPC AS C_FPC ,
pp_a.PYC AS C_PYC ,
t.h_cdr AS C_cdr
INTO #TempTable_2
FROM TMPCOV t
INNER JOIN COV c ON t.rid = c.rid
LEFT OUTER JOIN PPYS pp_a ON pp_a.FPC = c.FPC
LEFT OUTER JOIN PPYS pp_h ON pp_h.FPC = t.h_FPC
LEFT OUTER JOIN TMPCOV tempcovjoin ON c.id = tempcovjoin.MCDI
WHERE t.h_cdr = 2
AND tempcovjoin.MCDI IS NULL
AND ( ( t.h_SubC = c.SubC
AND pp_a.PYC IS NOT NULL
AND pp_a.PYC = pp_h.PYC
)
OR ( c.FPC = t.h_FPC )
OR ( pp_h.ptype = pp_a.ptype
AND pp_a.ptype = 'SLF'
)
)
AND NOT EXISTS ( SELECT *
FROM TMPCOV
WHERE MCDI = c.id )

Also when it hangs it is blocking other processes which use Tempdb. I am leaning more towards something related to tempdb. The indexes look fine from the source tables.


1) sp_whoisactive can actually help you find the PFS/SGAM latching issue I mentioned if it is occuring.

2) Not only are you populating a temp table here, you are also doing a SORT for the ROW_NUMBER operation. Double hit. Could be HASHING on the joins for more tempdb hits.

3) ORs are horrible for the optimization process. NOT EXISTS too. Ugly stuff there.

4) Note that SSMS has some different SET values for ANSI crap that can cause significantly different plans from ADO.NET or other connections. You can view these values down in the guts of one of the query execution DMVs. I would actually get the plans for both executions and examine for differences. It is possible you could use a plan guide since there are no parameters to force the optimal plan for every execution.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1120397
Posted Tuesday, June 7, 2011 4:07 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, October 26, 2014 7:16 PM
Points: 634, Visits: 810
Hello sqldba_icon,
seeing your query there are two things focused my attention and looked strange to me:

The NOT EXISTS clause. As pointed by Kevin it could be bad for the optimization process. You can try to substitute it for another LEFT OUTER JOIN; maybe it is done by SQL Server when creating a plan, or maybe don't.

The ROW_NUMBER operator, it implies a SORT over the data result. You create a temp table so you will use it sooner or later, and at first glance I can imagine two scenarios:
- When you use this temp table you need it sorted by c.rid, Row_Number. In this case you will sort the temp table so you don't need to sort your original query, you can calculate this row_number after when needed.
- When you use this temp table you need it filtered by row_number (i.e. Row_number = 1). In this case you can filter data at first, in your original query.

Maybe you use other scenarios but anyway you should detect these scenarios, perceive the type of process you are doing with the temp table, and think about if some process can be optimized in the original query.

Regards,
Francesc
Post #1120836
Posted Tuesday, June 7, 2011 9:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991

Thanks everyone.
Post #1121100
Posted Tuesday, June 7, 2011 11:33 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
sqldba_icon (6/6/2011)

Thanks everyone. I don't think it is parameter sniffing issue because it doesn't take any parameters at all. Mentioned below is the query where it occasionally hangs.


sELECT Row_number() OVER ( PARTITION BY c.rid ORDER BY HEID ASC, CovStat DESC ) AS ident ,
t.rid ,
t.h_cdr AS cdr ,
c.id AS MCDI ,
t.id AS TempCoverageId ,
c.FPC AS C_FPC ,
pp_a.PYC AS C_PYC ,
t.h_cdr AS C_cdr
INTO #TempTable_2
FROM TMPCOV t
INNER JOIN COV c ON t.rid = c.rid
LEFT OUTER JOIN PPYS pp_a ON pp_a.FPC = c.FPC
LEFT OUTER JOIN PPYS pp_h ON pp_h.FPC = t.h_FPC
LEFT OUTER JOIN TMPCOV tempcovjoin ON c.id = tempcovjoin.MCDI
WHERE t.h_cdr = 2
AND tempcovjoin.MCDI IS NULL
AND ( ( t.h_SubC = c.SubC
AND pp_a.PYC IS NOT NULL
AND pp_a.PYC = pp_h.PYC
)
OR ( c.FPC = t.h_FPC )
OR ( pp_h.ptype = pp_a.ptype
AND pp_a.ptype = 'SLF'
)
)
AND NOT EXISTS ( SELECT *
FROM TMPCOV
WHERE MCDI = c.id )

Also when it hangs it is blocking other processes which use Tempdb. I am leaning more towards something related to tempdb. The indexes look fine from the source tables.


I had something like this come up just yesterday that I fixed. It was very strange. The SProc would hang sometimes. I would copy/paste the code from the stored procedure into SSMS and run it in steps and it would run just fine - each step ran in a second or two. This was SQL 2000 so I don't know if this is the same situation as yours.

The logic had a similar LEFT JOIN and a check for SomeColumn IS NULL in the WHERE clause - i.e. finding unmatched records from one table to the other. I changed the LEFT JOIN to a NOT EXISTS in the WHERE clause and it would still hang. Very mysterious. I was under a lot of pressure to just get the thing running so I couldn't fully investigate the phenomenon.

I changed the initial insert into the temp table to just dump everything on the matching criteria. Then I did an INNER JOIN on a DELETE to get rid of the ones that were matched when I wanted only the unmatched records. The procedure ran just fine then - no hang.

I've found that sometimes doing things in steps - divide and conquer - can work better than trying to do it all at once.

I'm still going back to the original LEFT JOIN/WHERE SomeColumn IS NULL and try to figure out why the heck it would hang like that sometimes.

Give it a try and see if it works for you.
Todd Fifield
Post #1121232
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse