Deadlock on TempTable in SP

Question

Deadlock on TempTable in SP

jayoub

SSCertifiable

Points: 6991
More actions
July 13, 2017 at 5:31 pm

#343525

I was asked to help a developer and was not given much information, so i hope you can help out
Using SQL Server 2014
The developer re wrote a stored procedure to use #temp tables instead of @variable to store some temp data. He said there is no updating of any regular tables in the database. For some reason he is getting deadlocks now when users are trying to use the application. He says its only when two or more people execute the same thing at the exact same time.
When using the @variable this problem did not exist and he decided to use the #temp table for better performance. Any help would be appreciated.
I looked at the stored procedure and found that he is using a Cursor with a join select statement to fill the temp table which I think is weird because he could probably use a set result query - select ...into #temp and be better off.
I would like to recommend something, so any feedback is appreciated.
Jeff

Viewing 11 posts - 1 through 10 (of 10 total)

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

peacesells SSCrazy Points: 2882 More actions · Answer 1

I recommend using temp table over table variable when you are dealing with larger data sets for performance reasons. There are some advantage you have with temp table over table variable. Recommend not using cursor unless its absolutely necessary have a row by row processing. Also whats the query timing? any indexes to tune the query?

jayoub SSCertifiable Points: 6991 More actions · Answer 2

jayoub

SSCertifiable

Points: 6991

July 13, 2017 at 7:20 pm

#1950865

Thanks for the reply.

Jeff

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

It's hard to say much from the little detail posted, honestly.

Any chance we can the deadlock graph or code?

Cheers!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Post the deadlock graph please.

The deadlock can't be on the temp table (baring a bug in SQL), as those are per-session, each connection has its own copy of the temp table.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jayoub SSCertifiable Points: 6991 More actions · Answer 5

Thanks again for the help. i will work with the developer and see if there is more information.

Jeff

ScottPletcher SSC Guru Points: 101091 More actions · Answer 6

If he is actually doing a SELECT ... INTO #temp, that could cause blocking, perhaps long blocking, but it normally wouldn't cause true "deadlocks". Is it an actual deadlock or just (very) long blocking?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jayoub SSCertifiable Points: 6991 More actions · Answer 7

I spoke to the developer and he want to deal with by adding the WITH NOLOCK in several places in the script and will be reverting back to the @variable tables.

As for the Cursor he understood my recommendation to use a set query instead, but said the SP was using that before and for some reason it was very slow, so converted it to a cursor.

Thanks for the help

Jeff

ScottPletcher SSC Guru Points: 101091 More actions · Answer 8

Table variables are almost always much slower than temp tables. I wouldn't use table variables unless I knew it was only 1 or 2 rows, ever, period, or I had to have the data past a ROLLBACK (such as error info, etc.).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

jayoub - Friday, July 14, 2017 10:39 AM
I spoke to the developer and he want to deal with by adding the WITH NOLOCK in several places in the script and will be reverting back to the @variable tables.
As for the Cursor he understood my recommendation to use a set query instead, but said the SP was using that before and for some reason it was very slow, so converted it to a cursor.
Thanks for the help

Heh... the sp was very slow so you folks converted it to a cursor, which will likely make things slower. Temp db is "deadlocking" and you don't know why. Now you're using WITH NOLOCK and are reverting back to Table Variables the latter of which is likely a part of the original cause of slowness. You folks need to slow down and stop jabbing at stuff.

Let's start at the beginning. What part of the stored procedure was slow and what was causing that part to be slow? I'm pretty sure that you good folks have no clue about the second part so stop writing code to try to fix it until you actually know what's wrong and how to fix it. 😉

Please see the second link under "Helpful Links" in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

jayoub - Friday, July 14, 2017 10:39 AM
I spoke to the developer and he want to deal with by adding the WITH NOLOCK in several places in the script and will be reverting back to the @variable tables.

So he wants to replace deadlocks with incorrect results and slow queries instead of fixing it. Sounds like a great plan!

for some reason it was very slow, so converted it to a cursor.

"The query is slow. Hmm, what to do? I know, use a slower and less efficient method!"

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass