Review Questions

1. 

You are the database administrator of a SQL Server 2005 instance. The server has recently been upgraded from 2GB to 4GB of RAM and from two processors to four processors because users were complaining about performance. Performance has not improved as much as management would have liked, and management has asked you to troubleshoot. The SQL Server 2005 instance has the following configuration:

Open table as spreadsheet

Configuration Option	Configured Value
affinity I/O mask	0
affinity mask	0
awe enabled	0
cost threshold for parallelism	5
lightweight pooling	0
max degree of parallelism	0
max server memory (MB)	2147483647
max worker threads	2
priority boost	1
set working size	1
user connections	0

You need to ensure that performance on this new hardware is optimal. What should you do?

1. Change the affinity mask configuration option to 4.

2. Change the max worker threads configuration option to 4.

3. Change the max worker threads configuration option to 0.

4. Change the max degree of parallelism configuration option to 4.

2. 

You are the database administrator for a SQL Server 2005 instance. Information workers have noticed that the spr_GetContact stored procedure is performing poorly. The T-SQL code for this stored procedure is as follows:

 CREATE PROC spr_GetContact @Contact INT WITH RECOMPILE AS SELECT * FROM Contacts (WITH INDEX(0)) WHERE ContactId = @Contact GO ;

You also run DBCC SHOWCONTIG ('Contacts') and get the following output.

 DBCC SHOWCONTIG scanning 'Contacts' table... Table: 'Contacts' (98734544); index ID: 1, database ID: 69 TABLE level scan performed. - Pages Scanned................................: 2264448 - Extents Scanned..............................: 285485 - Extent Switches..............................: 487093 - Avg. Pages per Extent........................: 7.8 - Scan Density [Best Count:Actual Count].......: 8.69% [283057:487094] - Logical Scan Fragmentation ..................: 0.06% - Extent Scan Fragmentation ...................: 3.69% - Avg. Bytes Free per Page.....................: 3.8 - Avg. Page Density (full).....................: 99.96%

Performance of this query is critical to the business. You need to improve the performance of this query immediately. What should you do?

1. Remove the WITH RECOMPILE option from the stored procedure.

2. Remove the WITH (INDEX(0)) option from the stored procedure.

3. Rebuild the clustered index on the Contacts table.

4. Reorganize the clustered index on the Contacts table.

3. 

You are the junior database administrator for a small company that is running Microsoft Small Business Server, which includes SQL Server 2005 on a single computer. Your boss has come to you just before your lunch break, complaining about the performance of the Sales database on that server. What should you do to determine the cause of the performance problem?

1. Run DBCC LOGINFO in the Sales database to determine what is causing this behavior.

2. Run the Database Engine Tuning Advisor on the Sales database, and implement its recommendations.

3. Use System Monitor to determine whether it is the operating system or SQL Server that is consuming server resources.

4. Use the Current Activity Monitor in SQL Server Management Studio to determine what is causing this behavior.

4. 

You are the database administrator for a SQL Server 2005 instance. Users of the database are experiencing query performance problems, and you want to see what query plans are currently cached and statistical information about them. What DMV should you query?

1. sys.dm_exec_query_optimizer_info

2. sys.dm_exec_query_plan

3. sys.dm_exec_query_stats

4. sys.dm_exec_sql_text

5. 

You are a senior database administrator responsible for tuning a SQL Server 2005 instance that is critical to the business. Over the last week its performance has degraded substantially, and no significant event has occurred in that time frame. The database developers have assured you that the queries and indexes are optimal. You have used System Monitor to monitor the computer and gather the following metrics:

Open table as spreadsheet

Counter	Value
Processor : %Processor Time	75
System : Processor Queue Length	1.2
Memory : Available Bytes	200
Memory : Pages/Sec	200
SQL Server:Buffer Manager : Page Life Expectancy	350
SQL Server:Buffer Manager : Buffer Cache Hit Ratio	95
PhysicalDisk Object : % Disk	44
PhysicalDisk Object : Avg. Disk Queue Length	0.9

What should you do to improve performance?

1. Upgrade your processor subsystem.

2. Upgrade your memory subsystem.

3. Upgrade your I/O subsystem.

4. Increase the size of your tempdb system database, and move it to another partition.

6. 

You are a database administrator for a small organization. One of the developers has asked you to help troubleshoot query response times within a database. You are not familiar with the database. Which tool can help you help the developer?

1. System Monitor

2. SQL Trace

3. Performance Logs and Alerts

4. Database Engine Tuning Advisor

7. 

You are the database administrator of a SQL Server 2005 instance. The server has recently been upgraded from 2GB to 4GB of RAM and from two processors to four processors. Performance has not improved as much as management would have liked, and management has asked you to troubleshoot. The SQL Server 2005 instance has the following configuration:

Open table as spreadsheet

Configuration Option	Configured Value
affinity I/O mask	0
affinity mask	2
awe enabled	0
cost threshold for parallelism	5
lightweight pooling	0
max degree of parallelism	0
max server memory (MB)	2147483647
max worker threads	0
priority boost	1
set working size	1
user connections	0

