Memory-optimized table variables appear to have no metadata or DMV entries - by Bob Beauchemin

Status : 


Sign in
to vote
ID 812286 Comments
Status Active Workarounds
Type Bug Repros 2
Opened 12/24/2013 2:58:36 PM
Access Restriction Public


Trying to track memory usage and metadata for memory-optimized table variables.

They don't seem to appear in sys.tables or sys.objects, either in the "current" database or in tempdb.
They don't seem to appear in any of the sys.dm_db_xtp_* dynamic management views either.

Where does the information on these variables appear?
Sign in to post a comment.
Posted by Kendra Little on 5/13/2014 at 10:21 AM
I just ran into this when doing a comparison between temp tables, table variables, and memory optimized table variables in 2014.

I agree with Bob that I don't think it has to be super granular. What I'd really want to know as an administrator is how actively they're being used and the net resources they're taking up at any given time. But an option to make it more granular would certainly be appreciated.
Posted by Jos de Bruijn [MSFT] on 12/30/2013 at 1:51 PM
Hi Bob,

Thanks for the further feedback. I agree that detailed stats about individual table variables would be useful for troubleshooting.
Which stats we are going to expose, and how, we will figure out when we get to this feature. We will definitely take your feedback into account.


Posted by Bob Beauchemin on 12/30/2013 at 12:42 PM
Hi Jos,

I was surprised because there is metadata for individual "vanilla" table variables (in tempdb.sys.tables, tempdb.sys.objects), even when they are created with strong table types. That metadata has a very short lifetime, but it's there, that's why I expected to see it for memopt variables.

I'm assuming that providing it for memory-optimized table variables might slow the process of creating them down (cause a bottleneck). So perhaps some indication of how many (dm_db_xtp-something) and what the oldest one was, etc. If providing metadata per-individual variable is too slow, perhaps having a switch that could turn that detailed metadata on (at the expense of perf) for troubleshooting, a la stats for natively compiled sprocs.

Cheers, Bob
Posted by Jos de Bruijn [MSFT] on 12/30/2013 at 10:28 AM
Thanks for submitting this feedback.

We currently do not show any information about specific table variables in the DMVs. This is a limitation in SQL14, that we plan to address in a future release.
What we are thinking about is to expose the memory utilization for individual table variables, and tie those to the sessions that created them. This will allow to much better troubleshoot memory issues stemming from table variable usage.

Is there any other information you would be interested in?

We would not want to expose variables in the catalog views, as they do not have associated metadata. Rather, the metadata is part of the table type, which is exposed through sys.table_types.

Note that table variables are in the memory space of the user database. They are in the single PGPOOL memory consumer, which you can see using dm_db_xtp_memory_consumers.

Jos de Bruijn - SQL Server PM