virtual servers users lock each other
-
Hi Friends -thanks in advance for any help you can provid
As shown on the picture above, we have 5 virtual servers and 1 SQL server. (Windows Server 2008 R2 installed.)
Users connect virtual servers via remote desktop.
The problem is that users lock each other when they delete, update or select on servers. I checked sql profiler to see that sql server put the transactions in order. This ordering makes users lock each other.
Is there a setting in the sql server to solve this problem?
-
The (b)locking in SQL is a standard mechanism to ensure data integrity. This isn't a problem as long these blocking actions are short. Depending on the isolation level different actions (like SELECTs) can have influence on the blocking mechanism.
How long are the actions from other users being blocked?
How are the users updating and deleting records?
Are these actions executed through stored procedures?
Are these actions wrapped inside transactions (begin tran...end tran)? If yes: are there no other actions within these transactions?
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
Thanks for the reply.
These actions are performed while using an ERP program which requires very heavy database integration. Before the virtual servers, there were no problems even with hundreds of users. However now just a couple of users blocking each other's activities about five minutes. We hope that there may be a setting about the issue. Users connected to virtual servers via remote desktop block each other while using the ERP program.
-
Can you run sp_who2 to see what is blocking what?
In your trace what events are you looking at?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I'd suggest first understanding why you're experiencing the blocking. You can use sys.dm_exec_query_stats to see what is being blocked. Resolving the issues at the query or index/structure level is the best approach.
But, if you get stuck there, you could look into using one of the snapshot isolation levels. That will reduce the locking experiencing from reads, but not from writes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply