Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

To NOLOCK or not to NOLOCK, that is the question!

I was building a report today and ran into some deadlock issues. A couple of weeks ago I might have blamed our database server hardware but since we're running a brand new server with a RAID10 array of SSDs it doesn't look like hardware. The report was a custom SQL query and, fortunately for our team, we have a former DBA. He suggested that I look at using the WITH (NOLOCK) hint in our reports but cautioned that we not use them in our alerts due to the possibility of dirty data.

I'm reaching out to your SQL wonks out there -- where can (and should!) one leverage the NOLOCK hint?

(And if sqlrockstar‌ wanted to weigh in too -- we'll see if he takes the bait )

Find more posts tagged with

sql

nolock

Accepted answers

sqlrockstar

Deadlocks are not a hardware issue, they are a database design issue. Using NOLOCK is not going to remove deadlocks, as NOLOCK is just another way of saying READ UNCOMMITTED, and neither is going to remove how SQL server acquires locks in order to perform transactions.

So, your question is "when to use NOLOCK", and I'd suggest a better question is "when would I use a query hint".

You would use a query hint whenever (1) you need a specific query to exhibit a behavior that is not the default for other queries and (2) you can't solve your issue any other way.

For deadlocks, I point people to this article: https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/

In short, a few things you can try to eliminate deadlocks from happening in SQL Server:

Remove bookmark lookups, if present
Don't use SERIALIZABLE isolation, if you are
Avoid cascading constraints (i.e., you need to change your data model)
Access tables in a different order
Keep your transactions as short as possible

HTH

All comments

sqlrockstar

So, your question is "when to use NOLOCK", and I'd suggest a better question is "when would I use a query hint".

You would use a query hint whenever (1) you need a specific query to exhibit a behavior that is not the default for other queries and (2) you can't solve your issue any other way.

For deadlocks, I point people to this article: https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/

In short, a few things you can try to eliminate deadlocks from happening in SQL Server:

Remove bookmark lookups, if present
Don't use SERIALIZABLE isolation, if you are
Avoid cascading constraints (i.e., you need to change your data model)
Access tables in a different order
Keep your transactions as short as possible

HTH

jbiggley

Bait taken - success!

Unfortunately, the DB in question is our Orion DB and the first 3 suggestions (correct me if I'm wrong) are outside of our control. Even 4 and 5 are mostly out of our control except where custom reports, custom SQL alerting (which we do have a good amount of in our environment) and data pulls from other analytics platforms (yes, Orion is the source of some extensive analytics data for out IT analytics group!).

Having thrown the fastest hardware we could get at the problem (and jumping from RAID5 to RAID10 at the same time) we expected waits to drop significantly. And they did, but the win was short lived. Now we are trying to track down what changed that caused the waits to jump back up again. I believe the deadlock was only a symptom of a very busy DB and not the root cause.

silverbacksays

I think we're starting to see the down side to the 'all your eggs in one basket' model. When you take into account that the most recent version of NTA splits off the Flow database, you can see that over time the Orion DB is getting to the point where it's just too popular a party for everyone to attend in one hall. So, they're splitting off people into VIP rooms... that's the case with NTA.

We may see the same happening to other modules over time. As long as it's done in a sensible manner it should be fine, just means we'll have more servers/databases to manage.

jbiggley

Agreed. I suspect that the exact tactic you just outlined could be employed in other places in the Orion suite to limit the impact to the core SQL DB. I guess we'll have to wait and see what the future holds for us.

That said, this is the era of big data. Converged (aka Single Pane of Glass) views of disparate data sources are going to require a tonne of processing power. We are examining technologies like AlwaysOn to replicate our data to a second database that can be accessed by applications and teams that don't need the data quickly, they just need it to be current. By removing contention from our expanding demand for data generated from Solarwinds we can focus on data collection instead of data sharing. (Not that we don't want to share -- but collection first, sharing second.)

I can see 3rd party technologies like Hadoop and Tableau wanting to drink from the cup of Solarwinds data in our environment in the near future -- and I like it!

This all speaks to the power and allure of the data that is being collected by the Orion family and the other Solarwinds products. When people ask me why I do enterprise monitoring it is not because I am a technology wonk who knows everything about every technology (such a person doesn't exist anyway) but rather there is no better place to be to collect gobs of technology related data and figure out how to present it in an intelligent way.

For a numerophile there is no better career.

sqlrockstar

Joshua,

I've no idea if this is the case or not...but are you allowed to modify indexes on the Orion DB without violating terms of support? If so, then you would be able to cure bookmark lookups.

Of course, first thing would be to determine why the deadlocks are happening. The article I linked to shows ways to capture the deadlock graphs to get an idea of what activity is triggering the deadlocks. And if it is a case where a bookmark lookup is the issue (causing the transaction to take longer than necessary) we can make some quick modifications to fix that.

HTH

jbiggley

I appreciate the pointed assistance -- especially to this accidental DBA. I'll let you know if things improve.