0

I have a question about the use of WITH (NOLOCK / READPAST) in SQL Server.

When I used the WITH (NOLOCK / READPAST) hint on one or two tables, and I have other tables in JOINs, should I use that hint for all tables, or just the tables that I know about this need?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user8223022
  • 195
  • 2
  • 15
  • 3
    [Table hints](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15) apply per table. [Query hints](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15) apply per query. Nolock is a table hint. – GSerg Jan 10 '20 at 16:21
  • 4
    Can you explain clearly why you "need" NOLOCK? It is a query hint with a name that is not great. It also has some major baggage that most people don't seem to realize. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ – Sean Lange Jan 10 '20 at 16:24
  • 2
    As for the question at hand. Nobody can tell you definitively if you should use for all tables or not. I would lean heavily towards not using at all. – Sean Lange Jan 10 '20 at 16:25
  • I have a table that receives multiple log records per second, and I always need to read these logs with current time -1h, so this data is unchanged and can be read with WITH (NOLOCK) so as not to wait for the transactions they insert into this table finish, by making the query much faster. My question was if for the other tables is also necessary to use WITH (NOLOCK) – user8223022 Jan 10 '20 at 16:31
  • 3
    At *worst* I would suggest you just use it against the table you are getting the deadlock against, and be prepared for (consistently) wrong data. At best, don't use it at all and fix the underlying performance problem; indexing and statistics are great places to start. – Thom A Jan 10 '20 at 16:34
  • Thank you @Larnu. I may be asking one more pointless question. We have some queries that use READPAST, to only read non-transaction data, and to be able to work with the security that this data is not changing in another process, is that correct? – user8223022 Jan 10 '20 at 16:43
  • I'm not sure I follow your question, sorry. – Thom A Jan 10 '20 at 16:45
  • 4
    Reading data that's not changing isn't sped up by adding `NOLOCK` (unless your indexing is lacking), because the engine is clever enough to lock only rows that need locking. With a table that you read almost exclusively based on time and where rows are only inserted chronologically, it can make a lot of sense to make the insert/audit date the (non-unique) clustered index. There should then be little to no use for `NOLOCK`, and even where that's not enough, it makes sense to consider alternatives like snapshot isolation and in-memory tables first. Treat `NOLOCK` as "I don't mind wrong results". – Jeroen Mostert Jan 10 '20 at 16:48

1 Answers1

0

As GSerg pointed out in a comment table hint are used per table.

OP commented she/he is using it to read logs. I assume log tables are not "index rich" for many reasons, mainly insert performance.

With few or none indexes querying that log will be a pain and can easily degenerate to a table scan, doing it on a table being heavily updated (by inserts) can really lead to one of the few scenarios where a (NOLOCK) is justifiable.

Use the hint on any table in the join you are not afraid of getting dirty reads or missed locked rows.

Edit

Jeroen and Lamu give us some good insight in the comments. I know many times we cannot change the schema, merely adapt and try to do the best with whatever we got. But if you can improve your logging/audit feature by changing the table/schema please do it.

jean
  • 4,159
  • 4
  • 31
  • 52
  • I agree that log tables are not *normally* index rich, however, log tables tend to normally not be very wide, therefore make it difficult for them to be "rich". Archive tables are normally wider than the source table, but a log of user actions, for example, would likely be quite small in width (perhaps 7/8 columns; ID, User, datetime, action, record reference). These can quite easily, in my view, have a good selection of indexes because of how "thin" they are. – Thom A Jan 10 '20 at 16:48
  • It is a table that contains more than 40 columns. The idea of having multiple columns is to avoid joins with other tables, read-ready data is created, but this table is used in many different queries, creating multiple indexes would be too slow for inserts and would also have too many indexes to serve all queries. – user8223022 Jan 10 '20 at 16:57
  • 1
    Even if this is the kind of database you have, you're usually better off simply changing the default transaction isolation level, rather than remembering to put the hint in each query. – Joel Coehoorn Jan 10 '20 at 17:02
  • What isolation level is recommended for this situation? – user8223022 Jan 10 '20 at 17:07