Friday, April 9, 2010

[Tuning] SPARSE varchar calculation

Since it doesn't appear that anybody has done this before, here you go. I've been comparing SPARSE to COMPRESSION, and for my particular tables, I got 25% space savings via parse. However, I got 40% savings from ROW compression, and 50% savings from PAGE.


Standard
=(Number_Of_Rows*(Average_Varchar_Length+2)
*((100-Percent_Null)/100))
+(Number_Of_Rows*(Percent_Null/100*2))

Sparse:
=(Number_Of_Rows*(Average_Varchar_Length+4))*((100-Percent_Null)/100)



Next up is comparing the CPU for each option. Nobody's really talked about whether the compression is symmetric or asymmetric, though I'd hope it's asymmetric (aka easier to decompress than compress, in this case)

No comments: