SQL Server Performance Forum – Threads Archive
High Data Traffic Question
I’m a better than novice db designer, but I’ve got a new application that is giving me some concern. My client wants to use SQL mostly as a "VERY LARGE" data FIFO with permanent storage of tracking information. The db is mainly used for temporary storage of multi-media files (1-4mb nominal size, although 100mb will not be unheard of). Probably have as many as 1000 to 2000 per day being uploaded through the web site (asp.net) and downloaded by a vb.net application on local machines. After downloading, the multimedia files will be deleted from the db. My question is with this high level of traffic through the db, what sort of problems can I expect with reclaiming space on hard drive and in DB. I’ve read some comments that over time, i can expect heavy fragmentation. Can anyone direct we where to look or perhaps someone to contact on this? Thanks in advance. still waters, great photosThe suggestion would be to store the file path in the database rather than keeping directly on the database, this way you can gain performance based upon your environment and load of the files. And as you say the files will be deleted after they download at the places wherever required.
The best option would be to run regular dbcc checks and reindex will help to gain the performance. To reduce the fragmentation on data files you have to db reindexing and during the online traffic may be update stats will sustain the load. Just followup the tips on this website about database design. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
1-2k per day, even assumming over 8hr day, is still only 125-250/hour or 2-4/min. even with 100MB files should be easiliy within the capabilities of SQL.
i would also recommend using the file system to store the bulk data per satya suggestion.
fragementation is not an issue because you are working with large individual records. frag. is only an issue if you are working with many small rows that are scattered instead of localized in a few pages.
you bigger issue is the ability to handle multiple uploads concurrently, will probably require multiple disks, no more than 4-6 disks per scsi channel.
also be sure to clean of the log frequently
Thanks to both for the messages, youv’e calmed some of my concerns. still waters, great photos
]]>