I have a number of tables in tempdb named like
#A0030B77
#A01D908C
#A06D1FCB
#A0F72FB0
#A1614404
#A1EB53E9
#A205D8FE
#A255683D
- How (if) can I get spid of the process which created them?
- 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?
- 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 ║
╚═════════╩═════════════╩════════╩═════════════════════╝
allocation_unit_idare there any big consumers or do you have a lot of different allocation_unit_ids? (BTW: You might find something fromSELECT SPID FROM tempdb.sys.fn_dblog(NULL,NULL) WHERE AllocUnitId = 2449958198646734848) if the active log still contains this info. – Martin Smith Jan 20 at 17:59