what's MEMORYBROKER_FOR_HASHED_DATA_PAGES in memory_brokers

Question

what's MEMORYBROKER_FOR_HASHED_DATA_PAGES in memory_brokers??

Answer 1

Pages of Hashed data in the memory.

Answer 2

Hi @sakuraime ,

what's it for ?

All the memories and pages are used to keep data, but different space for difference data.

and what generate it ?

The question is same as how to geneate hash data, you can use hash function to generate it.hashbytes-transact-sql

More information: the-hashbytes-function-in-t-sql

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @sakuraime ,

what about hash join ? hash join will use this area ? MEMORYBROKER_FOR_HASHED_DATA_PAGES

Hash join will not use this area from my test.You can test on your own side.

Test code as next:

--restart sql service and then check the space use of tempdb  
use tempdb  
go  
  
sp_helpdb tempdb  
  
select 'tempdb' as db,getdate() as time,  
sum(user_object_reserved_page_count) * 8 as user_objects_kb, --temp table use  
sum(internal_object_reserved_page_count)*8 as internal_objects_kb,--hash use  
sum(version_store_reserved_page_count)*8 as version_store_kb,--version store use  
sum(unallocated_extent_page_count)*8 as free_space_kb from sys.dm_db_file_space_usage where database_id =2   
  
SELECT pool_id,  
	memory_broker_type,  
	allocations_kb,  
	allocations_kb_per_sec,  
	predicted_allocations_kb,  
	target_allocations_kb,  
	future_allocations_kb,  
	overall_limit_kb,  
	last_notification  
FROM sys.dm_os_memory_brokers  
  
--hash join test  
  
--create test table as next  
use AdventureWorks2017  
go  
  
drop table Sales.SalesOrderHeader_test  
go  
  
drop table Sales.SalesOrderDetail_test  
go  
  
select * into Sales.SalesOrderHeader_test from Sales.SalesOrderHeader  
go  
  
select * into Sales.SalesOrderDetail_test from Sales.SalesOrderDetail  
go  
  
Create clustered index SalesOrderHeader_test_cl on Sales.SalesOrderHeader_test (SalesOrderID)  
go  
  
Create clustered index SalesOrderDetail_test_ncl on Sales.SalesOrderDetail_test (SalesOrderID)  
go  
  
--hash join test  
select count(b.SalesOrderID) from Sales.SalesOrderHeader_test a  
inner hash join Sales.SalesOrderDetail_test b  
on a.SalesOrderID = b.SalesOrderID  
where a.SalesOrderID > 1 and a.SalesOrderID < 53660  
go  
  
---execute code as begin  
use tempdb  
go  
sp_helpdb tempdb  
  
select 'tempdb' as db,getdate() as time,  
sum(user_object_reserved_page_count) * 8 as user_objects_kb, --temp table use  
sum(internal_object_reserved_page_count)*8 as internal_objects_kb,--hash use  
sum(version_store_reserved_page_count)*8 as version_store_kb,--version store use  
sum(unallocated_extent_page_count)*8 as free_space_kb from sys.dm_db_file_space_usage where database_id =2   
  
SELECT pool_id,  
	memory_broker_type,  
	allocations_kb,  
	allocations_kb_per_sec,  
	predicted_allocations_kb,  
	target_allocations_kb,  
	future_allocations_kb,  
	overall_limit_kb,  
	last_notification  
FROM sys.dm_os_memory_brokers  

First data of memorybroker_for_hashed_data_pages is 0 allocated:

After hash join test, the values is also 0:

Any size limitation of MEMORYBROKER_FOR_HASHED_DATA_PAGES? for example % of max_server_memory ?

Yes. Execute code as next,the values of the overall_limit_kb is its max value. Test on your side, its affected by your max_server_memory, but not its max_server_memory value, on my side, its about 22.5% * max_server_memory:

SELECT pool_id,  
	memory_broker_type,  
	allocations_kb,  
	allocations_kb_per_sec,  
	predicted_allocations_kb,  
	target_allocations_kb,  
	future_allocations_kb,  
	overall_limit_kb,  
	last_notification  
FROM sys.dm_os_memory_brokers  

BR,
Mia