You need to ensure that performance on this new hardware is optimal. What should you do?

1. Change the affinity mask configuration option to 0.

2. Change the affinity mask configuration option to 1.

3. Change the affinity mask configuration option to 3.

4. Change the affinity mask configuration option to 4.

8. 

You are the DBA for a SQL Server 2005 instance. You want to free all memory taken up by stored procedures and views but keep the data currently cached in memory. What command should you execute?

1. DBCC DROPCLEANBUFFERS

2. DBCC FREEPROCCACHE

3. DBCC FREESYSTEMCACHE

4. DBCC PROCCACHE

9. 

You are a senior database administrator in your organization. A junior database administrator has identified that a specific SQL Server 2005 instance is experiencing a lot of recompilations. She has asked you which of the following are causes for recompilation. (Choose all that apply.)

1. The schema of an underlying object has changed.

2. The SET ANSI_NULLS option has changed.

3. The sp_updatestats stored procedure was run within the database.

4. The SET ROWCOUNT option has changed.

10. 

You are the DBA for your company. On one uniprocessor SQL Server instance there are several large departmental databases (on the C: drive) that all use the SORT_IN_TEMPDB option when rebuilding all indexes. The tempdb currently has the following configuration:

Open table as spreadsheet

File	Size	Drive Location
tempdb.mdf	25GB	C:
templog.ldf	5GB	D:

You have noticed that performance degrades when the indexes are being rebuilt. You have three 10GB disk drives that can be used in a RAID array. What should you do to improve performance?

1. Spread the tempdb database data files across the three disk drives in a RAID-5 array.

2. Spread the tempdb database log files across the three disk drives in a RAID-0 array.

3. Spread the tempdb database data files across the three disk drives in a RAID-0 array.

4. Spread the tempdb database log files across the three disk drives in a RAID-5 array.

11. 

You are a database administrator in charge of a SQL Server 2005 instance that is experiencing high processor utilization. You have narrowed down the performance problem to the database engine and want to identify the number of workers waiting to be scheduled. What DMV should you query?

1. sys.dm_db_index_usage

2. sys.dm_exec_query_stats

3. sys.dm_exec_sql_text

4. sys.dm_os_schedulers

12. 

You are the database administrator of a SQL Server 2005 instance. The server has recently been upgraded from 2GB to 4GB of RAM and from two processors to four processors. Performance has not improved as much as management would have liked, and management has asked you to troubleshoot. The SQL Server 2005 instance has the following configuration:

Open table as spreadsheet

Configuration Option	Configured Value
affinity I/O mask	0
affinity mask	0
awe enabled	0
cost threshold for parallelism	5
lightweight pooling	0
max degree of parallelism	3
max server memory (MB)	1024
max worker threads	0
priority boost	0
set working size	0
user connections	0

You need to ensure that performance on this new hardware is optimal. What should you do?

1. Change the set working size configuration option to 1.

2. Change the awe enabled configuration option to 1.

3. Change the max degree of parallelism configuration option to 4.

4. Change the max server memory (MB) configuration option to 2048.

13. 

You are a database administrator at your organization. While you were away in Zambia on a short-term contract over the Australia Day holiday period, a contractor was employed in your stead. Upon your return to the Sydney office, a SQL Server 2005 instance has “crashed” for no apparent reason. You suspect that the contractor might have made some configuration changes that have contributed to the crash. What should you examine?

1. The SQLAGENT log file

2. The ERRORLOG log file

3. The default trace

4. The Windows Event Log

14. 

You are a database administrator responsible for maintaining the performance of a SQL Server 2005 instance that is experiencing a lot of deadlocks. What trace flag should you turn on to enable more verbose logging?

1. 1204

2. 1211

3. 1224

4. 2528

15. 

You are a database administrator at a small company. You want to be notified when one of your servers is running near capacity. What should you do?

1. Use SQL Server Profiler to monitor the server.

2. Use SQL Trace to monitor the server’s resources.

3. Use Performance Logs and Alerts.

4. Monitor the SQL Server log.

16. 

You are the database administrator of a SQL Server 2005 instance. Your users have noticed degradation in performance and have run System Monitor to gather the following metrics:

Open table as spreadsheet

Counter	Value
Processor : %Processor Time	70
System : Processor Queue Length	2.5
Memory : Available Mbytes	20
SQL Server : Buffer Manager–Page Life Expectancy	1000
PhysicalDisk Object : % Disk	30
PhysicalDisk Object : Avg. Disk Queue Length	0.8

What subsystem is the bottleneck?

1. Processor

2. Memory

3. Disk I/O

4. The tempdb system database

17. 

You are the database administrator for your company. Managers run a critical query throughout the day to get a real-time report into the state of the business. This query accesses a single table that changes frequently. Management has notified you that the report is taking longer to run. You suspect that the table is heavily fragmented. What DMV should you query to determine this?

1. sys.dm_db_index_operational_stats

2. sys.dm_db_index_physical_stats

3. sys.dm_db_index_usage_stats

4. sys.dm_db_partition_stats

18. 

