Review Questions | MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (Exam 70-444) Study Guide

You are a database administrator responsible for maintaining a SQL Server 2005 instance. You get a call from the corporate help desk saying that database users are complaining about poor query performance. You need to see what queries are currently consuming the most SQL Server 2005 resources. What do you do?

Use SQL Server Profiler using the Tuning template.
Use SQL Server Profiler using the TSQL_Duration template.
Query the sys.dm_exec_query_stats DMV.
Query the sys.dm_exec_query_plan DMV.

You are the database administrator for you company. Marion, a junior developer, has asked you to tune a store procedure. The stored procedure reads a [Products] table four times to calculate four different reports. The problem is that because the stored procedure takes so long to run, new products can be inserted between the calculations of these four reports, which leads to an inconsistency between the reports. After reading this book, Marion has assumed that this can be solved by changing the stored procedure’s transaction isolation level. What transaction isolation level should you use?

UNCOMMITTED READ
COMMITTED READ
REPEATABLE READ
SERIALIZABLE

You are a database administrator responsible for tuning a database on a SQL Server 2005 instance. What tool should you use to improve the performance of your database?

SQL Server Profiler
Database Engine Tuning Advisor
Index Tuning Wizard
DTSRUN.EXE

You are a database administrator for your company. Your company has purchased a third-party application that uses SQL Server 2005. The third-party application generates T-SQL code on the fly, which is submitted as ad hoc queries. Performance has been abysmal for a number of these queries. You have monitored the database using SQL Server Profiler and have captured a representative trace. Your early conclusion points to poor indexing and suboptimal queries. What should you do? (Each correct answer represents part of the solution. Choose two.)

Use optimizer hints.
Use indexed views.
Use plan guides.
Use partitioning.
Use the Database Engine Tuning Advisor.
Use the SQL Server 2005 Upgrade Advisor.

You are a database administrator for a SQL Server 2005 instance that is experiencing a lot of deadlocks. What trace flag do you turn on to get more information about all nodes involved in the deadlocks that are occurring?

3604
3605
1204
1205

You are a database administrator responsible for a SQL Server 2005 instance that is driving a website responsible for market trades. Performance is critical, and users connected to the system must get the latest data. Data is being modified continuously. You have noticed that readers are being blocked by writers, and this is becoming a serious problem. Readers typically read a few rows at a time, whereas writers only ever modify a single row at a time. Transactions are never committed. What should you use to solve the concurrency issue?

Use the UNCOMMITTED READ isolation level.
Use the REPEATABLE READ isolation level.
Use snapshot isolation.
Use read committed snapshot isolation.

You are a database administrator for your company. A number of range queries are experiencing poor performance on a particular SQL Server 2005 instance. You have determined that this is due to lock escalation. This is causing further concurrency issues. SQL Server 2005 seems to be acquiring row-level locks that are always escalated. Additionally, you are getting errors indicating you have run out of memory for locks. You are planning to add more memory to the SQL Server 2005 instance but need to reduce these lock escalation problems today. What query hint should you use for these range queries to see whether it solves the problem?

READPAST
ROWLOCK
PAGLOCK
TABLOCK

You are the database administrator for your company. A database, using the default options, has been implemented on a SQL Server 2005 Enterprise Edition instance. While monitoring the performance of this database, you have noticed that the updating of statistics at certain periods of a particular table degrades performance unacceptably. You want to turn off automatic updating of statistics for this table only. What do you use?

Use the sp_autostats stored procedure.
Use the AUTO_UPDATE_STATISTICS database option.
Use the sp_createstats stored procedure.
Use the sp_updatestats stored procedure.

You are the database administrator for your company. A sales database solution has been implemented on a SQL Server 2005 instance. You have identified poor performance for the following query:

 SELECT SalesOrderID,        SUM(UnitPrice) AS TotalPrice FROM   Sales.SalesOrderDetail WITH (INDEX(0)) WHERE        ProductID BETWEEN @low AND @high GROUP BY        ProductID ;

The [SalesOrderDetails] table contains in excess of 1 billion rows. There is a clustered index on the [SalesOrderID] column and no nonclustered indexes. What should you do to improve performance? (Each correct answer represents part of the solution. Choose two.)

