Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thumsup9
Starting Member
7 Posts |
 Posted - 2009-03-05 : 16:16:10
|
| Two sql statements involving common base tables are combined using a UNION.This query is generated by business objects and the reports worked for sometime. Recently, we started seeing the following error when the report is run against Production DatamartServer: Msg 1205, Level 13, State 2, Line 1Transaction (Process ID 121) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.I copied the Query and ran it in Development which has approx same numbers of rows in PROD and it is working there but when ran in Production it gives me the same error as above.I changed the UNION to UNION ALL and ran the query in PROD, it runs fine.I ran the two sql statements individually and they run fine.Only when they have UNION, it doesnt work.I am assuming that if UNION ALL is working, then it should not be a table lock issue. Can it be a performance issue beacuse the UNIOn removes duplicates. The DBA's say that its the query issue which doesnt seem right, Any thoughts.Thanks, |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-03-05 : 16:40:20
|
| well the easiest thing you can do is to trace the deadlock event in the profiler and see what acctually deadlocks with what.i doubt that union would cause deadlock as they don't take any X or IX locks and they do their duplicate removal in memory or in tembdb if the set doesn't fit in memory.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-03-06 : 11:22:43
|
| "union" involves a specific SORT to eliminate duplciates."union all".... involves no sortingI'd suggest the locking/performance issue is on your tempdb..where sort files are held. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2009-03-06 : 17:55:10
|
does it really deadlock every time? that's neat! whenever I have seen deadlocks they are always hard to repro, involving multiple simultaneous requests, not a single standalone query.
elsasoft.org |
|
|
|
|
|
|
UNION vs UNION ALL