Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a number of tables in tempdb named like

#A0030B77
#A01D908C
#A06D1FCB
#A0F72FB0
#A1614404
#A1EB53E9
#A205D8FE
#A255683D
  1. How (if) can I get spid of the process which created them?
  2. These tables are empty, but tempdb looks like full of data belonging to the tables and these tables occupy much space in the server's buffer cache - does anybody seen the issue?
  3. Space occupied by tempdb in the buffer cache is constantly growing up to 15-20Gb and then remains the same with relatively small deviations

Sql Server Enterprise 2012 SP1 x64 @ 24 cores, 64Gb RAM

Startup trace flags are:

253 - Prevents ad-hoc query plans to stay in cache

345 - Increase the accuracy of choice of optimum order when you join 6 or more tables.

834 - SQL 8+ – Causes SQL Server to use Windows large-page allocations for the memory that is allocated for the buffer pool

1117 - Grows all data files at once

1118 - Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent

1119 - Turns off mixed extent allocation.

2328 - SQL 9+ - Makes cardinality estimates upon resulting selectivity

2371 - SQL 10.5 SP1 – Before this automatic statistics were triggered when a column would get modifications exceeding 20% of the # of rows in the table. On enabling this flag the standard 20% changes to a dynamic value if table has more than 25000 rows & reduces as the count increases.

Why do I think that tables are empty:

Query

select database_id, file_id, total_page_count, allocated_extent_page_count,    version_store_reserved_page_count, user_object_reserved_page_count, internal_object_reserved_page_count, mixed_extent_page_count 
from sys.dm_db_file_space_usage

returns me

╔═════════════╦═════════╦══════════════════╦═════════════════════════════╦═══════════════════════════════════╦═════════════════════════════════╦═════════════════════════════════════╦═════════════════════════╗
║ database_id ║ file_id ║ total_page_count ║ allocated_extent_page_count ║ version_store_reserved_page_count ║ user_object_reserved_page_count ║ internal_object_reserved_page_count ║ mixed_extent_page_count ║
╠═════════════╬═════════╬══════════════════╬═════════════════════════════╬═══════════════════════════════════╬═════════════════════════════════╬═════════════════════════════════════╬═════════════════════════╣
║           2 ║       1 ║          1024000 ║                         712 ║                                 8 ║                             344 ║                                  88 ║                     272 ║
║           2 ║       3 ║          1024000 ║                         392 ║                                 8 ║                             184 ║                                 144 ║                      56 ║
║           2 ║       4 ║          1024000 ║                         344 ║                                24 ║                             168 ║                                 104 ║                      48 ║
║           2 ║       5 ║          1024000 ║                         432 ║                                24 ║                             192 ║                                 168 ║                      48 ║
╚═════════════╩═════════╩══════════════════╩═════════════════════════════╩═══════════════════════════════════╩═════════════════════════════════╩═════════════════════════════════════╩═════════════════════════╝

Queries

select * from sys.dm_db_task_space_usage
select * from sys.dm_db_session_space_usage

Also show nothing special, but 15Gb of tempdb is allocated and query

SELECT * FROM sys.dm_os_buffer_descriptors
WHERE database_id = 2

Shows many rows, which looks like this:

