All geek questions in one place

MS SQL Server 2008 Cache Bit Fields? Those. VARBINARY

MS SQL Server 2008 Cache Bit Fields? Those. VARBINARY

We have MS SQL Server 2008 R2 using 12 GB of RAM. Our database includes over 200 GB of VARBINARY. Will it also be cached?

EDIT: SQL Server really caches binary data. Thanks @Martin

Can you tell SQL Server to stop caching binary data, or limit it in some way?

+4
source share
1 answer

Yes. Try

WITH T AS (SELECT database_id, page_type, total_page_count = COUNT(*) OVER (), page_count = COUNT(*) OVER (PARTITION BY database_id, page_type), row_num = ROW_NUMBER() OVER (PARTITION BY database_id, page_type ORDER BY (SELECT 0)) FROM sys.dm_os_buffer_descriptors) SELECT page_type, page_count, PercentageOfCache = ROUND(1E2 * page_count / total_page_count, 2), SizeInCache = CASE WHEN page_count > 131072 THEN LTRIM(STR(page_count / 131072.0) + ' GB') WHEN page_count > 128 THEN LTRIM(STR(page_count / 128.0) + ' MB') ELSE LTRIM(STR(page_count * 8) + ' KB') END FROM T WHERE database_id = DB_ID() AND row_num = 1 ORDER BY page_count DESC

To find out how many pages are currently in the buffer cache for your database.

The TEXT_MIX_PAGE LOB page types are TEXT_MIX_PAGE and TEXT_TREE_PAGE , but LOB data can also be stored on data pages.

+3
source

Source: https://habr.com/ru/post/1343079/

More articles:


All Articles