BLOBs and buffer cache

Question

BLOBs and buffer cache

kevkaz

Old Hand

Points: 335
More actions
March 27, 2003 at 4:12 pm

#63273

We're thinking about implementing a table containing BLOBs (image data type). Lets say we'll add 5k-6k images averaging 40Kb each weekday. Most all of these images would be read 1-3 times later during the same day, then probably never again. Nevertheless, I need to keep them for archival purposes. So I'll probably partition the data and keep the older stuff physically separate from the new. My real concern is the buffer cache. Does SQL Server retain BLOB pages in memory like it would for any other data page? If so, I'm a bit worried that other pages for other DBs are going to get bumped out more frequently and cause trouble. More memory is certainly one option. Any thoughts?
Thanks
Kevkaz

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

I don't really know if blob data pages are cached as other pages, but I don't think it would be a problem for you since they are only used a couple of times a day. Other pages used more (i.e. data pages for other tables) would be used more and therefore have a higher priority in the cache than the blob pages, so the blob pages would be the ones that gets bumped. More memory is always a good option though.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

zach_john SSCertifiable Points: 5922 More actions · Answer 2

The following should answer your question

You can use the DBCC BUFFER console command to print out the buffer headers and pages from the buffer cache.

Syntax

DBCC BUFFER ( [@dbid|'@dbname' ]

[,@objid|obname ] [,@numofbuffers]

[,@printopt] )

Example

--Dump contents of buffers (DBCC DROPCLEANBUFFERS )

--Clean up the display

SET NOCOUNT ON

--Trace Flag 3604 must be on in order for DBCC command to run

DBCC TRACEON (3604)

--Use the pubs database

USE pubs

--Determine and hold database id for the pubs database

DECLARE @numDBID INTEGER

SET @numDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'pubs')

--Determine and hold object id for the authors table

DECLARE @numOBID INTEGER

SET @numOBID = OBJECT_ID('dbo.authors')

--Fill buffers

SELECT COUNT(*) FROM dbo.authors

--Display buffers

DBCC BUFFER (@numDBID ,@numOBID,1, 1 )

GO

John Zacharkan

kevkaz Old Hand Points: 335 More actions · Answer 3

It appears that BLOB pages are cached just the same as any other page. Thanks for your help.

quote:
The following should answer your question
You can use the DBCC BUFFER console command to print out the buffer headers and pages from the buffer cache.
Syntax
DBCC BUFFER ( [@dbid|'@dbname' ]
[,@objid|obname ] [,@numofbuffers]
[,@printopt] )
Example
--Dump contents of buffers (DBCC DROPCLEANBUFFERS )
--Clean up the display
SET NOCOUNT ON
--Trace Flag 3604 must be on in order for DBCC command to run
DBCC TRACEON (3604)
--Use the pubs database
USE pubs
--Determine and hold database id for the pubs database
DECLARE @numDBID INTEGER
SET @numDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'pubs')
--Determine and hold object id for the authors table
DECLARE @numOBID INTEGER
SET @numOBID = OBJECT_ID('dbo.authors')
--Fill buffers
SELECT COUNT(*) FROM dbo.authors
--Display buffers
DBCC BUFFER (@numDBID ,@numOBID,1, 1 )
GO
John Zacharkan

zach_john SSCertifiable Points: 5922 More actions · Answer 4

zach_john

SSCertifiable

Points: 5922

April 2, 2003 at 9:01 am

#452842

My pleasure

John Zacharkan

DickBaker Old Hand Points: 334 More actions · Answer 5

I seriously recommend that you create a separate FILEGROUP (say BLOBGROUP) and use the

CREATE TABLE table_name ( ...) ON DEFAULT TEXTIMAGE_ON BLOBGROUP

this means that the physical storage of blobs is kept away from the "small" data. [MSSQL has not always excelled at managing mixed extents]

Dick