How to locate usaged Temp Table of format like #3F3D11FA

Question

How to locate usaged Temp Table of format like #3F3D11FA

digitalox

SSCommitted

Points: 1830
More actions
June 17, 2009 at 9:23 am

#205604

Hello,
I have a temp table that shows up daily in the missing indexes report, provided by the performance dashboard report. The problem is that it in the form of tempdb.dbo.#3F3D11FA and I don't know how pin it down.
Any ideas on how I could find the actual name and/or proc that is executing it?
Thanks,
Scott

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That's a table variable and, other than unique constraints, it can't be indexed.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

digitalox SSCommitted Points: 1830 More actions · Answer 2

Ahh ok didn't know it was a table variable.

Is it the format of #xxxxxx that implies that is a table variable vs. temp table?

It still might be good to track it down and see if its better suited to a temp table. How would one approach that, is there enough info in the system views to track down the query associated with it?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

digitalox (6/17/2009)
Is it the format of #xxxxxx that implies that is a table variable vs. temp table?

Yes. Temp tables have a name in TempDB that includes the name given to them at creation along with lots of ___ and a hash at the end, table variables get a name made up of a # and then some hex characters.

Temp table: create table #MyTable (id int)

Actual name in TempDB:

#MyTable____________________________________________________________________________________________________________000000000002

Table variable: DECLARE @myTable TABLE (id int)

Actual name in TempDB:

#07020F21

It still might be good to track it down and see if its better suited to a temp table. How would one approach that, is there enough info in the system views to track down the query associated with it?

As far as I'm aware, no. In all query plans it'll be referenced by the name it was declared with. I don't know of any way to tie the physical name back to a query.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

digitalox SSCommitted Points: 1830 More actions · Answer 4

bummer that what i was afraid of. thanks for your help.

--

Scott

WayneS SSC Guru Points: 95460 More actions · Answer 5

digitalox (6/17/2009)
Hello,
I have a temp table that shows up daily in the missing indexes report, provided by the performance dashboard report. The problem is that it in the form of tempdb.dbo.#3F3D11FA and I don't know how pin it down.
Any ideas on how I could find the actual name and/or proc that is executing it?
Thanks,
Scott

That's a table variable and, other than unique constraints, it can't be indexed.
Gail Shaw

I wonder if the "dashboard report" is using the table variable?

Oh Gail... did you forget that table variables can have primary key constraints also?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

digitalox SSCommitted Points: 1830 More actions · Answer 6

I can tell by the columns its our code. Probably a proc that's stuffing more data than it needs to in a table variable and processing it. I know there's probably a lot of places that would benefit from an indexed temp table rather than a table variable.