If I do a vanilla SELECT * FROM sys.tables
or SELECT * FROM sys.dm_exec_query_stats
are those tables getting locked since I'm reading data from them?
If so, what happens when SQL Server ties to write to them while I'm locking them e.g. when a new table is created?…does the table creation actually hang until the read lock is released?
Best Answer
To start with,
sys.tables
is a system catalog view, not a table. And DMVs / DMFs (especially those that report stats) often query memory, which is why they get cleared out when you restart or executeDBCC FREESYSTEMCACHE(N'ALL');
.Still, since
sys.tables
does eventually lead to actual system tables, I figured I should test this, especially because I have noticed in the past that sometimes addingWITH (NOLOCK)
does help when querying some of the system catalog views.In order to test this, I used a SQLCLR function to pause for a few seconds after returning each row. This keeps the auto-commit transaction open long enough to be able to see what locking is actually happening during the operation (without coming up with several cross joins in an attempt to return tens of millions of rows which also takes a while but takes up a lot of memory as well). The SQLCLR function, DB_WaitForDelay, is available in the free version of SQL#, a library of SQLCLR procs and functions that I wrote.
While that was executing, the following resources were being locked by that session:
That last item,
sysschobjs
does not show up if I uncomment theWITH (NOLOCK)
when selecting fromsys.tables
. Interesting.While that was running (without the
WITH (NOLOCK)
), I was able to execute the following in[tempdb]
:So, even with some locks being taken, it seems like you should be fine. In fact, I even tested again, but this time I first executed the following before the
SELECT
:And again there was no interference with the
CREATE TABLE
/DROP TABLE
operations.When selecting from the
sys.dm_exec_query_stats
DMV and using DB_WaitForDelay I did not see any locks being taken by that session.Just to have this caveat stated: I only tested the two objects mentioned in the question. It is possible that other system catalog views and/or DMFs/DMVs do some additional locking that might have a more noticeable impact.