tempdb blocking

  • If I attempt to expand tables via SSMS, I get: "Lock request time out period exceeded".

    - When querying tempdb.sys.tables, I’m being blocked pretty much non stop by random SELECT INTO procedures

    - I can query tempdb.sys.tables WITH (NOLOCK)

    - Why would that block me from viewing data? Uncommitted temp table creations!?

    - If I attempt to right click tempdb and view properties, I get: "Property Size is not available for Database '[tempdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights."

    - Definitely not a permissions issue. Database is owned by sa and I can query all the tempdb system properties via TSQL

    Any initial starting points would be most welcome. I realize the tempdb is very busy and possibly contentious; however I've never seen this type of behavior before.

    Thanks

  • That does sound like either permissions issues, serious blocking issues, or maybe a combination of the two. I have seen the first error message before. It was on a system that was under serious resource contention. Are you seeing excessive blocking on the server? Don't try to use the GUI to check it, use dynamic management views such as sys.dm_exec_requests.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I have faced this issue one while trying to open the stored procedures using SSMS. Everytime i tried to expand the stored Procedure node it gave "Lock request time out period exceeded".

    When i checked the queries running on the server i found out that a proc which was calling XP cmdshell was running under multiple connections. On Further investigation i found out that even when the called procedure completed its execution xp cmdshell was continued to run and was causing some kind of locking in SQL Server.

    Killing the process from the task manager solved my issue.

    Regards,
    Shafat Husain
    🙂
    And Your Lord Never Forgets...!! (64:19 -- Quran)

  • After some more digging and testing, it would seem that a SELECT INTO does indeed lock some of the system tables.

    We're now starting to dissect performance between SELECT INTO and INSERT INTO and could use some guidance.

    Why would a SELECT INTO vs. an INSERT INTO yield more tempdb allocations? More specifically when we did a SELECT INTO, we saw over tens of thousands of tempdb allocations (sys.dm_db_task_space_usage); however when using an INSERT INTO, we saw none. Obviously this makes us want to recommend removing all the SELECT INTO’s; however would like either of your opinions on this topic while we continue to investigate and test.

    I have to assume overall, a SELECT INTO is more contentious than that of an INSERT INTO; however would love some true backing to confirm as the amount of work ahead to make these changes is quite substantial.

    Any guidance is most welcome.

    Thanks

  • Adam Bean (12/11/2014)


    After some more digging and testing, it would seem that a SELECT INTO does indeed lock some of the system tables.

    We're now starting to dissect performance between SELECT INTO and INSERT INTO and could use some guidance.

    Why would a SELECT INTO vs. an INSERT INTO yield more tempdb allocations? More specifically when we did a SELECT INTO, we saw over tens of thousands of tempdb allocations (sys.dm_db_task_space_usage); however when using an INSERT INTO, we saw none. Obviously this makes us want to recommend removing all the SELECT INTO’s; however would like either of your opinions on this topic while we continue to investigate and test.

    I have to assume overall, a SELECT INTO is more contentious than that of an INSERT INTO; however would love some true backing to confirm as the amount of work ahead to make these changes is quite substantial.

    Any guidance is most welcome.

    Thanks

    From what i remember, the SELECT INTO issue with sys tables "locking" in tempdb was resolved long ago, i would say that fix is here since SQL200 SP3...

    SELECT INTO used to ecrease contention in tempdb of SQL2000 and pre.

    SELECT INTO never blocked anything in tempdb.

    Used in tempdb, SELECT INTO is no more contentious than that of an INSERT INTO in post SQL2000 SP3

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Adam Bean (12/11/2014)


    After some more digging and testing, it would seem that a SELECT INTO does indeed lock some of the system tables.

    We're now starting to dissect performance between SELECT INTO and INSERT INTO and could use some guidance.

    Why would a SELECT INTO vs. an INSERT INTO yield more tempdb allocations? More specifically when we did a SELECT INTO, we saw over tens of thousands of tempdb allocations (sys.dm_db_task_space_usage); however when using an INSERT INTO, we saw none. Obviously this makes us want to recommend removing all the SELECT INTO’s; however would like either of your opinions on this topic while we continue to investigate and test.

    I have to assume overall, a SELECT INTO is more contentious than that of an INSERT INTO; however would love some true backing to confirm as the amount of work ahead to make these changes is quite substantial.

    Any guidance is most welcome.

    Thanks

    You need to look at the way they both work. INSERT INTO requires an existing table, SELECT INTO creates a new table, this would need to check the system catalogs to ensure the table doesn't already exist

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Eugene - It most definitely does lock up tempdb.

    Perry - Agreed; however simply trying to understand performance implications between the two.

  • one will insert rows to an already allocated table and one will have to create the table and employ all the I\O processes around the page allocations, as well as then insert the data. Whether the performance is noticeable or not there will be some contention as the page allocations are made (i.e. finding free pages, allocating them and updating any free space metadata for the pages\extents)

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply