April 15, 2011 at 1:14 pm
I tried to find a temporary table by selecting from tempdb.sys.tables. However I can see only ## global temp tables, not #. Is there a way to find them ?
Thanks
April 15, 2011 at 1:22 pm
Here are 2 ways:
CREATE TABLE #table (id INT) ;
SELECT
*
FROM
sys.tables AS T
WHERE
T.name LIKE '#table%' ;
-- OR
SELECT
*
FROM
sys.tables AS T
WHERE
T.object_id = OBJECT_ID(N'tembdb..#table', N'U') ;
DROP TABLE #table;
Of course the first one will return any table that starts with '#table'. The second searches for a specific table. You can prove that by starting another session and creating another #table table and then run the code above before closing the other connection.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2011 at 1:23 pm
the name field doesn't have the name like you think: it usually has a ton of underscores plus a unique numbr
try this instead:
select name, object_name(object_id) As ObjName,*
from tempdb.sys.objects
where name like '#%'
does that get you where you want to be?
Lowell
April 15, 2011 at 1:26 pm
It works, Jack, if I run this code in same connection. But I need to create a temp table in one connection and search for it in another one.
April 15, 2011 at 1:30 pm
Why do you need to find the table in another connection?
Temp tables are tied to a connection/session, so you can use the same name in each connection without issue. You can't access a normal temp table from another session/connection even if you know the "real" name.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2011 at 1:32 pm
That is the way temp tables are designed to work. They are only visible to the connection that creates them. If you need to see a temp table from another connection you need a global temp table. ##table
This will make that temp table visible to all connections.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2011 at 1:35 pm
That's because I am tasked to write a script to find largest objects in tempdb. They affect our performance. The code is simple, I just join sys.tables and sys.sysindexes in tempdb. But the only problem that I can't see other connections local temp tables. Only global.
April 15, 2011 at 1:45 pm
Sure you should be able to see all the temp tables regardless of connection. The first query I provided will do that. Perhaps you need to be sysadmin for this to work?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2011 at 1:55 pm
It looks like this is the problem, I do not have 'sa' privileges.
April 15, 2011 at 2:05 pm
SQL Guy 1 (4/15/2011)
That's because I am tasked to write a script to find largest objects in tempdb.
Just having large objects in TempDB shouldn't have any effect on performance.
Next question is what you would do if you found a large object in TempDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2011 at 2:43 pm
First, they do affect the performance. From time to time users get a messages that SQL Server cannot allocate space in tempdb.
Second, notify managers via email when used space in tempdb exceeds some threashold. At least, they will know what's going on.
April 15, 2011 at 2:50 pm
I would suggesst looking at what space is available. Can the TemDB be moved to a drive with more free space? What is running that is actually filling up the Tempdb and why must so much data be loaded to a temporary table. If you are filling up your presumably C: drive and using all available space then you may find youself in a critical situation very fast. It is not a performace issue per say but more of an administration problem.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 15, 2011 at 2:55 pm
SQL Guy 1 (4/15/2011)
First, they do affect the performance. From time to time users get a messages that SQL Server cannot allocate space in tempdb.
Now we are getting somewhere.
Have you seen this, http://support.microsoft.com/kb/2000471.
You should probably read this as well, http://msdn.microsoft.com/en-us/library/ms176029.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 16, 2011 at 12:39 am
SQL Guy 1 (4/15/2011)
First, they do affect the performance. From time to time users get a messages that SQL Server cannot allocate space in tempdb.
That's not a performance problem. That's a space problem. TempDB likely need to be bigger. Make sure it's not running at default settings and growing (8MB data, 1MB growth). Pre-size it. Separate drives is good if it gets heavy use.
What you probably want to do it to start tracing down queries that use lots of TempDB space and see if they can be optimised. I know there's a query, I don't have time right now to look, but I will find it for you later.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2011 at 5:48 am
I wrote an article some time ago that suggests a way of tracking tempdb utilization using reporting services:
http://www.sqlservercentral.com/articles/tempdb+utilization/65149/
(unfortunately, some of the figures are a bit too small to see clearly, as I realized when it was too late...)
Here is a query you can run periodically to track tempdb usage:
--http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#EX1AE
SELECT
R1.session_id
, R1.user_objects_alloc_page_count
, R1.user_objects_dealloc_page_count
, R1.internal_objects_alloc_page_count
, R1.internal_objects_dealloc_page_count
, R3.[text]
,S.[program_name]
,S.login_name
,S.[status]
,S.cpu_time
,S.memory_usage
,S.total_scheduled_time
,S.total_elapsed_time
,S.last_request_start_time
,S.last_request_end_time
,S.reads
,S.writes
,S.logical_reads
FROM
sys.dm_db_task_space_usage AS R1
INNER JOIN
sys.dm_exec_sessions AS S
ON
R1.session_id = S.session_id
LEFT OUTER JOIN sys.dm_exec_requests AS R2
ON R1.session_id = R2.session_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
WHERE
R1.session_id > 50
AND
(
R1.user_objects_alloc_page_count > 0
OR R1.user_objects_dealloc_page_count > 0
OR R1.internal_objects_alloc_page_count > 0
OR R1.internal_objects_dealloc_page_count > 0
OR R3.[text] IS NOT NULL
);
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply