Blob Datatypes and Performance Issues
-
I am a relatively new DBA and have a couple of questions about using Blob datatypes and it's effect on other high transaction databases on the server.
1. I remember reading that Blob datatypes can cause corruption problems. What are these problems and do they effect just the database or the entire server?
2. If a word document with a macro virus attached is placed in a blob datatype, will the virus infect the system?
3. When a powerpoint file larger than 65KB is inserted into the database, a size exceeded error occurs. I believe that changing the network packet size property in SQL Server will solve this problem. Is this true and what are the ramifications on the performance of the other databases on the server in changing this setting?
4. Are there any other issues in using blobs in databases besides the slowness of stored procedures using inserts and updates?
Thank you
Rosalind Philips
Office of the Superintendet of Public Instruction, Olympia WA
-
My understanding is this:
1) Blobs themselves don't cause corruption problems. Transaction handling and "chunking" need to be handled carefully, though.
2) Macro virus' are only problems when the macro is in an environment to run. Copying files on the disk or putting them into the database does not activate the virus.
3) Seems like SQL said the limit on each "chunk" for text, ntext and image types is 64K. I don't believe that changes with the network packet size. I have found it to fail using 64K and had to back off (I use 62K)
4a) Size is a major issue. Until you truncate the log or back it up, you have twice the disk volume (1 in the data file, 1 in the transaction log).
4b) SQL Backups are affected.
4c) Files under 20K seem to save faster to the database than to disk (on my machine - 100 vs 130) Files at 50K were slower (125 vs 100)
4d) Depends on how much you are accessing the information and in what manner. Like everything else - this answer is highly dependant on the specific application. That's why you get paid the big bucks.
Guarddata-
-
Agree with all that, though I havent dont specific timings on file sizes. Not a bad idea to consider storing the blobs in a separate db, though that brings up sync issues if you have to do a restore. We store all our articles plus the associated images as blobs here at SSC, but we also write it all to disk. We get the advantages of having it all in the db for backup and querying, don't have to task the db hit of looking it up each time. Don't know that it had to be that, we're just conservative!
Andy
-
Thank you very much for your responses. How do you deal with images and powerpoint presentations that are larger than 64K?
-
If you use the ADO stream object, it handles all the chunking for you. Or plenty of examples on MSDN showing how to do the chunks manually (use the stream).
Andy
-
Great! That does the trick!
-
I have developed applications (and currently developing applications) that put large amounts of large blobs on a MS SQL Server database and I have not encountered any problems.
I have no problems having blobs with sizes > 5MB with row counts in the hundreds of thousands.
One thing that one can do to optimize things a little is to do some horizontal partitioning and put the blob columns on separate tables. I hear this minimizes logging the blob columns whenever the other non-blob columns are updated.
-
Couple of things I've learned, I'm working on a similar project. I'm assuming you're running SS 2000.
1) Using the bulk-logged recovery model will minimally log BLOB inserts (see BOL for details). If point-in-time recovery isn't critical than this may be the way for you to go.
2) You can put the BLOB data in a separate file/filegroup. This allows you to backup that data separately and at a different frequency. You could also use views to partition the BLOB data to reduce the size/duration of BLOB backups.
Hope this helps!
kevkaz
-
quote:
2) You can put the BLOB data in a separate file/filegroup. This allows you to backup that data separately and at a different frequency. You could also use views to partition the BLOB data to reduce the size/duration of BLOB backups.re-read my post and maybe I wasn't clear. Say you have 12 partitions, 1 for each month. once you've completed one month, take a final backup and begin writing to the next part, and repeat. Your backups are never for more than 1 month of data. Otherwise full backups will start to take forever once the DB gets big.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply