February 9, 2011 at 5:16 pm
Hi ALL,
I opened my temp DB and saw that there are many tables inside it. They are all surely not user created , I think because they are named something like "dbo.#2D27B809" . I would like to know if I am correct.
Is there a way to differentiate between User tables and system created temporary tables besides identifying through the name?
I know the system creates many temp tables (for eg while sorting).
Thanks,
Suri
February 9, 2011 at 5:23 pm
I don't know the answer offhand, but out of curiousity, why do you care? Are you just simply curious or do you have a distinct criteria for it?
Now, if it was global temp tables I'd feel for you. I'd want to find and kill those too on any new system I inherited. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 9, 2011 at 9:03 pm
Actually I was listening to one of the PASS videos on tempDB. When I opened my tempdb on my production and reporting db I saw a lot of them. In the video is said we could identify patterns of how the tempdb is being used. It says we could use the sys.dm_dB_XX (not sure) DMV to find the pattern.
I noticed that when I restart my reporting db its memory consumption gradually grows from 6Gb to 10GB(12 GB limit) in approximately 45 minutes. I was not sure how these are related if at all. So I randomly thought tempdb would help.
February 10, 2011 at 5:07 am
There are thee types of 'temp' tables, all stored in the tempDB database.
Global Temp Tables.
Created by CREATE TABLE ##MyTempTable AS ...
They are visible to all sessions. If not explicitly dropped, they are removed when all connections that reference them have disconnected from the server.
Visible in tempDB with the same name as they were created (##MyTempTable )
Local Temporary tables
Created by CREATE TABLE #MyTempTable AS ...
These are visible only to the connection that created them. If not explicity droped, they are dropped as soon as the connection that created them is closed, or the stored proc that created them completes (whichever occurs first)
Visible in tempDB with a name like #MyTempTable____________________________________________________________________________000000000002
Table variables
Created by DECLARE @myTableVar TABLE ...
Visible only in the scope they were created in. Dropped as soon as they go out of scope. Cannot be explicitly dropped.
Visible in tempdb with names like #0DAF0CB0
Courtesy:Gail Shaw
February 10, 2011 at 12:04 pm
Thanks Shaw . That's good information. One question, so if SQL Server needs to sort , I know it creates temp tables. Which of the three categories would it fall under?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply