Optimizing SQL Server Performance

Question 1

You are the database administrator at Coho Vineyards. Following the consolidation of all the wineries’ inventory, customer, and order databases, the marketing group wants to be able to run ad hoc queries for analysis purposes. Users are allowed to execute any query that they can construct, regardless of the impact it might have on the performance of the database. Unfortunately, the same databases are being used to create and process customer orders. Management does not want to restrict the queries that marketing can execute, but it wants you to ensure that customer orders can be created and processed in a timely fashion. What can be used to limit the impact of marketing queries to ensure customer orders are processed?

• Configure the max degree of parallelism option.
• Limit the memory utilization for marketing users.
• Implement Resource Governor.
• Configure the query governor cost threshold.

Answer 1

Implement Resource Governor

EXPLANATION:

You can create a workload group for all the marketing users and assign the workload group to a resource pool that limits the CPU and memory available.

Question 2

What types of workloads can DTA use for analysis? (Choose all that apply.)

1. A trace file containing SP:StmtCompleted events
2. A trace file containing RPC:Completed events
3. A trace file containing Extensible Markup Language (XML) showplans
4. A T-SQL script

Answer 2

1. A trace file containing SP:StmtCompleted events
2. A trace file containing RPC:Completed events
3. A T-SQL script

Question 3

You are trying to troubleshoot a performance issue at Fabrikam. At about 15 minutes past the hour, on a recurring basis, query performance declines for about 1 minute before application performance returns to normal. What tools can you use to diagnose the cause of the performance problems? (Choose all that apply.)

1. Profiler
2. Resource Governor
3. System Monitor
4. Database Engine Tuning Advisor

Answer 3

1. Profiler
2. System Monitor

EXPLANATION

System Monitor allows you to capture performance counters for the state of the hardware, operating system, and various SQL Server subsystems, which can be correlated to a Profiler trace to diagnose a performance issue.

Profiler allows you to capture the query activity on the instance and then correlate the queries with performance counters captured using System Monitor.

Question 4

What DMV would you use to find indexes that are no longer being used?

• sys.dm_db_index_operational_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_missing_index_details

Answer 4

sys.dm_db_index_usage_stats

EXPLANATION

Sys.dm_db_index_usage_stats tells you the last time an index was used to satisfy a request, as well as how many times the index has been used.

Question 5

What does the System:Processor Queue Length counter measure?

• The number of system requests waiting for a processor
• The number of SQL Server requests waiting for a processor
• The number of processors actively performing work
• The amount of time that a processor is in use

Answer 5

The number of system requests waiting for a processor

EXPLANATION

The System:Processor Queue Length indicates the number of processes at a machine level that are waiting for a processor to be allocated.

Question 6

As part of a recent acquisition, Humongous Insurance now has SQL Server instances from version 6.5 through 9.0. A variety of third-party products and custom code has been used in the past to manage capacity across the SQL Server environment. Your manager wants to consolidate everything into a single platform that can be used to perform capacity management tasks and evaluate performance against baselines. You need to implement a solution that has minimal cost and requires the least amount of effort to configure and maintain. What solution should you propose?

• Install a SQL Server 2008 instance and implement policy-based management.
• Install a SQL Server 2008 instance and rewrite everything using SSIS.
• Install a SQL Server 2008 instance and implement a Performance Data Warehouse.
• Implement Microsoft System Center Operations Manager 2007.

Answer 6

• Install a SQL Server 2008 instance and implement a Performance Data Warehouse.

EXPLANATION

The Performance Data Warehouse in SQL Server 2008 allows you to configure data collection quickly against SQL Server 6.5 through 9.0 to consolidate all the capacity management, as well as performance baseline analysis.