+-------------+---------+---------+------------+---------------------+---------------+-----------+---------------------+-------------+-----------+---------------+
| database_id | file_id | page_id | page_level | allocation_unit_id  | page_type     | row_count | free_space_in_bytes | is_modified | numa_node | read_microsec |
| 2           | 4       | 287456  | 0          | 2449958198646734848 | DATA_PAGE     | 3         | 7737                | 1           | 0         | 0             |
| 2           | 5       | 363824  | 0          | 2738188574962286592 | DATA_PAGE     | 0         | 8096                | 1           | 0         | 0             |
| 2           | 3       | 290160  | 0          | 2810246169041174528 | DATA_PAGE     | 0         | 8096                | 1           | 1         | 0             |
| 2           | 4       | 40447   | 0          | 71918033664475136   | TEXT_MIX_PAGE | 1         | 40                  | 0           | 0         | 5907          |
| 2           | 1       | 373352  | 0          | 2449958198646734848 | DATA_PAGE     | 1         | 7953                | 1           | 0         | 0             |
| 2           | 4       | 301856  | 0          | 2305843010488958976 | DATA_PAGE     | 1         | 7953                | 1           | 1         | 0             |
| 2           | 3       | 374544  | 0          | 2449958198646734848 | DATA_PAGE     | 1         | 7999                | 1           | 0         | 0             |
| 2           | 5       | 285352  | 0          | 2738188574962286592 | DATA_PAGE     | 0         | 8096                | 1           | 1         | 0             |
| 2           | 4       | 319496  | 0          | 2738188574962286592 | DATA_PAGE     | 0         | 8096                | 1           | 0         | 0             |
| 2           | 4       | 325000  | 0          | 2089670228252295168 | DATA_PAGE     | 1         | 8054                | 1           | 1         | 0             |
| 2           | 3       | 407904  | 0          | 2449958198646734848 | DATA_PAGE     | 2         | 7880                | 1           | 1         | 0             |
+-------------+---------+---------+------------+---------------------+---------------+-----------+---------------------+-------------+-----------+---------------+

Notice, that the cache full of almost empty or empty pages.

Grouping by allocation_unit_id shows two major consumers and some thousands of minors:

SELECT ROW_NUMBER() OVER (ORDER BY cnt DESC) Ordinal, cnt*8/1024. MiB, *
FROM
(
    SELECT COUNT(*) cnt, allocation_unit_id
    FROM sys.dm_os_buffer_descriptors
    WHERE database_id = 2
    GROUP BY allocation_unit_id
) t

