How to prevent locks/delays when querying the same tables from different processes?

I have an issue that people can't work with an intranet application that is querying the same tables that i'm querying from SQL Server Management Studio. They must wait until the query finished what can last ~10 minutes.

I've already reduced the deadlock-priority of SSMS but that seems to have no effect on the delays now.

I would think it should be possible that SQL-Server could handle both queries parallel or at lea开发者_如何转开发st have an option to reduce SSMS' priority. So is there any way/option to ensure that some processes like w3sp.exe get high-priority and other "internal"(SSMS related queries) get low-priority in processor time? The server wasn't busy at all, only 6% CPU was used, why is this so? Also, why does SQL-Server seem to lock tables that are queried without any changes(updates/deletes). Can i avoid that?

Thank you in advance.

It's possible that you could reduce the transaction isolation level for some/all of the read-only operations being performed by either or both of the intranet application and your SSMS queries. But if you do this, you have to be wary of such things as dirty reads (where you read one or more rows from a table and these rows later turns out not to be committed by their owning transaction).

There are no priority level settings for connections within SQL Server (other than, as you've noted, volunteering to be the deadlock victim). OS level settings (e.g. process priorities) will have no effect on SQL Server - all it cares about are locks, and whether these locks are compatible between different connections.

You can use the SET TRANSACTION ISOLATION LEVEL statement to change your isolation at the connection level, or you can use locking hints (such as WITH NOLOCK) on individual tables within statements to have more granular control over what locks are being taken.

Note, though, that if you're running DML statements (e.g. INSERT or DELETE), these will still need to take exclusive locks, so if your intranet application wishes to query the same tables, it had to wait for the DML statement to complete, or it has to be modified to relaz its isolation. There's no means to specify the behaviour of other connections from your own queries - they have to choose their own isolation settings.