Tuning on parallel processing

Question

Post reply

Tuning on parallel processing

sql_butterfly

Ten Centuries

Points: 1264
More actions
February 5, 2011 at 3:21 am

#244717

We have a stored procedure which is doing inserts, updates and deletes into and from many big tables. There are 20-30 parallel processings for the same sproc. What should I take into considerations to get rid of performance bottlenecks as well as deadlocks.

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

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

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

That's a pretty big question. Entire books have been written on the subject.

Start with these

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Then try Grant's book

http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025/ref=sr_1_3?ie=UTF8&qid=1296912716&sr=8-3

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

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 2

Hello Gail, I read both the links and they are quite useful. However my main problem is little different. We are using one procedure parallely multiple times (around 30 times parallely) with different set of parameters. In the last section there is an update statement which causes blocking. Can you please suggest something ?

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

What are you looking for?

The principal is the same, find the offending code and fix it. The two links should help you with the first, Grant's book with the second.

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

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 4

I checked all almost all of the things. Everything looks fine to me. However there is one update which affects around one thousand rows (on an average) on a table. When many sessions execute the same procedure at the same time, it results in deadlock. I am thinking of using Snapshot isolaton.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

sql_butterfly (2/13/2011)
I checked all almost all of the things. Everything looks fine to me. However there is one update which affects around one thousand rows (on an average) on a table. When many sessions execute the same procedure at the same time, it results in deadlock. I am thinking of using Snapshot isolaton.

We can speculate an awful lot without ever getting to the problem. Any chance of you posting the code and maybe an "Actual Execution Plan"?

--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)

srikant maurya Hall of Fame Points: 3293 More actions · Answer 6

Try to access the table same order in each procedure and use NOLOCK hint to avoid the sahred lock(while access the table ). It may help some exetend

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

srikant maurya (2/14/2011)
Try to access the table same order in each procedure and use NOLOCK hint to avoid the sahred lock(while access the table ). It may help some exetend

It may also give incorrect and inconsistent data. Will make no difference to an update.

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

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 8

I'm planning to use SET TRANSACTION ISOLATION LEVEL SNAPSHOT before update. Will it help me avoid deadlocks ?

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 9

srikant, No issues of different order usage of tables, since we are using same procedure in different sessions (only with different parameteres)

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

sql_butterfly (2/14/2011)
I'm planning to use SET TRANSACTION ISOLATION LEVEL SNAPSHOT before update. Will it help me avoid deadlocks ?

Yes. Just make sure that your code can handle and retry in the case of update conflicts.

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

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 11

That is something which can be achieved using jobs. However I have to work withing codings to avoid deadlocks. I am stuck and not getting any solution.

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

under snapshot isolation you won't get deadlocks. However you can get update conflicts which will, similar to a deadlock, result in a transaction getting rolled back.

If you want tuning help, post the deadlock graphs, table definitions, index definitions and execution plan.

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

M&M SSC-Insane Points: 21700 More actions · Answer 13

You would need to take note of Gail's inputs. Also, just wanted to point out that snapshot isolation would be making use of tempdb. You need to plan accordingly.

M&M

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 14

Gail, Isn't Conflict detection present in Snapshot isolation ? Apart from that, is there any way by which we can neglect deadlocks ? Using some locking hints or something ? (Sorry for being so harsh on this as I think indexes are good enough on the tables as per my investigations)