March 18, 2009 at 8:20 am
I have been tasked with making sure a group of stored procedures all have the transaction isolation level to 'READ UNCOMMITED'
What is the best way to accomplish this?
Can I just add
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
To the very first line of every statement??
March 18, 2009 at 8:25 am
krypto69 (3/18/2009)
I have been tasked with making sure a group of stored procedures all have the transaction isolation level to 'READ UNCOMMITED'
For what reason? Does the person who asked know about the potential risks with the READ UNCOMMITTED isolation level?
To SQL, READ UNCOMMITTED doesn't mean 'get my data quickly' it means, 'get my data, any way you have to, I don't mind if it's slightly inaccurate'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2009 at 8:46 am
Thanks for answering and yes they know the risks Gail.
And hey, not my call...they want it on every one of these procs.
Guess I was just wondering if putting it as the very first line will always work, on all types of statements?
March 18, 2009 at 9:01 am
krypto69 (3/18/2009)
Guess I was just wondering if putting it as the very first line will always work, on all types of statements?
Putting it on the first line is your only option. It will not work for all statements, data modifications will still take locks, they have to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2009 at 9:25 am
Thanks Gail
March 18, 2009 at 1:57 pm
GilaMonster (3/18/2009)
For what reason? Does the person who asked know about the potential risks with the READ UNCOMMITTED isolation level?
To SQL, READ UNCOMMITTED doesn't mean 'get my data quickly' it means, 'get my data, any way you have to, I don't mind if it's slightly inaccurate'
Hi Gail
I need it (we are using the NOLOCK table hint) for some KPI/MIS reporting. Our old database is very slow by its database design and has about 180gb to avoid trouble with the operational processes we have no other possibility.
It's not nice, but I see no other possibility.
Greets
Flo
March 18, 2009 at 2:12 pm
Florian Reischl (3/18/2009)
It's not nice, but I see no other possibility.
a
Optimise queries, tune indexes?
Seriously, in most cases doing those two removed the 'need' for nolock. Or are you running reports on an OLTP system?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2009 at 2:30 pm
Hi Gail
There are two reasons for this ugly approach:
First the database design
The database is not my design it came by my previous chief developer. I would call it not even a real relational database... Short description:
One single object table for all objects! There is only one table called TT_TO which only contains two IDs as primary key and a type code for the object type, no describing data.
One single TT_TOLNK table which defines all links between any objects containing the parent IDs and the child IDs.
One single table for each possible field type with the IDs, the field value (datetime, float, varchar, or int) and a type code which specifies the type of the field.
By this "design" you have always quiet huge SELECT-JOIN cascades and dozen of UPDATE-JOINS to scrap all your needed information together...
Second the customer requirements
Sure, we also have a flat DW database for some other reports and tracking and tracing systems. But sometimes it seems to be impossible for the accounting team to explain to the customer that a report is a report and does not need the very latest information...
Your importer from OLTP database to the DW sometimes has backlogs of about 10 to 30 minutes but this is to much for the customer.
So I have to run some of the reports on live OLTP database... Sure with NOLOCK the data still not really the latest version but this is okay.
I'm currently (since more than one year...) creating the new system which will be a usual database design. But it takes time to migrate over 10 web applications and almost 80 importer/converter/exporter/guis/scanner/... .
Greets
Flo
March 18, 2009 at 2:38 pm
Ouch. The old 'One True Table' design. I'm surprised you can get anything out of that in a reasonable time, even with nolock.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2009 at 1:43 pm
If I put it at the top of statement, it says 'completed' but when I go back in and look at the proc, the isolation level part I just added is gone...
If I add the isolation part after the 'AS' in the procedure it stays, looks fine.
Why is that?
April 21, 2009 at 1:57 pm
krypto69 (4/21/2009)
If I put it at the top of statement, it says 'completed' but when I go back in and look at the proc, the isolation level part I just added is gone...If I add the isolation part after the 'AS' in the procedure it stays, looks fine.
Why is that?
If you put the SET TRANSACTION ISOLATION LEVEL before the create procedure, then it applies when you're creating the procedure. Anything that has to run when the procedure runs has to go inside the procedure, ie after the AS
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2009 at 2:03 pm
Oh i see gotcha thanks Gail
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply