How Approximate Are SQL Server 2022 XTP Hash Index Stats?
A documented dynamic management view (DMV) tells you when a memory-optimized hash index has too few buckets. The awkward detail is that Microsoft says the DMV scans the whole table, so it can take a long time on large tables.[1]
This post is part of the Undocumented Changes in SQL Server 2022 and 2025 series. The contents page lists every part in order.
In the undocumented DMV post, I left myself a note about sys.dm_db_xtp_hash_index_approx_stats. It appeared in SQL Server 2022 beside the documented sys.dm_db_xtp_hash_index_stats, and the name suggested a bounded version of the same hash index statistics.
I compared the returned values, elapsed time, and the result of lowering the scan limits.
What the two objects expose
The documented object, sys.dm_db_xtp_hash_index_stats, returns one row per memory-optimized hash index with bucket count, empty bucket count, average chain length and maximum chain length. Microsoft says those statistics are useful for tuning bucket counts and detecting many duplicate index keys.
The undocumented function was new in my SQL Server 2022 inventory and still has the same definition in the SQL Server 2025 files I captured. Its definition is short:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE FUNCTION sys.dm_db_xtp_hash_index_approx_stats ( @maxComputeTime int = 900, @maxRowsToScanPerBucket int = 10000 ) RETURNS TABLE AS RETURN SELECT * FROM OpenRowset(TABLE XTP_HASH_IDX_APPROX_STATS, @maxComputeTime, @maxRowsToScanPerBucket) AS H WHERE H.object_id > 0; |
sys.all_parameters on my SQL Server 2025 test instance showed the two parameters, both int: @maxComputeTime and @maxRowsToScanPerBucket. The catalogue did not expose their default values there, even though the captured definition shows 900 and 10000.
The function returns bucket_count, scanned_bucket_count, approx_filled_count, approx_avg_chain_length, approx_max_chain_length, bucket-range counters, and a counter named buckets_with_chain_length_greater_than_max_rows_to_scan_per_bucket.
The test table
I created one scratch database named undoc_spinoff_xtp on the SQL Server 2025 test instance. It has a memory-optimized data filegroup under the instance default data path. I did not drop the database after the test.
The table has 500,000 rows. One hash index has only 1,024 buckets, which is much too small for a unique key. The other has 1,048,576 buckets, which is deliberately larger than the row count.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
USE master; GO DECLARE @data_path nvarchar(4000) = CONVERT(nvarchar(4000), SERVERPROPERTY(N'InstanceDefaultDataPath')); IF RIGHT(@data_path, 1) <> N'\' SET @data_path += N'\'; IF DB_ID(N'undoc_spinoff_xtp') IS NULL BEGIN DECLARE @sql nvarchar(max) = N' CREATE DATABASE [undoc_spinoff_xtp] ON PRIMARY ( NAME = N''undoc_spinoff_xtp_data'' , FILENAME = N''' + REPLACE(@data_path, '''', '''''') + N'undoc_spinoff_xtp_data.mdf'' ), FILEGROUP [undoc_spinoff_xtp_mod] CONTAINS MEMORY_OPTIMIZED_DATA ( NAME = N''undoc_spinoff_xtp_mod'' , FILENAME = N''' + REPLACE(@data_path, '''', '''''') + N'undoc_spinoff_xtp_mod'' ) LOG ON ( NAME = N''undoc_spinoff_xtp_log'' , FILENAME = N''' + REPLACE(@data_path, '''', '''''') + N'undoc_spinoff_xtp_log.ldf'' );'; EXEC sys.sp_executesql @sql; END; GO USE undoc_spinoff_xtp; GO DROP TABLE IF EXISTS dbo.HashIndexProbe; GO CREATE TABLE dbo.HashIndexProbe ( row_id int NOT NULL , large_key int NOT NULL , filler char(20) NOT NULL CONSTRAINT DF_HashIndexProbe_filler DEFAULT 'x' , CONSTRAINT PK_HashIndexProbe PRIMARY KEY NONCLUSTERED (row_id) , INDEX ix_bucket_count_too_small HASH (row_id) WITH (BUCKET_COUNT = 1024) , INDEX ix_bucket_count_too_large HASH (large_key) WITH (BUCKET_COUNT = 1048576) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); GO INSERT INTO dbo.HashIndexProbe (row_id, large_key) SELECT TOP (500000) [n] = CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) , [n] = CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b; GO |
Microsoft’s hash index guidance says the ideal bucket count is usually between one and two times the number of distinct index key values. It also says a bucket count below ten percent empty is too low, while larger empty-bucket percentages can be fine.[2]
The last batch loads 500,000 rows, with row_id and large_key both running from 1 to 500,000. Then I ran each query five times and measured elapsed time with SYSDATETIME(). I use the timing only to compare these four calls in this setup, and I do not treat it as a benchmark.
What matched
With the default parameters, the approximate function returned the same comparable values as the documented DMV for both test indexes.
| Index | Method | Buckets scanned | Filled buckets | Average chain | Maximum chain |
|---|---|---|---|---|---|
ix_bucket_count_too_large |
documented exact | 1,048,576 | 399,197 | 1 | 6 |
ix_bucket_count_too_large |
approx default | 1,048,576 | 399,197 | 1 | 6 |
ix_bucket_count_too_small |
documented exact | 1,024 | 1,024 | 488 | 553 |
ix_bucket_count_too_small |
approx default | 1,024 | 1,024 | 488 | 553 |
For these two indexes, either object pointed to the same conclusion. The oversized index had many empty buckets and small chain lengths. The undersized index had every bucket filled, with an average chain length of 488.
What changed when I lowered the bounds
The @maxRowsToScanPerBucket parameter matters. When I changed it from the default 10000 to 100, the undersized index no longer reported the real average or maximum chain length. It reported 100 for both, and all 1,024 buckets moved into the buckets_with_chain_length_greater_than_max_rows_to_scan_per_bucket counter.
With @maxRowsToScanPerBucket = 10, it reported 10 for the average and maximum chain length, with the same 1,024 capped buckets.
That looks like a bounded per-bucket scan rather than a statistical estimate of the true chain length. The function still tells me the index is a problem, but it does not tell me whether the average chain length is 120 or 488 once I set the per-bucket cap to 100.
The @maxComputeTime parameter also matters, but not on both indexes in this run. With @maxComputeTime = 1 and @maxRowsToScanPerBucket = 10, the large-bucket index scanned 710,512 of its 1,048,576 buckets on the first run, while the small-bucket index still scanned all 1,024 buckets. I could not reproduce the partial scan later: five more runs scanned all 1,048,576 buckets. I would not rely on @maxComputeTime to cap the work in a predictable way.
Was it faster?
Not in the default case on this table.
| Method | Parameters | Runs | Average elapsed microseconds | Minimum | Maximum |
|---|---|---|---|---|---|
| Documented DMV | none | 5 | 864,356 | 290,051 | 2,174,520 |
| Approx function | defaults, 900, 10000 |
5 | 1,553,843 | 637,388 | 2,141,077 |
| Approx function | 900, 100 |
5 | 905,104 | 183,758 | 2,507,307 |
| Approx function | 1, 10 |
5 | 809,450 | 517,036 | 1,055,259 |
Those timings are noisy. They include a small table, a warm local test instance and whatever else the machine was doing. The default approximate call was slower in this test, but I am not turning that into a broader performance claim. The narrower result is that default approximation settings did not make this query faster here.
Lowering the bounds changed the output. The function capped the per-bucket chain values, and in one case it scanned fewer than all buckets.
How I would use this
I would still start with the documented DMV in normal troubleshooting. It has a Microsoft Learn page. Microsoft documents its permissions, and its output directly matches the tuning guidance.
The undocumented approximate function gave me the same numbers with default parameters in this small test. With lower limits, it gave me a bounded answer. It identified the high-chain index, but it did not distinguish the tested chain lengths once they exceeded the cap.
If I saw a large memory-optimized table where the documented DMV took too long, I might test this function in a lab to see whether the capped counters help triage. I would not put it in monitoring, because I could not find documentation for its behaviour or support status in the places I searched.
Have you seen sys.dm_db_xtp_hash_index_approx_stats used in a support script, or do you know of documentation my searches missed? Tell me on Bluesky or LinkedIn, and I’ll update the notes.
References
- sys.dm_db_xtp_hash_index_stats (Transact-SQL) - Microsoft Learn. Documents the supported XTP hash index statistics DMV, its columns, permissions and the warning that it scans the whole table. ↩
- Troubleshooting hash indexes for memory-optimized tables - Microsoft Learn. Gives hash index bucket count guidance and interpretation rules for empty buckets and chain length. ↩