Archive for April 28th, 2010

CHECKSUM Vs FileStream April 28th, 2010

Vinod Kumar

In the previous posts I have discussed briefly about the CHECKSUM options. Fundamentally, Database options allow you to establish checksum, torn page or no audit detection levels at a database level. File stream file groups do not apply the database level options to the files it stores as part of NTFS volume.

It makes sense, as to checksum files would require the SQL Server process to read a file and calculate the checksum as it is streaming the big chunk from disk.  This could be a very expensive operation as file streams is targeted at files in excess of 1MB, document and video storage. This increased load on the system could quickly become a performance factor. Inline stored blobs receive checksum at the individual page level, reducing the overhead and spreading the activity.

Sometimes, looking at technology in isolation and not understanding its impact on other subsystems can become fatal in our core understanding of the features used.

Continue reading...