Questions

Q1. SQL Server provides number of tools that can be used to perform performance monitoring and tuning. Each of them is useful in certain scenarios and you will often need to combine several tools to achieve the desired outcome. Name five SQL Server perform performance monitoring and tuning tools?

Q2. What are the two types of dynamic management views?

Q3. What are the two types of dynamic management functions?

Q4. Name three Common Execution Plan Elements ?

Q5. What are the Methods for Obtaining Index Information ?

Q6. Name Three types of SQL Server functions?

Q7. What are the two main concurrency control types ?

Q8. Name five types of SQL Server locks?

Q9. Name five Locking-related Table Hints ?

Q10. Name three SQL Server Transaction Isolation Levels ?

Answers

Q1. Correct Answers

Database Engine Tuning Advisor

SQL Server Management Studio

Dynamic Management Objects

SQL Server Data Collection

SQL Server Profiler

SQL Trace

SQL Server Extended Events

Distributed Relay

Reliability and Performance Monitor

 Q2. Correct Answers

Server-scoped dynamic management views

Database-scoped dynamic management views

Q3. Correct Answers

Server-scoped dynamic management functions

Database-scoped dynamic management functions

Q4. Correct Answers

Table and Clustered Index Scans and Seeks

Nested Loops

Lookups

Merge and Hash Joins

Aggregations

Filter

Sort

Data Modification

 Q5. Correct Answers

SQL Server Management Studio

System Stored Procedures

System Functions

Catalog Views

Dynamic Management Views and Functions

Q6. Correct Answers

Scalar Functions

Table-valued Functions

System Functions

Q7. Correct Answers

Pessimistic

Optimistic

Q8. Correct Answers

Shared (S)

Update (U)

Exclusive (X)

Intent

Schema

Bulk Update (BU)

Key-range

Q9. Correct Answers

HOLDLOCK

NOLOCK

PAGLOCK

ROWLOCK

TABLOCK

TABLOCKX

UPDLOCK

XLOCK

Q10. Correct Answers

Read uncommitted

Read committed

Repeatable read

Serializable

Snapshot