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 |
|
mihnea.radulescu
Starting Member
5 Posts |
 Posted - 2011-05-03 : 09:32:16
|
| Hello everybody!My company has a multi-tier solution involving a SQL Server 2008 Express database (with FILESTREAM enabled). There is a data abstraction layer within the DB made of stored procedures, which are called concurrently by multiple clients.We have a stress test application containing 8 threads (clients) which call the same stored procedures sequence, respectively. On SQL Server 2008 Express this always results in deadlocks.As an interesting aspect, using the same database (created by scripts) on SQL Server 2008 R2 Express SOMETIMES results in no deadlocks whatsoever, even running 10 or more consecutive stress tests. Other times (different days), creating the database on R2 from the same scripts and running the same stress test, deadlocks occur in abundance in every run.The reliable solution would be to amend the DB schema and rewrite the data abstraction layer to minimize (or eliminate) deadlocks, but the company is looking for a quick fix.My question is what could cause completely different behavior (resulting in many deadlocks vs. no deadlock at all) on SQL Server 2008 R2 Express, considering the tests, DB schema and data are the same, yet the results are polar opposites.Best regards,Mihnea |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-03 : 09:43:17
|
| Could be something else that is accessing the disks and slowing things down.Fragmentation slowing things downThings running faster due to client network access being fasterThe server running a checkpoint causing blockingDeadlocks are due to things mutually locking on resources so can be sensitive to whatever is going on at the time.R2 will be processing differently - even if it's just the system tasks==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Completely arbitrary deadlock pattern