Unanswered: What causes updates/locks on sysobjvalues, sysrowsets?
Disclaimer: I'm not a very experienced SQL Server user (I do have a lot of experience with Oracle and Postgres though)
Our customer is using SQL Server 2005 and have a process that updates several tables in a single transaction, importing data from a loading process.
The process truncates two intermediate (regular) tables, inserts data into one of them, removes duplicates from it using delete ... output deleted into ... and then does an update/insert from that intermediate table into the real ones.
Nothing fancy, it usually processes something around 20.000 - 50.000 rows. This works fine and we never had problems in the test or integration environments.
Now when this went into production, some users complain that during the day their Management Studio would lock up while trying to expand the list of tables and then after a while show an error message "Lock request time out period exceeded".
As we we enabled read_committed_snapshot turned this must be some locks on the system tables and due to SQL Server's "writers block readers" behaviour (which is frankly technology from the 80s but that's a different topic)
Now, our program isn't doing any DDL that would cause uncommitted changes to the system tables.
We used the following statement, to analyze the locks in the database the next time this happened:
SELECT l.request_session_id AS spid,
(select obj.name from sys.sysobjects obj with (nolock) where l.resource_associated_entity_id = obj.id) as table_name,
rtrim(l.resource_description) as resource_description,
db_name(l.resource_database_id) as db_name,
FROM sys.dm_tran_locks l with (nolock)
WHERE resource_type <> 'DATABASE'
order by spid, l.resource_database_id
This showed the expected locks on the tables that our program was processing, but it also showed four system tables:
spid table_name resource_type request_mode request_status
82 sysrowsets OBJECT IX GRANT
82 sysallocunits OBJECT IX GRANT
82 sysserefs OBJECT IX GRANT
82 syscolpars OBJECT IX GRANT
Now my assumption is that Management Studio while reading the list of tables is somehow also trying to read those tables and locks up there.
Now my question(s):
What causes these updates?
How can I prevent them from within my program?
The table names seem to indicate that this has something to do with system statistics. Is there some kind of configuration setting I can change to change this?
Things we can not do:
* running in auto-commit mode (we need to have all steps in a single transaction)
* commit after n rows - mainly because the actual DML are single delete,insert and update statements where we cannot commit "in-flight".
* upgrade to a more recent version of SQL Server
I noticed that those locks also appear in our environment but they don't cause a "lock timeout" error when refreshing the table list in the Management Studio.
So it seems this is indeed either a SQL Server setting or maybe even a problem Management Studio.
I think the isolation level for each client can override whatever is set at the database level. I do not do much experimentation on isolation levels, so I could be very wrong. The client's isolation level should be shown in sys.dm_exec_sessions. Your real problem may be that truncate table is a command that can be rolled back in SQL Server. Any chance just the truncate can be moved outside the transaction?
I think the isolation level for each client can override whatever is set at the database level. I do not do much experimentation on isolation levels, so I could be very wrong. The client's isolation level should be shown in sys.dm_exec_sessions.
We checked that (I suspected that as well), but they are all using READ_COMMITTED
Your real problem may be that truncate table is a command that can be rolled back in SQL Server.
So it is in Postgres.
Any chance just the truncate can be moved outside the transaction?
That's what we finally did. Not pretty, but a somewhat usable workaround.
For some of the original questions, sysallocunits is probably the base table for the view sys.allocation_units, which is similar to DBA_EXTENTS in function. Unfortunately, I'm not familiar with the other tables, so I can not give a definitive answer on those. Your assumptions were well on the right track.