Create a nonclustered index on the [ProductId] column.
Change the hint to INDEX(1).
Remove the INDEX(0) hint.
Create a nonclustered index on the [ProductId] column, and include the [UnitPrice] column.
Create a nonclustered index on the [ProductId] and [UnitPrice] columns.
Add the FAST hint.

10.

You are the database administrator for your company managing a SQL Server 2005 solution. The [Products] table contains more than a million records. There are no indexes on the table at present. You need to create an indexing strategy for the following queries:

 -- Query 1 SELECT ProductCategory,           COUNT(*) FROM   Products GROUP BY ProductCategory -- Query 2 SELECT * FROM   Products ORDER BY ProductNumber -- Query 3 SELECT ProductNumber,        ProductName,        ProductCategory,           ProductStockLevel,           Discounted FROM   Products WHERE  ProductCategory = 'Nuclear'

What indexes should you create? (Each correct answer represents part of the solution. Choose two.)

Create a clustered index on the [ProductName] column.
Create a nonclustered index on the [ProductName] column.
Create a clustered index on the [ProductNumber] column
Create a nonclustered index on the [ProductNumber] column.
Create a nonclustered index on the [ProductCategory] column and include the [ProductStockLevel] and [ProductDiscounted] columns.
Create a nonclustered index on the [ProductNumber] column and include the [ProductStockLevel] and [ProductDiscounted] columns.
Create a nonclustered index on the [ProductNumber], [ProductName], [ProductCategory], [ProductStockLevel], and [ProductDiscounted] columns.
Create a nonclustered index on the [ProductNumber], [ProductName], [ProductCategory], and [ProductStockLevel] columns.

11.

You are a database administrator for your company. Your company has purchased a third-party application that uses SQL Server 2005 Express Edition. The third party uses stored procedures and views for all data access. Performance has been abysmal for a number of queries. The vendor has since gone bankrupt. You have monitored the database using SQL Server Pro-filer and have captured a representative trace. Your early conclusion points to suboptimal query plans. Indexing seems to be optimal and statistics are up-to-date. What should you do?

Use optimizer hints.
Use indexed views.
Use plan guides.
Use partitioning.

12.

You are the database administrator for KatsNuke, a nuclear power station in New Zealand. KatsNuke has a SQL Server 2005–based database solution that is being used to store real-time feeds from a nuclear reactor. A particular table has a lot of inserts being performed on it from these feeds, is queried often, and has a number of indexes on it. You have determined that the indexes become fragmented very quickly. Data insertions and query performance degrades throughout the day. Your indexes are rebuilt daily. What should you do when rebuilding the indexes?

Use a FILLFACTOR setting of 0.
Use a FILLFACTOR setting of 20.
Use a FILLFACTOR setting of 80.
Use a FILLFACTOR setting of 100.

13.

You are the database administrator for a call center that operates between 7 A.M. and 7 P.M. in Sydney, Australia. The call center’s database is implemented on a SQL Server 2005 instance. You have determined that page splits are causing performance problems for the [Customers] table. You have decided to apply a fill factor setting to the indexes on the [Customers] table. The average size of the rows in this table is 116 bytes. The primary key is a clustered index on the UNIQUEIDENTIFIER data type. How should you implement your fill factor strategy? (Each correct answer represents part of the solution. Choose three.)

Use a FILLFACTOR setting of 100.
Use a FILLFACTOR setting of 90.
Use a FILLFACTOR setting of 10.
Set the PAD_INDEX option to ON.
Set the PAD_INDEX option to OFF.
Rebuild the indexes daily.
Reorganize the indexes daily.
Rebuild the indexes weekly.
Reorganize the indexes weekly.

14.

You are a database administrator for your company. A SQL Server 2005 database solution has a complex stored procedure with hundreds of lines of code that is generating exceptions. The error could be generated at any of 69 lines of code within the stored procedure. You need to modify the stored procedure to appropriately trap and log any errors so that you can investigate the cause further. What mechanism should you use within the stored procedure to generate appropriate logging?

Set XACT_ABORT ON at the end of the stored procedure.
Use a CATCH/TRY block within the store procedure.
Use the RAISERROR statement after each line of code.
Set XACT_ABORT ON at the beginning of the stored procedure.

