SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stored proc occasionally hangs?


stored proc occasionally hangs?

Author
Message
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2920 Visits: 3636
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?
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8593 Visits: 7660
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
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2920 Visits: 3636
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?
kastros.george
kastros.george
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 300
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...
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12429 Visits: 8548
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 on googles mail service
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2920 Visits: 3636
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.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12429 Visits: 8548
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 on googles mail service
gofrancesc
gofrancesc
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 812
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
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2920 Visits: 3636
Thanks everyone.
tfifield
tfifield
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1293 Visits: 2890
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search