Microsoft SQL Server 2008, Implementation and Maintenance

  By Certification Magazine —

You need to verify the integrity of the nonclustered indexes. The operation must complete as quickly as possible. What should you do?

A.    Execute DBCC CHECKFILEGROUP FG1.
B.    Execute DBCC CHECKFILEGROUP FGIndex.
C.    Execute CHECKSUM SalesDB.
D.    Execute DBCC CHECKDB SalesDB.

Answer:
D

Tutorial:
You should execute DBCC CHECKDB SalesDB. Because the nonclustered indexes are stored on a different filegroup than the base tables, you need to execute DBCC CHECKDB to verify the integrity of the indexes.

You should not execute DBCC CHECKFILEGROUP FG1. The integrity of a nonclustered index does not matter when validating the integrity of a base table. Therefore, nonclustered indexes will only be checked if they are located in the same filegroup as the base table.

You should not execute DBCC CHECKFILEGROUP FGIndex. In versions prior to SQL Server 2005, you could execute CHECKFILEGROUP on the filegroup containing the index to check the integrity of indexes even if the base tables are stored in different filegroups. However, this functionality was changed in SQL Server 2005.

You should not execute CHECKSUM SalesDB. The CHECKSUM command generates a CHECKSUM for the object specified in the expression. It does not validate index integrity.

References:
DBCC CHECKFILEGROUP (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms187332(SQL.100).aspx
    
CHECKSUM (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms189788(SQL.100).aspx
    
DBCC CHECKDB (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms176064(SQL.100).aspx
    

Objective: Perform data management tasks.
Sub-objective: Implement data compression.

Single answer, multiple-choice

You manage a database hosted on an instance of SQL Server 2008. The database has a table named ProductSpecs. The ProductSpecs table is shown in the exhibit. The ProductID can have between three and 10 characters. A number of products have the same measurement in one or more dimensions. For example, nearly 100 products have either a height or width of 10 inches.

A clustered index is created on the ProductID column. The table has a very large number of rows. You want to reduce the amount of storage space required for the ProductSpecs table. You need to choose the best compression option. What should you do?

 
A.    Use row compression.
B.    Define ProductID as a sparse column.
C.    Use page compression.
D.    Define all columns as sparse columns.

Answer:
C

Tutorial:
You should use page compression. Page compression first uses row compression to compress a row of data. If multiple rows are contained on a page, it also uses dictionary compression for multiple instances of the same values. Because there are duplicate values in many of the fields, dictionary compression will help conserve space.

You should not use row compression. Row compression uses the smallest number of bytes to store a specific value. For example, the number 10 would be stored in a single byte. However, it is a subset of page compression, so you would not get the added savings of combining instances of a duplicate value.

You should not use sparse columns. Sparse columns help conserve space for columns that allow nulls. All columns are required, so none allow nulls.

References:
Row Compression Implementation
Microsoft TechNet
http://technet.microsoft.com/en-us/library/cc280576(SQL.100).aspx
    
Page Compression Implementation
Microsoft TechNet
http://technet.microsoft.com/en-us/library/cc280464(SQL.100).aspx
    
Using Sparse Columns
Microsoft TechNet
http://technet.microsoft.com/en-us/library/cc280604(SQL.100).aspx

Objective: Optimize SQL Server performance.
Sub-objective: Implement Resource Governor.

Single answer, multiple-choice

You manage an instance of SQL Server 2008. You enable the Resource Governor and install a classifier function. Users report they cannot connect to SQL Server. You need to troubleshoot the problem while Resource Governor is running. What should you do?

A.    Connect to the instance by using a Dedicated Administrator Connection (DAC).
B.    Set the database to single user mode.
C.    Connect to the instance as a member of the sysadmin fixed server role.
D.    Set the database to restricted user mode.

Answer:
A

Tutorial:
You should connect to the instance by using a DAC. A DAC executes by using the internal resource pool, so it is not affected by problems with the classifier function or other Resource Governor configuration settings.

You should not set the database to single user mode. The Resource Governor does not run in single user mode, so while you can connect to the instance, you will not be able to resolve the problem. However, if the DAC is not enabled, you could use this method to disable Resource Governor to allow users to connect while you investigate the problem on a different instance.

You should not connect to the instance as a member of the sysadmin fixed server role. Members of the sysadmin fixed server role are still subject to classification, so you will not be able to connect.

You should not set the database to restricted user mode. Restricted user mode allows only members of db_owner, dbcreator and sysadmin roles to access the database, but it does not exempt the connection from being managed by Query Governor.

Reference:
Resource Governor Concepts
Microsoft TechNet
http://technet.microsoft.com/en-us/library/bb934084(SQL.100).aspx