December 9, 2014 at 2:54 pm
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
December 10, 2014 at 4:14 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 10, 2014 at 4:32 am
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)
December 11, 2014 at 10:04 am
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
December 11, 2014 at 10:20 am
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
December 11, 2014 at 11:10 am
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" 😉
December 11, 2014 at 11:13 am
Eugene - It most definitely does lock up tempdb.
Perry - Agreed; however simply trying to understand performance implications between the two.
December 11, 2014 at 11:22 am
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply