Spatial index compression in SQL Server Denali

Since SQL Server Denali adds support for index compression
of spatial indexes, using the same options as data and index compression in SQL Server 2008 and above. As opposed to what I expected at first (because spatial indexes consist of only a few small fixed-length columns), using compression with spatial indexes appears to be quite effective. With my
cursory tests, I obtained between 25-40% reduction in size with ROW compression
and 50-90% reduction in size with PAGE compression. Queries that use a
compressed spatial index execute in about the same (in some cases, less) CPU and
Elapsed time as the same queries using uncompressed spatial indexes. As spatial
indexes can be quite large depending on the MAX_CELLS_PER_OBJECT and density
settings, and the makeup and size of the spatial data set being indexed, compression of
spatial indexes (which is restricted to SQL Server Enterprise and above
editions) is a good space-saving choice.


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.