Query getting hung

Question

Query getting hung

M&M

SSC-Insane

Points: 21700
More actions
July 11, 2010 at 5:47 pm

#157072

Query getting hung and going to suspended status.
On checking performance dashboard reports, I can see lot of waits due to parallelism.
Only the query I am running is showing under CXpacket WaitType.
The max degree of parallelism on server is configured = 2
Please advise on steps needed to run this query.
Below is the query
UPDATE TableName SET FieldId = @P1,
UpdateUserId = @P2 WHERE RegionDTS = @P3
AND RegionDBCd = @P4
AND RandomId = @P5
AND UpdtateTs = @P6
AND OldValueTax = @P7
AND NewValueTax = @P8
M&M

Viewing 7 posts - 1 through 6 (of 6 total)

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

Leo.Miller SSChampion Points: 13051 More actions · Answer 1

This appears to be a fairly standard and simple update. A few questions I have is: Are any of the columns in the WHERE clause indexed? Particularly the first columns. Another question is are the columns being updated indexed?

Further how big is the table?

If the answer to the first question is NO, then you will get poor performance.

If the answer to the second question is yes, you will also get poor performance as the indexes are being updated.

Have you tried setting MAXDOP down to 1? This would remove the parallelism.

Consider making temporary index changes to facilitate the update.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

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

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

1) you are updating a table without a where clause, meaning you are scanning/updating the ENTIRE table at once. clearly parallelism COULD be good for this. But you have too little RAM, too poor an IO subsystem (or BOTH) and you are thus seeing CXPACKET waits for the parallel effort. You are also blocking the entire table from all access during this operation.

2) if you have a PK or some other column that you can use to batch the updates and get an index seek you could iterate through the table in batches of somewhere between 1000 and 100000 rows at a time (making sure the batch size is small enough to get index seek query plan) and you will get a bunch of wins here. Probably want to put MAXDOP 1 hint in too to prevent all parallelism.

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

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

Thank you all for your inputs. As per application team, parallelism on Informatica code level is not yet introduced in the ETL load job. This query belongs to an ETL job which is run through informatica job.

My doubt is if parallelism is set to 2 on the system, it would be used by all queries right as long there is no maxdop hint in the query itself.

M&M

Leo.Miller SSChampion Points: 13051 More actions · Answer 5

mohammed moinudheen (7/13/2010)
My doubt is if parallelism is set to 2 on the system, it would be used by all queries right as long there is no maxdop hint in the query itself.

Correct, the sp_configure value you see for Max Degree of Parallelism is instance wide and so used by all queries. You can over ride this with a MAXDOP hint.

However, you say this is an ETL job, I assume doing batch loads and updates. If this isn't an OLTP server you may not want to change the MAXDOP settings as the batch processes may suffer.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

Hi

CXPACKET Waits occur due to the high incidence of parallel queries when MAXDOP is enabled on the server.

In OLTP sytems MAXDOP is usually set to 1 to reduce the impact of parallel queries on the system resources.

As you have mentioned that only this query has resulted in CXPACKET waits and if other other queries are not expected to use parallelism, you can disable MAXDOP by setting it to 1 or you can leave it at a value of 0. And you can specify MAXDOP hint within the query to override the default server MAXDOP setting (to parallelize the query)

Usually SQL Server does a good job of deciding whether to use parallel queries or not. (MAXDOP=0)

Even though MAXDOP setting has been enabled at the server level not all queries are parallelized. Only if SQL Server thinks that the cost of the query exceeds the Cost Threshold for parallelism, then SQL server uses parallel queries. Otherwise it would go through serial execution.

Thank You,

Best Regards,

SQLBuddy.