Questions

Q1. Name three SQL Server components that are required to handle distributed transactions?

Q2. Name two SQL Server Login Auditing options?

Q3. The main elements of a SQL Server database are referred to as objects. Name five database objects ?

Q4. Name SQL Server 2012 spatial data types?

Q5. SQL Server 2012 supports several types of indexing, name five of them?

Q6. SQL Server Maintenance plans provide an automated way to perform essential maintenance in the server.  SQL Server provides lots of Maintenance Plan tasks to create a sophisticated Maintenance Plan. Name five Maintenance Plan Tasks ?

Q7. Name Two SQL Server Alert Types?

Q8. Name three execution types that can be executed by SQL Server Agent Jobs?

Q9. SQL Server is typically affected by five main bottlenecks; name three of them?

Q10. Name three factors that SQL Server used to calculate most expensive queries in the Query Statistics Report?

Answers

Q1. Correct Answer

Resource managers

Distributed Transaction Coordinator service

Transaction manager

Q2. Correct Answer

None

Failed Logins Only

Successful Logins Only

Both Failed and Successful Logins

Q3. Correct Answer

Certificates

Constraints

Defaults

Indexes

Keys

Stored procedures

Tables

Triggers

User-defined data types

User-defined functions

Views

Users

Roles

Rules

full-text catalogs

Synonyms

Q4. Correct Answer

geography

geometry 

Q5. Correct Answer

Clustered

Non-clustered

Covering

Filtered

Primary XML

Secondary XML {Path, Property, Value}

Spatial

Full-text

Columnstore

Q6. Correct Answer

Backup Database Task

Check Database Integrity Task

Execute SQL Server Agent Job Task

Execute T-SQL Statement Task

History Cleanup Task

Maintenance Cleanup Task

Notify Operator Task

Rebuild Index Task

Reorganize Index Task

Shrink Database Task

Update Statistics task

Q7. Correct Answer

SQL Server event alerts

SQL Server Performance condition alerts

Windows Management Instrumentation (WMI) event alerts

Q8. Correct Answer

Transact-SQL statement

Operating system command

SQL Server Integration Service package

SQL Server Analysis Services command or query

PowerShell command or script

ActiveX script

Replication Distributor, Merge, Queue Reader, Snapshot, or Transaction-Log Reader

Q9. Correct Answer

CPU

Memory

File I/O

Locking, blocking, or deadlocking

Networking

Q10. Correct Answer

Elapsed time

Worker time

Logical reads

Logical writes

Physical reads

Execution count