You are a junior database administrator responsible for a quad-processor SQL Server 2005 instance that has been installed using the default installation. Several large departmental databases are on this instance, and all use the SORT_IN_TEMPDB option when rebuilding all indexes. You have noticed that performance degrades when the indexes are being rebuilt. What should you do to improve performance?

1. Add more memory to the SQL Server instance, and allocate it to the instance.

2. Add more processors to the SQL Server instance.

3. Add more data files to the tempdb system database.

4. Add more log files to the tempdb system database.

19. 

You are a database administrator for the Kats bank in Sydney, Australia. After spending the whole Thursday night out with your friends, you come in on Friday to find that there was an unexpected power problem overnight. Consequently, one of the SQL Server 2005 instances rebooted unexpectedly. The SQL Server 2005 instance is up and running. You want to ensure that no databases were corrupted as a result of the reboot. What do you look at to determine this?

1. Examine the SQLAGENT log file.

2. Examine the ERRORLOG log file.

3. Examine the default trace.

4. Examine the Windows Event Log.

20. 

You are the database administrator for your company. Users are complaining about query performance on your production SQL Server 2005 instance. You plan to capture a trace for the duration of the day and play it against your development SQL Server 2005 instance. What template should you use in SQL Server Profile to capture the trace?

1. TSQL

2. Tuning

3. TSQL_Grouped

4. TSQL_Replay

Answers

1.	C. Changing the max worker threads configuration option to 0 will allow the SQL Server 2005 instance to dynamically generate as many worker threads as it deems necessary. This will result in optimal performance. A value of 4 would be too little. The other options would have no substantial performance gain.
2.	B. Removing the WITH (INDEX(0)) optimizer hint to perform a tablescan operation each time will dramatically improve performance straightaway. Although the table is heavily fragmented, it will not affect this query because it is a point query and not a range query. Likewise, rebuilding or reindexing the clustered index will not improve performance immediately because a tablescan will still be performed. The same applies for any potential performance issues with the WITH RECOMPILE option.
3.	C. The performance problem is not necessarily SQL Server related because a number of processes are running on the computer. The first step is to isolate the subsystem responsible through System Monitor.
4.	C. The sys.dm_exec_query_stats DMV returns aggregate performance statistics for cached query plans.
5.	B. The Memory : Pages/Sec performance object counter value of 200 indicates a memory-related problem. This is further backed up by the Memory : Available Bytes performance object counter value of 200. All other performance object counters are within normal thresholds.
6.	D. The Database Engine Tuning Advisor is designed to analyze the database and make various recommendations to improve the performance of the database. The other tools will not make any recommendations by themselves.
7.	A. Changing the affinity mask configuration option to 0 will enable the SQL Server instance to utilize all the processors.
8.	B. The DBCC FREEPROCCACHE command frees up only the procedure cache. DBCC DROPCLEANBUFFERS frees the data cache. FREESYSTEMCACHE frees both the data and the procedure cache. The DBCC PROCCACHE command just displays information about the procedure cache.
9.	A, B, C. Options A, B, and C will all trigger a recompilation.
10.	C. Spreading the tempdb database data files on a RAID-0 array will improve performance. (Yes, I know there is no redundancy, but we did not ask for that!) RAID-5 is inappropriate because it will degrade write performance. The tempdb log file is only 5GB and is already separate from the operating system
11.	D. The sys.dm_os_schedulers DMV has the runnable_tasks_count column that represents the number of workers waiting to be scheduled on the runnable queue. The other DMVs return information about the database and query environment, not the SQL operating system.
12.	D. Changing the max server memory (MB) configuration option to 2048 will enable the SQL Server 2005 instance to utilize the extra memory. The awe enabled configuration option is configured only on 32-bit computers with more than 4GB of RAM. The set working size configuration option has no effect in SQL Server 2005. Changing the max degree of parallelism configuration option to 4 will not substantially affect performance.
13.	C. The default trace captures a log of important activity and the changes primarily related to the configuration options.
14.	A. Trace flag 1204 returns more information about the deadlock event, including the type of locks participating in the deadlock and the current command affected.
15.	C. Using the Performance Logs and Alerts tool, you can set up alerts when hardware components are used to a certain level. SQL Server Profiler and SQL Trace do not have the ability to notify you if this event occurs. The SQL Server log does not have this information, and it can’t generate alerts.
16.	A. The System : Processor Queue Length performance object counter indicates that there is a processor bottleneck. The other performance object counters are underneath the threshold that would typically indicate one of them is the bottleneck.
17.	B. The sys.dm_db_index_physical_stats DMV returns size and fragmentation information for the data and indexes of the specified table or view.
18.	C. By adding more data files to the tempdb system database, SQL Server 2005 can use multiple worker threads to leverage the multiple processors when accessing the multiple data files. Adding more log files to the tempdb system database will not improve performance because it will still be used sequentially. Nothing suggests that adding more processors or memory will improve performance.
19.	B. The ERRORLOG file shows the output of the potential autorecovery of databases whenever the SQL Server 2005 instance starts.
20.	D. The TSQL_Replay template collects detailed information about the T-SQL statements that have been issued so that they can be replayed.