July 25, 2011 at 4:16 am
We are experiencing a number of issues with our database at work and within the reporting team we are seemed to be getting the brunt of the blame for locking tables when querying. This issue is a little odd, as the queries we run are run daily and the database down time isn't happening daily, so I wondered how can Locked tables be blamed on reporting.
But anyway I wanted to prove to myself that it cant just be down to running queries is the sole issue of the database problems.
So if I run a query and use WITH (NOLOCK) does this need to be added to each table I query from?
eg 1
SELECT * FROM tableA WITH (NOLOCK)
eg 2
SELECT * FROM tableA a WITH (NOLOCK)
LEFT OUTER JOIN tableB b WITH (NOLOCK) ON b.id = a.id
eg 3
SELECT * FROM tableA a WITH (NOLOCK)
LEFT OUTER JOIN tableB b WITH (NOLOCK) ON b.id = a.id
LEFT OUTER JOIN tableC c WITH (NOLOCK) ON c.id = a.id
July 25, 2011 at 4:25 am
Before you go that route...
Nolock means potentially incorrect data. Not just dirty reads but also duplicate rows and missed rows. If those reports just have to be mostly accurate that may be fine. If not....
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Consider using snapshot isolation or read committed snapshot isolation rather. It does have an impact on TempDB, but it's not usually huge and it gets you lock-less read queries without the same data accuracy issues that read uncommitted does.
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
July 25, 2011 at 4:28 am
The NOLOCK hint can cause dirty reads, so only use it if you don't require the data returned to be totally accurate. Instead, concentrate on optimising your queries. Specify only the columns you need instead of using SELECT *, and use a WHERE clause where appropriate. Make sure your indexing strategy is correct and that you reorganise or rebuild indexes and update statistics as often as necessary. Post back if you need specific help with any of that.
John
July 25, 2011 at 4:48 am
John Mitchell-245523 (7/25/2011)
The NOLOCK hint can cause dirty reads, so only use it if you don't require the data returned to be totally accurate. Instead, concentrate on optimising your queries. Specify only the columns you need instead of using SELECT *, and use a WHERE clause where appropriate. Make sure your indexing strategy is correct and that you reorganise or rebuild indexes and update statistics as often as necessary. Post back if you need specific help with any of that.John
I just used SELECT * as an example, I always just extract fields that I need as A its a waste of resource querying them and B I'm not going to use them for anything.
I have asked IT to look at the Indexing as I dont have access to that side of things. The DBA is all done by an external contractor.
July 25, 2011 at 4:54 am
jez.lisle (7/25/2011)
I have asked IT to look at the Indexing as I dont have access to that side of things. The DBA is all done by an external contractor.
Oh dear.... 😉
I hope the external people know what they are doing...
Your problem does sound like there's a fair amount of non-optimal queries running. If need be, would there be scope for getting an external SQL expert in?
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
July 25, 2011 at 5:00 am
GilaMonster (7/25/2011)
Before you go that route...Nolock means potentially incorrect data. Not just dirty reads but also duplicate rows and missed rows. If those reports just have to be mostly accurate that may be fine. If not....
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Consider using snapshot isolation or read committed snapshot isolation rather. It does have an impact on TempDB, but it's not usually huge and it gets you lock-less read queries without the same data accuracy issues that read uncommitted does.
Not meaning to sound thick today. what would be the best way of going about this? I only say this as I have never used this sort of stuff before in my queries
July 25, 2011 at 7:42 am
May I suggest you read up on the snapshot isolation levels first. There's a lot of info in Books Online.
You can't just change the query, snapshot isolation has to be enabled on a database level first. That'll require a DBA.
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
July 26, 2011 at 7:07 am
1) consider moving your reporting off to another server. Log shipping is great for this if you only need say yesterday's data 'freshness'. Large-scale reporting against production databases can be problematic in many ways (especially if the production database is OLTP in nature).
2) Also consider a project to produce a proper reporting data mart or warehouse.
3) snapshot isolation as Gail mentioned can really help with concurrency issues, but it has issues of it's own. I agree with here that you might need to get some professional help on board to guide you since you have no in-house resources.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply