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.
Best Answer
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.