15.

You are the database administrator for your company. You have been asked to look at the exe-cution plan for a query running on SQL Server 2005. The query involves a complex join and a sort operation. You have noticed that the execution plan has a number of hash operations that are taking up the majority of the query’s execution time. What should you do to improve performance?

Use the SQL Server Profiler to trace for potential deadlocks.
Investigate what additional indexes can be created on the tables that are being used by the query.
Partition the tables used by the query.
Investigate what indexes can be dropped from the tables that are being used by the query.

16.

You are a database administrator for a SQL Server 2005 instance that has been experiencing a lot of deadlocks. A particular transaction that is critical to the business has sometimes been chosen as the victim, and this is not acceptable. What should you do to help prevent this from occurring?

Set DEADLOCK_PRIORITY for the transaction to LOW.
Run the DBCC OPENTRAN command in the transaction.
Set DEADLOCK_PRIORITY for the transaction to HIGH.
Use the FAST query hint in the transaction.

17.

You are a database administrator for your company. You are in charge of maintaining a VLDB based on a SQL Server 2005 instance. Automatic creating and updating of statistics has been turned off. All indexes are rebuilt nightly after hours. One morning, after enjoying your breakfast, your manager, Julie, indicates that database users have been having performance problems with reports that they run daily throughout the day. These reports use three stored procedures as follows:

 sp1 @CustomerID sp2 @OrderID, [@CustomerID] sp3 @OrderDate, [@CustomerID]

After investigating this further, you discover that a bulk load of data has occurred shortly after work began as a result of important data arriving. This bulk load of data affected the tables these stored procedures are accessing. What should you do?

Add the RECOMPILE option to the EXECUTE statement that calls the three stored procedures.
Turn on the AUTO_CREATE_STATISTICS database option.
Run the sp_updatestats stored procedure.
Add the RECOMPILE option to the three stored procedures.

18.

You are a database administrator for a SQL Server 2005 instance running in your company. The SQL Server 2005 instance is running a mission-critical system where performance is critical and cannot be impacted during business hours. You are seeking advice about how to tune a particular query that is run after-hours as part of an end-of-day process. This query takes more than an hour to run and is extremely resource intensive. You intend to email the query plan and all additional resource metrics to an external performance-tuning guru. You do not want to impact the performance of the SQL Server instance. What statement should you use?

SET SHOWPLAN_ALL ON
SET SHOWPLAN_TEXT ON
SET STATISTICS PROFILE ON
SET STATISTICS XML ON

19.

You are the database administrator for your company. Iris, a database user, complains about the performance of a query running on the SQL Server 2005 instance. Iris has been waiting for five minutes for the query to complete. You query the sys.dm_tran_locks and get the fol-lowing partial results:

Open table as spreadsheet

resource_type	resource_description	request_mode	request_status	request_session_id
KEY	(c39d32a242)	X	GRANT	70
KEY	(22ab2242aa)	X	GRANT	67
DATABASE		S	GRANT	69
KEY	(139111a288)	X	GRANT	67
DATABASE		S	GRANT	66
KEY	(239432a242)	X	GRANT	66
KEY	(2394324232)	X	GRANT	66
KEY	(2394324242)	U	WAIT	69
PAGE	1:24831	IX	GRANT
KEY	(34BD8999A4)		GRANT	67
PAGE	1:24831	IX	GRANT	66
KEY	(2394324242)		GRANT	66
PAGE	1:24831	IU	GRANT	69
PAGE	1:34553	IX	GRANT	70
METADATA		Sch-S	GRANT	66
METADATA		Sch-S	GRANT	69

What command do you run to determine who is the blocking process?

EXEC sp_who 66
EXEC sp_who 69
SELECT USER_NAME(66)
SELECT USER_NAME(69)

20.

You are a database administrator for a SQL Server 2005 instance that is experiencing a lot of deadlocks. You want to set up a SQL Server Profiler trace that will capture an XML description of the deadlocks. What event class should you capture?

Deadlock Graph
Lock:Deadlock Chain
Lock:Deadlock
Lock:Timeout (timeout > 0)

Answers