╔═════════╦═════════════╦════════╦═════════════════════╗
║ Ordinal ║     MiB     ║  cnt   ║ allocation_unit_id  ║
╠═════════╬═════════════╬════════╬═════════════════════╣
║       1 ║ 1390.000000 ║ 177920 ║   71916966883950592 ║
║       2 ║ 639.445312  ║  81849 ║   71916967705182208 ║
║       3 ║ 60.773437   ║   7779 ║   71916956836495360 ║
║       4 ║ 44.937500   ║   5752 ║   71916944283402240 ║
...
║      73 ║ 40.296875   ║   5158 ║   71916956847374336 ║
║      74 ║ 39.843750   ║   5100 ║   71916959399804928 ║
║      75 ║ 39.820312   ║   5097 ║   71916956843769856 ║
║      76 ║ 39.320312   ║   5033 ║   71916953125126144 ║
║      77 ║ 38.945312   ║   4985 ║   71916956340387840 ║
║      78 ║ 38.054687   ║   4871 ║   71916953128927232 ║
║      79 ║ 37.843750   ║   4844 ║   71916956243656704 ║
║      80 ║ 37.718750   ║   4828 ║   71916959858032640 ║
║      81 ║ 35.929687   ║   4599 ║   71916948084097024 ║
║      82 ║ 35.820312   ║   4585 ║   71916956338618368 ║
║      83 ║ 33.929687   ║   4343 ║   71916961224392704 ║
║      84 ║ 33.804687   ║   4327 ║   71916961228980224 ║
║      85 ║ 32.117187   ║   4111 ║   71916944291987456 ║
║      86 ║ 31.945312   ║   4089 ║   71916943633874944 ║
║      87 ║ 31.476562   ║   4029 ║   71916943639379968 ║
║      88 ║ 30.828125   ║   3946 ║   71916922688045056 ║
║      89 ║ 30.828125   ║   3946 ║   71916922698334208 ║
║      90 ║ 30.164062   ║   3861 ║   71916922685685760 ║
║      91 ║ 30.007812   ║   3841 ║   71916944294084608 ║
║      92 ║ 29.140625   ║   3730 ║   71916954958036992 ║
║      93 ║ 29.078125   ║   3722 ║   71916932764401664 ║
║      94 ║ 28.789062   ║   3685 ║   71916959830704128 ║
║      95 ║ 28.460937   ║   3643 ║   71916948094779392 ║
║      96 ║ 27.539062   ║   3525 ║ 2882303763120062464 ║
║      97 ║ 27.328125   ║   3498 ║   71916954956857344 ║
║      98 ║ 26.164062   ║   3349 ║ 2522015792725622784 ║
║      99 ║ 25.804687   ║   3303 ║   71916961223999488 ║
║     100 ║ 24.710937   ║   3163 ║ 2161727822331183104 ║
║     101 ║ 24.359375   ║   3118 ║   71916942980874240 ║
║     102 ║ 22.703125   ║   2906 ║ 2810246169041174528 ║
║     103 ║ 22.289062   ║   2853 ║   71916948093730816 ║
║     104 ║ 21.929687   ║   2807 ║ 2449958198646734848 ║
║     105 ║ 21.687500   ║   2776 ║   71916959403147264 ║
║     106 ║ 21.492187   ║   2751 ║   71916968399732736 ║
║     107 ║ 21.484375   ║   2750 ║ 2089670228252295168 ║
║     108 ║ 20.476562   ║   2621 ║   71916957069344768 ║
║     109 ║ 20.476562   ║   2621 ║   71916959421693952 ║
║     110 ║ 20.476562   ║   2621 ║   71916962657009664 ║
...
║     181 ║ 19.960937   ║   2555 ║   71916959833194496 ║
...
║     226 ║ 10.132812   ║   1297 ║   71916950757441536 ║
...
║     304 ║ 8.093750    ║   1036 ║   71916967906115584 ║
...
║     624 ║ 4.234375    ║    542 ║   71916967720976384 ║
...
║     746 ║ 1.375000    ║    176 ║   71916958488330240 ║
...
║    1469 ║ 1.015625    ║    130 ║   71916967968243712 ║
...
║    2094 ║ 0.015625    ║      2 ║     422318827634688 ║
║    2095 ║ 0.015625    ║      2 ║   72057594037993472 ║
║    2096 ║ 0.015625    ║      2 ║     422318971682816 ║
║    2097 ║ 0.015625    ║      2 ║     422315916656640 ║
║    2098 ║ 0.015625    ║      2 ║     422319896985600 ║
║    2099 ║ 0.015625    ║      2 ║ 4971973991407812608 ║
║    2100 ║ 0.015625    ║      2 ║     422315915542528 ║
║    2101 ║ 0.015625    ║      2 ║     422213972721664 ║
║    2102 ║ 0.015625    ║      2 ║     422318988656640 ║
║    2103 ║ 0.015625    ║      2 ║     562949958270976 ║
║    2104 ║ 0.007812    ║      1 ║   72057604529192960 ║
║    2105 ║ 0.007812    ║      1 ║ 8430738512437313536 ║
║    2106 ║ 0.007812    ║      1 ║   71916964662673408 ║
║    2107 ║ 0.007812    ║      1 ║ 9079256864390184960 ║
║    2108 ║ 0.007812    ║      1 ║   72057594038059008 ║
║    2109 ║ 0.007812    ║      1 ║  144115198608080896 ║
║    2110 ║ 0.007812    ║      1 ║ 8863084076910641152 ║
║    2111 ║ 0.007812    ║      1 ║ 8502796111759081472 ║
║    2112 ║ 0.007812    ║      1 ║     422257658363904 ║
║    2113 ║ 0.007812    ║      1 ║  432345580166512640 ║
║    2114 ║ 0.007812    ║      1 ║ 8935141676232409088 ║
║    2115 ║ 0.007812    ║      1 ║  648518362403176448 ║
║    2116 ║ 0.007812    ║      1 ║   71916964300652544 ║
║    2117 ║ 0.007812    ║      1 ║  792633534832312320 ║
║    2118 ║ 0.007812    ║      1 ║   71916963469393920 ║
║    2119 ║ 0.007812    ║      1 ║   71916963916939264 ║
║    2120 ║ 0.007812    ║      1 ║  864691144639840256 ║
║    2121 ║ 0.007812    ║      1 ║   71635381590032384 ║
║    2122 ║ 0.007812    ║      1 ║   71635381592522752 ║
║    2123 ║ 0.007812    ║      1 ║ 7710162571648434176 ║
║    2124 ║ 0.007812    ║      1 ║  288230392008736768 ║
║    2125 ║ 0.007812    ║      1 ║ 8286623329522417664 ║
║    2126 ║ 0.007812    ║      1 ║   71916922101825536 ║
║    2127 ║ 0.007812    ║      1 ║  720575956482064384 ║
║    2128 ║ 0.007812    ║      1 ║ 8646911299916857344 ║
║    2129 ║ 0.007812    ║      1 ║ 8214565730200649728 ║
║    2130 ║ 0.007812    ║      1 ║ 1585267069700079616 ║
║    2131 ║ 0.007812    ║      1 ║ 8791026482831753216 ║
║    2132 ║ 0.007812    ║      1 ║ 6701356259786883072 ║
║    2133 ║ 0.007812    ║      1 ║   71916964978884608 ║
║    2134 ║ 0.007812    ║      1 ║     422212465262592 ║
║    2135 ║ 0.007812    ║      1 ║   71916963851272192 ║
║    2136 ║ 0.007812    ║      1 ║   71916964848009216 ║
║    2137 ║ 0.007812    ║      1 ║ 1729382257857855488 ║
║    2138 ║ 0.007812    ║      1 ║   72057594038779904 ║
║    2139 ║ 0.007812    ║      1 ║     422212471226368 ║
║    2140 ║ 0.007812    ║      1 ║     422257559732224 ║
║    2141 ║ 0.007812    ║      1 ║  216172792686968832 ║
║    2142 ║ 0.007812    ║      1 ║  360287980844744704 ║
║    2143 ║ 0.007812    ║      1 ║   71916952295768064 ║
║    2144 ║ 0.007812    ║      1 ║   72057594038976512 ║
║    2145 ║ 0.007812    ║      1 ║   71916943643181056 ║
║    2146 ║ 0.007812    ║      1 ║ 4323455644697821184 ║
║    2147 ║ 0.007812    ║      1 ║   71916963191324672 ║
║    2148 ║ 0.007812    ║      1 ║ 8863084082153521152 ║
║    2149 ║ 0.007812    ║      1 ║  576460768324288512 ║
║    2150 ║ 0.007812    ║      1 ║ 8142508136121761792 ║
║    2151 ║ 0.007812    ║      1 ║   72057609772072960 ║
║    2152 ║ 0.007812    ║      1 ║  360287986087624704 ║
║    2153 ║ 0.007812    ║      1 ║     426763273109504 ║
║    2154 ║ 0.007812    ║      1 ║  288230386765856768 ║
║    2155 ║ 0.007812    ║      1 ║   71916965281398784 ║
║    2156 ║ 0.007812    ║      1 ║   71916963944726528 ║
║    2157 ║ 0.007812    ║      1 ║ 8574853705837969408 ║
║    2158 ║ 0.007812    ║      1 ║   71916964799905792 ║
║    2159 ║ 0.007812    ║      1 ║ 8574853700595089408 ║
║    2160 ║ 0.007812    ║      1 ║ 9151314458469072896 ║
║    2161 ║ 0.007812    ║      1 ║ 8214565735443529728 ║
║    2162 ║ 0.007812    ║      1 ║  216172797929848832 ║
║    2163 ║ 0.007812    ║      1 ║   71916951591649280 ║
║    2164 ║ 0.007812    ║      1 ║ 8791026488074633216 ║
║    2165 ║ 0.007812    ║      1 ║ 1657324663778967552 ║
║    2166 ║ 0.007812    ║      1 ║   71916956343140352 ║
║    2167 ║ 0.007812    ║      1 ║  504403174245400576 ║
║    2168 ║ 0.007812    ║      1 ║   71916964108697600 ║
║    2169 ║ 0.007812    ║      1 ║   71635381594423296 ║
║    2170 ║ 0.007812    ║      1 ║   71916965359255552 ║
║    2171 ║ 0.007812    ║      1 ║ 1801439851936743424 ║
║    2172 ║ 0.007812    ║      1 ║     422214309380096 ║
║    2173 ║ 0.007812    ║      1 ║   71916962753740800 ║
║    2174 ║ 0.007812    ║      1 ║   72057594038648832 ║
║    2175 ║ 0.007812    ║      1 ║ 8286623324279537664 ║
║    2176 ║ 0.007812    ║      1 ║ 8502796106516201472 ║
║    2177 ║ 0.007812    ║      1 ║   71916932751425536 ║
║    2178 ║ 0.007812    ║      1 ║     422217215180800 ║
║    2179 ║ 0.007812    ║      1 ║ 8718968888752865280 ║
║    2180 ║ 0.007812    ║      1 ║  792633550560952320 ║
║    2181 ║ 0.007812    ║      1 ║   71916963525361664 ║
║    2182 ║ 0.007812    ║      1 ║   71635381594488832 ║
║    2183 ║ 0.007812    ║      1 ║   71916964930584576 ║
║    2184 ║ 0.007812    ║      1 ║ 8646911294673977344 ║
║    2185 ║ 0.007812    ║      1 ║ 9007199265068417024 ║
║    2186 ║ 0.007812    ║      1 ║ 8935141670989529088 ║
║    2187 ║ 0.007812    ║      1 ║ 8718968893995745280 ║
║    2188 ║ 0.007812    ║      1 ║     422217523462144 ║
║    2189 ║ 0.007812    ║      1 ║ 8142508141364641792 ║
║    2190 ║ 0.007812    ║      1 ║ 3963167674303381504 ║
║    2191 ║ 0.007812    ║      1 ║   71916965500485632 ║
║    2192 ║ 0.007812    ║      1 ║ 9151314453226192896 ║
║    2193 ║ 0.007812    ║      1 ║ 6773413853865771008 ║
║    2194 ║ 0.007812    ║      1 ║ 8358680918358425600 ║
║    2195 ║ 0.007812    ║      1 ║   71916963493249024 ║
║    2196 ║ 0.007812    ║      1 ║  144115203850960896 ║
║    2197 ║ 0.007812    ║      1 ║ 8070450542042873856 ║
║    2198 ║ 0.007812    ║      1 ║ 9079256859147304960 ║
║    2199 ║ 0.007812    ║      1 ║ 9007199270311297024 ║
╚═════════╩═════════════╩════════╩═════════════════════╝
share|improve this question
    
Those are either table variables or cached temporary tables from a stored procedure. If the tables are empty what exactly is in the buffer pool related to them? –  Martin Smith Jan 20 '13 at 17:28
    
@MartinSmith Tried to fulfil the question with related data –  Oleg Dok Jan 20 '13 at 17:45
1  
If you group by allocation_unit_id are there any big consumers or do you have a lot of different allocation_unit_ids? (BTW: You might find something from SELECT SPID FROM tempdb.sys.fn_dblog(NULL,NULL) WHERE AllocUnitId = 2449958198646734848) if the active log still contains this info. –  Martin Smith Jan 20 '13 at 17:59
    
@MartinSmith Thanks for useful function! But unfortunately there is no any info left in the log. Updated question with the groupings –  Oleg Dok Jan 20 '13 at 18:19
1  
These tables are cached temp tables as they are named based on the hex version of the object_id. This post has some details on the cache process sqlblog.com/blogs/paul_white/archive/2012/08/17/… –  MartinC Jan 21 '13 at 14:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.