Advice on using BLOB Data . . .

Last Post 20 Dec 2006 11:44 PM by JHunter. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

20 Dec 2006 03:12 PM

Our CTO decided that we will be modifying one of our primary applications to use the database as a form of file repository for BLOB data. Some of this data will be small as in 20K whereas others could be hundreds of MB in size.

My understanding is that the performance will not be great as the data must be written out to 8KB pages and this will be highly inefficent for access. I did search the web to find any documented proof for not using BLOBS this way, but couldn't find any. Has anyone ever seen Documented performance problems caused by using BLOB Data?

In case we actually implement it, has anyone come across any guidelines for using BLOB's that they may wish to pass on. I am leery about putting large files into the database, but haven't seen anything that says you can't. Are there limits to file sizes that are lower than the 2GB stated file size limit for BLOBS?

Thank you for your help,

New Member
New Member

20 Dec 2006 11:44 PM
Hi Jeff,

The first thing to be aware of, blob data is not stored inline, that is on the same page as the rest of the data (unless specifically instructed: see sp_tableoption in bol, "text in row").

Secondly, all "blob" data can be forced into a separate filegroup, which you can position on a different IO subsystem.

"blob" data is stored in "blob" pages, theses are indeed 8K in size (as are all SQL Server pages), if your application retrieves data efficently (ie only pulls the blob data it requires), performance shouldn't be an issue. I had to manage a database which contained two "blob" columns on each row (holding XML logs), although I think the largest was < 1MB.

SQL Server is *very* efficient at dealing in 8K pages (note that IO is done in 64K chunks).

The normal solution to avoiding blobs in the db is to store a url/unc path and place the object on a NAS. Or, if the data are images, store the thumbnail in the db, and place the full image on a server...

The maximum size for a blob is 2GB, multiple blobs can share the 8K pages, so the minimum "blob" size will be your smallest file.

Hope that helps...
New Member
New Member

23 Dec 2006 05:15 AM
There is no easy way of managing blob-s within SQL server other than storing them in the file system and referring to them via url/unc or purchase a 3rd party software for managing them.

Kalman Toth

Acceptable Use Policy