Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12

    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:

    Code:
    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,
           l.resource_type,
           l.resource_subtype,
           rtrim(l.resource_description) as resource_description,
           l.request_mode,
           l.request_status,
           db_name(l.resource_database_id) as db_name,
           l.resource_associated_entity_id
    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:

    Code:
    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

    Any ideas?

    Update

    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.
    Last edited by shammat; 02-12-13 at 04:47.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    OK, after digging deeper into the statements and what is happening on the server, my initial assumption has been proven wrong.

    The hint that got me in the right direction was another row in the lock overview:

    Code:
    spid    table_name      resource_type  request_mode  request_status
    57      stage_foobar    OBJECT         Sch-S         WAIT
    (where stage_foobar is the table that has been truncate and is being "worked" on during our import).

    After checking the statements that are sent from Management Studio to the server to refresh the table list, the following part could be identified as the culprit of the "hanging" Management Studio:
    Code:
    select name, OBJECTPROPERTY(object_id, 'OwnerId')
    from sys.tables
    This is part of a much larger statement, but the problem can be reduced to the usage of the objectproperty() function.

    The statement consistently hangs, as soon as a TRUNCATE is issued on a table. Apparently the promise that READ_COMMITTED_SNAPSHOT resolves the "writers block readers" problem isn't true.

    Interesting enough not all Management Studio installations show this behaviour. Apparently there are some settings that influence the usage of the objectproperty() funcion.

    The workaround seems to be to use DELETE rather than TRUNCATE, or to commit the TRUNCATE right away to avoid the exclusive locks.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Thanks for the answer.
    Quote Originally Posted by MCrowley View Post
    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.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •