Database Reference
In-Depth Information
To examine the indexes being stored, modify the original query against sys.dm_db_index_ physical_stats to
add another column, compressed_page_count .
SELECT i.Name,
i.type_desc,
s.page_count,
s.record_count,
s.index_level,
compressed_page_count
FROM sys.indexes i
JOIN sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2012'),
OBJECT_ID(N'Person.Address'),NULL,
NULL,'DETAILED') AS s
ON i.index_id = s.index_id
WHERE i.OBJECT_ID = OBJECT_ID(N'Person.Address');
Running the query, you get the results in Figure 9-13 .
Figure 9-13. sys.dm_db_index_physical_stats output about compressed indexes
For this index, you can see that the page compression was able to move the index from 106 pages to 25, of which
25 were compressed. The row type compression in this instance made a difference in the number of pages in the
index but was not nearly as dramatic as that of the page compression.
To see that compression works for you without any modification to code, run the following query:
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Newton'
AND a.PostalCode = 'V2M1N7';
The optimizer chose, on my system, to use the IXCompPageTest index. Even if I forced it to use the IXTest index
thusly, the performance was identical, although one extra page was read in the second query:
SELECT a.City,
a.PostalCode
FROM Person.Address AS a WITH (INDEX = IX_Test)
WHERE a.City = 'Newton'
AND a.PostalCode = 'V2M1N7';
Search WWH ::




Custom Search