Performing Data Management Tasks Flashcards
Contoso has a very-high-volume transaction system. There is not enough memory on the database server to hold the active data set, so a very high number of read and write operations are hitting the disk drives directly. After adding several additional indexes, the performance still does not meet expectations. Unfortunately, the DBAs cannot find any more candidates for additional indexes. There isn’t enough money in the budget for additional memory, additional servers, or a server with more capacity. However, a new storage area network (SAN) has recently been implemented. What technology can Contoso use to increase performance?
- Replication
- Database mirroring
- Partitioning
- Log shipping
Partitioning
EXPLANATION:
You could partition the most heavily used tables, thus allowing you to spread the data across multiple files, which improves performance.
Exam Objective:
Performing Data Management Tasks
Exam SubObjective(s): Manage data partitions
Margie’s Travel wants to keep orders in their online transaction processing database for a maximum of 30 days from the date an order is placed. The orders table contains a column called OrderDate that contains the date an order was placed. How can the DBAs at Margie’s Travel move orders that are older than 30 days from the orders table with the least amount of impact on user transactions? (Choose two. Each answer represents a part of the solution.)
- Use the SWITCH operator to move data partitions containing data that is older than 30 days.
- Create a stored procedure that deletes any orders that are older than 30 days.
- Partition the order table using the partition function defined for a datetime data type using the OrderDate column.
- Create a job to delete orders that are older than 30 days.
- Use the SWITCH operator to move data partitions containing data that is older than 30 days.
- Partition the order table using the partition function defined for a datetime data type using the OrderDate column.
EXPLANATION:
Wide World Importers has a very large and active data warehouse that is required to be accessible to users 24 hours a day, 7 days a week. The DBA team needs to load new sets of data on a weekly basis to support business operations. Inserting large volumes of data would affect users unacceptably. Which feature should be used to minimize the impact while still handling the weekly data loads?
- The SWITCH operator within partitioning
- Database snapshots
- Database mirroring
- Transactional replication
The SWITCH operator within partitioning
EXPLANATION:
The SWITCH operator is designed to move partitions of data into a table without causing blocking.
Exam Objective:
Performing Data Management Tasks
Exam SubObjective(s): Manage data partitions.
Contoso Limited has a very-high-volume order entry system. Management has determined that orders should be maintained in the operational system for a maximum of six months before being archived. After data is archived from the table, it is loaded into the data warehouse. The data load occurs once per month. Which technology is the most appropriate choice for archiving data from the order entry system?
- Transactional replication
- Partitioning
- Database mirroring
- Database snapshots
Partitioning
EXPLANATION
By designing the table with partitioning, you can remove order data from the order entry system without affecting performance or concurrency. After you remove the partition from the table, you can load the data into the data warehouse.
Exam Objective:
Performing Data Management Tasks
Exam SubObjective(s): Manage data partitions.
Which options are not compatible with row or page compression? (Choose two. Each forms a separate answer.)
- A VARBINARY(MAX) column with the FILESTREAM property
- A table with a column set
- A column with a VARCHAR(MAX) data type
- A sparse column
- A table with a column set
- A sparse column
EXPLANATION:
You cannot use either row or page compression with a table that has a sparse column.
A column set is constructed for a group of sparse columns, so because row and page compression is incompatible with sparse columns, it is also incompatible with a column set.
Exam Objective:
Performing Data Management Tasks
Exam SubObjective(s): Implement data compression.
Columns with which properties cannot be sparse columns? (Choose two. Each forms a separate answer.)
- NULL
- FILESTREAM
- NOT FOR REPLICATION
- COLLATE
- FILESTREAM
- NOT FOR REPLICATION
EXPLANATION:
ROWGUIDCOL, IDENTITY, and FILESTREAM are not allowed to be used with SPARSE columns. Because the NOT FOR REPLICATION option is applied to an identity column, you cannot have a column that is SPARSE which also has the NOT FOR REPLICATION option. In addition, a column must allows NULLs in order to be designated as a sparse column.
Exam Objective:
Performing Data Management Tasks
Exam SubObjective(s): Implement data compression.
Fabrikam stores product information in the following table:
CREATE TABLE Products.Product
(ProductID int IDENTITY(1,1),
ProductName varchar(30) NOT NULL,
SKU char(8) NOT NULL,
Cost money NOT NULL,
ListPrice money NOT NULL,
ShortDescription varchar(200) NOT NULL,
LongDescription varchar(MAX) NULL,
CONSTRAINT pk_product PRIMARY KEY CLUSTERED (ProductID))
The table is queried either by ProductID, ProductName, or SKU. The application displays ProductName, SKU, ListPrice, and ShortDescription. The ProductID is also returned to facilitate any subsequent operations. Several thousand new products were recently added and now you have performance degradation. Which index should you implement to provide the greatest improvement in query performance?
- CREATE NONCLUSTERED INDEX idx_product ON Products.Product (ProductID, ProductName, SKU)
- CREATE NONCLUSTERED INDEX idx_product ON Products.Product (ProductName)
- CREATE NONCLUSTERED INDEX idx_product ON Products.Product (ProductName) INCLUDE (SKU, ListPrice, ShortDescription, ProductID)
- CREATE NONCLUSTERED INDEX idx_product ON Products.Product (ProductName, SKU, ProductID, ListPrice, ShortDescription)
CREATE NONCLUSTERED INDEX idx_product ON Products.Product (ProductName) INCLUDE (SKU, ListPrice, ShortDescription, ProductID)
EXPLANATION:
This index allows SQL Server to search on ProductName and return the entire result set for the query from the index instead of the table. In addition, the index is kept small by having only the ProductName column define the B-tree and upper levels of the index.
Exam Objective:
Performing Data Management Tasks
Exam SubObjective(s): Maintain indexes.
You are the Database Administrator at a retail company that supplies blanks and kits to pen turners. You are designing a database to store characteristics of the products offered. Each product has a variety of characteristics, but not all products have the same set of characteristics. You are planning the index strategy for the database. The most common query will be the following:
SELECT a.ProductName, b.ProductType, b.WoodSpecies, b.Color
FROM Products a INNER JOIN ProductAttributes b ON a.ProductID = b.ProductID
WHERE b.Color = “X”
Not all products have a Color attribute. Which index strategy would be the most efficient?
- A nonclustered index on Color that includes the ProductType and WoodSpecies columns
- A filtered, nonclustered index on Color
- A filtered, nonclustered index on Color that includes the ProductType and WoodSpecies columns
- A nonclustered index on Color
A filtered, nonclustered index on Color that includes the ProductType and WoodSpecies columns
EXPLANATION:
Because the Color column is nullable, the most efficient index for this query includes only the values that were not nullable. In addition, by including the ProductType and WoodSpecies columns, the query could be satisfied entirely by the index.
Exam Objective:
Performing Data Management Tasks
Exam SubObjective(s): Maintain indexes.
You are in charge of building the process that loads approximately 150 GB of data into the enterprise data warehouse every month.Every table in your data warehouse has at least eight indexes to support data analysis routines. You want to load the data directly into the tables as quickly as possible. Which operation provides the best performance improvement with the least amount of administrative effort?
- Use Integration Services to import the data.
- Use a BULK INSERT command.
- Disable and enable the indexes.
- Drop and recreate the indexes.
Disable and enable the indexes.
EXPLANATION:
By disabling the indexes prior to the load, you avoid all the overhead required to maintain the indexes. By using the disable/enable method, you do not have to maintain scripts to recreate the indexes following the data load.
Exam Objective:
Performing Data Management Tasks
Exam SubObjective(s): Maintain indexes.