1

If I have frequent DML(insert,update,delete) operations on a table with huge data with clustered and non-clustered indexes. Will it affect the Performance of SELECT queries on that table with a where condition on index columns, Or will the performance remain same as when there are no DML operations?

This Link says UpdateStatistics does block Select queries. So will DML operations cause UpdateStatistics. It says " The only time you wouldn’t be in control of this is if AUTO_UPDATE_STATISTICS is enabled on your database and AUTO_UPDATE_STATISTICS_ASYNC was disabled.". So is changing these values the best solution?

Note:  I will be using isolation level (Read Uncommitted) for the SELECT query which should not be blocked by locks.

2 Answers 2

2

First a comment on terminology: DML (Data Manipulation Language) includes SELECT. I have a feeling that you when you say DML refer to INSERT, UPDATE and DELETE. I'll be referring to these as "modification statements".

You also say that you run "default isolation level (Read Uncommitted)". That is not correct. The default isolation level is Read Committed, not Read Uncommitted.

Read Committed is indeed blocked by exclusive locks, which are acquired by data modification statements. That is the default behavior of Read Committed, but you can re-configure the behavior by changing the database setting read committed snapshot to ON (RCSI). With RCSI readers are not blocked by modifiers, they'll get the last committed version of the row instead of waiting. (RCSI is by default ON for Azure SQL Database, btw.)

Don't worry about update statistics. It doesn't block readers. That blog post you refer to INDEX REORGANIZE at the same time as UPDATE STATISTICS. UPDATE STATISTICS in itself doesn't block readers. I just verified this by running update statistics on the stats for an index and I simultaneously ran SELECT using that index/stats, several times without any hint of blocking.

3
  • Yeah, I verified. Read committed is the default isolation level. I consider setting read committed snapshot to ON (RCSI) as the solution. Thanks
    – Mahen
    Dec 14, 2020 at 12:01
  • Accepted this answer considering Read Committed isolation level for SELECT queries
    – Mahen
    Dec 14, 2020 at 12:03
  • Also good to know about the Azure SQL DB RCSI default ON and clarification of UpdateStatistics in blog link.
    – Mahen
    Dec 14, 2020 at 14:10
-1

*Note this answer starts out in regards to the default isolation level which is what OP originally asked about, but then goes into the actual isolation level he was curious about.

In general DML queries do cause locks on rows and Tables which will affect SELECT performance, especially in the READ COMMITTED isolation level. It depends on the specific DML operation, the records it's affecting, and the SELECT query and which records it needs to read.

If a certain DML operation only causes a row level lock, then only those specific rows will be locked and SELECTs against those rows will be blocked while waiting for the DML lock to be released. SELECTs against other rows of the same Table will not be blocked. If the DML operation escalates to a Table level lock, then all SELECT queries against that table will be blocked throughout. Bear in mind, most times this is usually very quick either way, and not a concern (unless it's a poorly constructed DML operation).

The previous is true regardless if you have indexes or not, the only difference is some DML operations can be slightly slower, the more indexes that exist on a Table, such as INSERT operations. But conversely other DML operations like UPDATES generally perform better with the appropriate indexes on the Table, since the indexes help the UPDATE operation locate which records to update.


Since OP meant they are using the READ UNCOMMITTED isolation level, direct row and Table level locking does not apply then from writers blocking readers as normally would be in the default isolation level, READ COMMITTED.

BUT a heavy DML operation can still affect performance of a SELECT query by causing contention on the Table or Server. For example, if the table is compressed and a large amount of rows are UPDATEd by a single query, that can use a lot of CPU and I/O, and those server resources would be under contention throughout the operation causing other SELECT queries to potentially run slower on that server.


Sidenote, "huge data" is very subjective in the database world. I'm not sure what that means to you quantifiably, but I've worked in databases with Tables that had 10s of billions of rows, that did multiple DML operations to 1,000s of rows per minute and the SELECT performance was still excellent.

3
  • READ UNCOMMITTED is in general not blocked by the locks. It is equivalent to NOLOCK. Your first statement is contradicting. Please validate.
    – Mahen
    Dec 14, 2020 at 7:49
  • @Mahem sorry when you said default isolation level that's what I was referring to in my answer, which is actually READ COMMITTED. (I should've refreshed myself on the isolation names as I didn't catch that you meant a different isolation than the default in your answer. Fixes the contradictory part of my answer.)
    – J.D.
    Dec 14, 2020 at 12:30
  • 1
    yeah. Even iam sorry for mistakenly stating Read Uncommitted as Default isolation level initially which created the confusion. My doubt was to check if even in Read Uncommitted level will there be any blocking. I thought Index Reorg may occur, but the DML operations will not trigger Index Reorg. So your answer can also be considered now for Read Uncommitted isolation level.
    – Mahen
    Dec 14, 2020 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.