0
Opa
I have an OLAP environment where there are reports that consume information from it. But lately I have picked up some LOCKS in the environment for some old procs are not "properly" elaborated.
I know that applying READ UNCOMMITED and WITH(NOLOCK) in the PROCS where they are being used , the competition problem will decrease.
Knowing that ISOLATION LEVEL is set by DEFAULT ( SQL SERVER ), there is some risk of I set it as ON in productive environment ?
For the procedures that are in the reports, is there any ISOLATION level option more appropriate for them ? Knowing that they consume information already loaded ?
And there is possibility of I find a situation where PROC is in READ_COMMITED and have tables using the hint WITH(NOLOCK) ?
I am using SQL SERVER 2014
But you put in your reports transaction data not yet committed is not reckless?
– anonimo
@Anonimo in this case I would have to assume that all my Procs that feed my OLAP environment would be using a right BEGIN TRAN ?
– Luiz
Any modifications, whether insertions or changes, should be in transactions. But I can only guess because I don’t know your data model or your way of processing. How you speak in LOCKS is presumed to be a consequence of unfinished transactions.
– anonimo
@anonimo But BEGIN TRAN both for INSERT UPDATE ? Because I started managing a very messy environment, I was able to notice that I have 197 procs without being with HINT WITH (NOLOCK), but being with NOLOCK is not sure that NOLOCK is in better practice ... right?
– Luiz
"there is some risk of I set it as ON in productive environment", there is no "ON", you must set the desired level. Your questions are difficult to answer, for example "For the procedures that are in the reports, there is some option of ISOLATION level more appropriate ", depends, has to see what the Trial does and its goal, in addition to having many different questions, maybe get better answers if you ask more than one question, each one more specific
– Ricardo Pontual