1.	C. The sys.dm_exec_query_stats DMV returns aggregate performance metrics about the cached execution plans. The sys.dm_exec_query_plan DMV will show you only the query plans. Using SQL Server Profiler is too late because it will show you only future traced events.
2.	D. The SERIALABLE transaction isolation level will prevent phantom values. New products will not be able to be inserted between the first and last queries that access the [Products] table.
3.	B. The Database Engine Tuning Advisor was designed to make recommendations about how performance can be improved on your SQL Server 2005 instance.
4.	C, E. Using the Database Engine Tuning Advisor will enable you to potentially create an optimal indexing strategy. Using plan guides will help with the application. One of the uses for plan guides is to help database administrators tune third-party applications where they cannot change the code.
5.	C. Trace flag 1204 returns deadlock information about each node involved in the deadlock.
6.	A. The UNCOMMITTED READ isolation level will not honor locks or acquire locks for read operations. Dirty reads should be all right because transactions are never committed and only ever modify a single row. The REPEATABLE READ isolation will cause greater contention. There is no need to go to SI or RCSI because readers might not be working with the latest data and there might be an overhead on the SQL Server 2005 instance.
7.	C. Page-level locks could reduce lock escalation and should consume less memory than row-level locks. Table-level locks would create too much contention. The READPAST query hint tells the query to skip locked data.
8.	A. The sp_autostats system stored procedure changes the automatic UPDATE STATISTICS setting for tables or indexes. The sp_updatestats system stored procedure updates the statistics for the entire database. The AUTO_UPDATE_STATISTICS database option turns off automatic statistics for the entire database. The sp_createstats system stored procedure creates statistics.
9.	C, D. Removing the INDEX(0) hint, creating a nonclustered index on the [ProductId] column, and including the [UnitPrice] column will be optimal. Creating a nonclustered index on the [ProductId] and [UnitPrice] columns will result in a bigger index. Changing the hint to INDEX(1), adding the FAST hint, or creating a nonclustered index on the [ProductId] column will not improve performance.
10.	C, E. C will be optimal for query 2. E will be optimal for queries 1 and 3.
11.	A. You can improve the performance of the queries through optimizer hints. Indexed views cannot be automatically used in SQL Server 2005 Express Edition. Plan guides and partitioning are not supported by SQL Server 2005 Express Edition.
12.	C. Using a FILLFACTOR setting of 80 will create free space on the data pages, avoiding page splits throughout the day, which should improve performance. The other FILLFACTOR settings would degrade performance.
13.	B, E, F. A fill factor setting of 90 percent will be optimal for this table. There is no need to pad the index. The index should be rebuilt daily. Reorganizing the indexes will not rebuild the B-tree with the fill factor setting.
14.	B. The CATCH/TRY block is designed to provide structured exception handling in batches and stored procedures. The RAISERROR statement traps the error of only the last T-SQL statement. The XACT_ABORT option does not trap errors.
15.	B. Hash operations indicate a lack of appropriate indexes.
16.	C. Setting DEADLOCK_PRIORITY to HIGH for the transaction will help ensure that the transaction is not chosen as the victim when a deadlock occurs. The DBCC OPENTRAN command will return information about the oldest open transaction. The FAST query hint only helps returning a certain number of rows quicker.
17.	C. Because of the bulk load of data and the AUTO_UPDATE_STATISTICS database option being turned off, the statistics are out-of-date. This is probably causing the performance problems because the optimizer is using out-of-date statistics to generate suboptimal query plans. Running the sp_updatestats system stored procedure will update the statistics for the entire database, ensuring that the query optimizer will be generating efficient query plans. The other options do not update the statistics.
18.	A. The SET SHOWPLAN_ALL option will return the most information about the query plan and related metrics without executing the query. The SET STATISTICS options will execute the query, which will impact the performance of the SQL Server instance and thus are inappropriate. The SHOWPLAN_TEXT option returns only basic information.
19.	A. The system process ID (SPID) of 69 represents the blocked process. The SPID of 66 represents the blocking process. The sp_who system stored procedure will return information about the SPIDs connected to the SQL Server 2005 instance. The USER_NAME() system function returns the name of the database user account.
20.	A. The Deadlock Graph event class provides an XML description of a deadlock.