You are the database administrator of your company. The network contains an instance of SQL Server 2008 Enterprise Edition named SQL1.
SQL1 contains a database named EmpDetails.
You create a partition for acolumn named EmpID in the Employees table in the EmpDetails database.
You configure page compression on the partition.
You want to monitor the page compression statistics for the partition.
Which dynamic management function should you use?
A.
sys.dm_db_index_physical_stats
B.
sys.dm_db_index_usage_stats
C.
sys.dm_db_index_operational_stats
D.
sys.dm_db_partition_stats
Explanation:
You should use the sys.dm_db_index_operational_stats dynamic management function. SQL Server 2008Enterprise and Developer editions allow you to configure compression for tables and indexes. The two types ofdata compression are row compression and page compression. When you have configured partitions on a tableor index, you can configure different types of compression for each partition. You can use the DATA_COMPRESSION parameter of the CREATE TABLE , CREATE INDEX , ALTER TABLE , and ALTERINDEX Transact-SQL statements to configure data compression. The possible values for the DATA_COMPRESSION parameter are NONE , ROW , and PAGE . When you configure the page compression onpartitions, you can use the sys.dm_db_index_operational_stats dynamic management function to obtain pagecompression statistics. You should not use the sys.dm_db_index_physical_stats dynamic management function because this functiondoes not provide page compression statistics for a partition. The sys.dm_db_index_physical_stats functionreturns information about the size and fragmentation for the data and indexes of the specified table or view. You should not use the sys.dm_db_index_usage_stats dynamic management function because this functiondoes not provide page compression statistics for a partition. The sys.dm_db_index_usage_stats functionreturns information about the number of different types of index operations along with the time each type ofoperation was last performed. You should not use the sys.dm_db_partition_stats dynamic management function because this function doesnot provide page compression statistics for a partition. The sys.dm_db_partition_stats function returnsinformation about page and row counts for every partition in the current database.
Objective:
Performing Data Management TasksSub-Objective:
Implement data compression.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Tables > Creating and Modifying Tables > CreatingCompressed Tables and Indexes