Tuning on parallel processing

Question

Tuning on parallel processing

Viewing 15 posts - 16 through 30 (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

sql_butterfly (2/14/2011)
Gail, Isn't Conflict detection present in Snapshot isolation ?

Yes, which is why you will get an error is there is an update conflict. Detection, not handling. You have to handle them

http://msdn.microsoft.com/en-us/library/ms188277.aspx

Snapshot isolation only (which is what your isolation level statement would give), not read committed snapshot

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)

Sorry for being blunt, but if you have deadlocks they are probably not. The vast majority of deadlocks are due to poorly written code or inadequate or inapproriate indexes.

If you are absolutely sure that you know better than SQL Server under all circumstances and possible conditions then go ahead and apply hints. Just be sure that you are sure first.

(p.s. I think you meant 'negate deadlocks'. Neglect means to leave alone, to not pay sufficient attention to, etc)

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

Gail, I read your article again and again. That's interesting but still I'm not able to get solution. Can you check for example the attached execution plan and Statistics and provide some inputs ?

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

Table and index definitions please.

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

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

sql_butterfly (2/14/2011)
Gail, I read your article again and again. That's interesting but still I'm not able to get solution. Can you check for example the attached execution plan and Statistics and provide some inputs ?

1. The use of UPPER in your "IN" joins will make it impossible to achieve an INDEX SEEK. Unless your database (or the columns involved) is set to "case sensitive", UPPER is simply not needed in SQL Server which defaults to "case insensitive".

2. Post the code for the FN_OGRE_SplitString function. If it's like most split functions I've seen, it has at least 1 major performance problem in it.

3. The following code makes it impossible to do an INDEX SEEK on the "B" table alias.

AND A.TaskId = B.DomainName+'-'+CONVERT(VARCHAR,B.COBDate,112)+'-'+CONVERT(VARCHAR,RunNumber)

4. The use of DISTINCT in a COUNT indicates a possible many-to-many situation which will can cause many unnecessary internal rows to be generated taking addition CPU and I/O resources.

5. I'm not sure what the intent of the following code is but I suspect that it's also responsible for a many-to-many join. I suspect that one side or the other should reference B.COBDate.

ON A.COBDate = A.COBDate

My recommendation would be to fix those things and if it's still a performance problem, then repost the things that Gail asked for including the table and index definitions.

Don't forget to post your splitter function code so we can have a go at that, as well.

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

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 5

1. Database is Case Sensitive.

2. Function definition is attached.

3. We have to equate the condition that way.

4. I'll check that out.

5. It's A.COBDate= B.COBDate (I'll rectify that)

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 6

Snapshot isolation didn't worked. It seems it does not work in a sproc where update statement is defined.

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

Define 'didn't worked'

Snapshot isolation works fine with all DML statements.

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

First enabled ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON. Then inside sproc mentioned SET TRANSACTION ISOLATION LEVEL SNAPSHOT. While running it shows error saying "Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation."

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

A simple update would not give you that. There must be something else in the procedure that's causing that error.

Post the full error message and check what's on the line that the error message refers to (if it does)

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 10

This is the update statement within the sproc which is getting stuck and creating deadlock (when run parallely in various sessions)

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 11

sql_butterfly

Ten Centuries

Points: 1264

February 15, 2011 at 6:04 am

#1286993

Any help ?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

sql_butterfly (2/15/2011)
Any help ?

You have a very complex situation here and you have been hunting-and-pecking on a forum for 10 days now. Way past time to hire a performance tuning professional to help resolve your problem - probably within a matter of hours to a day or so. There are significant issues with your code and possibly design and probably indexing.

Failing that, try reading this blog series: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

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

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

sql_butterfly (2/15/2011)
Any help ?

Did you make the corrections I suggested?

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

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

Jeff Moden (2/15/2011)
sql_butterfly (2/15/2011)
Any help ?
Did you make the corrections I suggested?

Sorry... missed the post where you said you made the changes.

I looked at the splitter function you posted and, yes, it has two major performance problems.

1. It contains a WHILE loop.

2. It's a "multi-line Table Valued Function" which is as bad as using a scalar function.

Are you interested in a redaction of the code for the function which may also require you to change the way you use it?

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

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

sql_butterfly (2/15/2011)
This is the update statement within the sproc which is getting stuck and creating deadlock (when run parallely in various sessions)

Update? Where's the code for the UPDATE? The only code I've seen so far is the SELECT code you attached that I made some recommendations to change.

You really need to post the code that's actually causing the problem especially if it's an UPDATE. There's a potential problem with UPDATEs that many people don't realize which can cause an effect known as "Halloweening" that will cause a 2 second update to slam multiple CPU's into the wall for several hours.

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