mrdenny
Programmer
- May 27, 2002
- 11,595
I'm fairly sure that this can't be done, but I need confirmation. Correction would be better.
I have an application that is causing major blocking because the select statements take such a long time to complete. Some up to 20 minutes. It's all backend processes that take this long to process, so I'm not consirned about the time. The tables that it's searching and joining are all multi-million record tables.
The problem is that the long running queries are causing blocking to user executed queries, making the other processes sit there and wait for a select statement to complete before returning data to another select statement.
I know that I can issue set ISSOLATION LEVEL commands at the session level, but I'm trying to find out if there is a way to get the same result at the server level. I'm trying to get the vendor to put WITH (NOLOCK) in all the queries, but in case they won't, I'd like to have another option available to me.
Perhaps someone has heard of a way to change the default Issolation level as the connection is started?
Thanks as always.
Denny
Between the ESP=ON and the RUM (Read Users Mind) upgrade, I'm ready to go.
I have an application that is causing major blocking because the select statements take such a long time to complete. Some up to 20 minutes. It's all backend processes that take this long to process, so I'm not consirned about the time. The tables that it's searching and joining are all multi-million record tables.
The problem is that the long running queries are causing blocking to user executed queries, making the other processes sit there and wait for a select statement to complete before returning data to another select statement.
I know that I can issue set ISSOLATION LEVEL commands at the session level, but I'm trying to find out if there is a way to get the same result at the server level. I'm trying to get the vendor to put WITH (NOLOCK) in all the queries, but in case they won't, I'd like to have another option available to me.
Perhaps someone has heard of a way to change the default Issolation level as the connection is started?
Thanks as always.
Denny
Between the ESP=ON and the RUM (Read Users Mind) upgrade, I'm ready to go.
