SQL Server Advanced Performance Tuning – Expert-Level Quiz

SQL Server Advanced Performance Tuning - Expert-Level Quiz

Are you ready to push your SQL Server skills to the limit? This expert-level quiz is designed to challenge even the most seasoned SQL Server professionals. Test your advanced performance tuning skills, from optimizing queries and indexes to managing server resources and troubleshooting performance bottlenecks. Let’s see if you have what it takes to be an SQL Server advanced performance tuning expert.

1 / 20

Which of the following marks the end of a successful implicit or explicit transaction?

SUCCESSFUL TRANSACTION

COMMIT WORK

ROLLBACK TRANSACTION

COMMIT TRANSACTION

2 / 20

One of the following locks is incompatible with all lock modes.

Schema stability

Bulk update

Intent exclusive (IX)

Schema modification

3 / 20

What is the purpose of the SET STATISTICS IO statement?

To display information about the network usage

To display information about the memory usage

To display information about the CPU usage

To display information about the amount of disk activity generated by T-SQL statements

4 / 20

Which SQL Server feature helps in identifying missing indexes?

SQL Server Agent

SQL Server Profiler

Database Engine Tuning Advisor

SQL Server Configuration Manager

5 / 20

What is the purpose of the DBCC CHECKTABLE command?

To check the integrity of a table or indexed view

To check the integrity of the buffer

None of the above

To check the integrity of the database

6 / 20

The document containing the XML schema for the XML output by SET STATISTICS XML is located where?

Microsoft SQL Server100ToolsBinnschemassqlserver20047showplanshowplanxml.xml

Microsoft SQL Server100ToolsBinnschemassqlserver20047showplanshowplanxml.xsl

None of the above

Microsoft SQL Server100ToolsBinnschemassqlserver20047showplanshowplanxml.xsd

7 / 20

Which design strategy can reduce the occurrence of blocking locks?

Make sure that CREATE and DROP statements use an existing index

Use non-clustered indexes on high-usage tables

Use clustered indexes on high-usage tables

Use clustered indexes on less-usage tables

8 / 20

What is the purpose of the DBCC FREEPROCCACHE command?

To clear the transaction log

To clear the buffer cache

To clear the procedure cache

To clear the database backup

9 / 20

Which of the following arguments is ignored by the database engine in COMMIT TRANSACTION?

@tran_name

transaction_name

@tran_name_variable

None of the above

10 / 20

Which of the following is how a statement can be physically executed?

execution plan

query plan

query execution plan

All of the above

11 / 20

What locks allow you to bulk copy data concurrently into the same table?

Bulk copy

Bulk update

Bulk import

Bulk export

12 / 20

What is the basic difference between COMMIT and COMMIT WORK?

COMMIT WORK accepts a user-defined transaction name

COMMIT TRANSACTION accepts a user-defined function

COMMIT TRANSACTION accepts a user-defined transaction name

COMMIT WORK accepts a user-defined stored procedure

13 / 20

What is the purpose of the DBCC INDEXDEFRAG command?

To defragment the procedure cache

To defragment indexes on a table or view

To defragment the database

To defragment the buffer pool

14 / 20

Which of the following is a wrong statement?

Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required

Schema modification (Sch-M) locks are used when a table data definition language operation is being performed

By default, SQL Server transactions times out after a certain time

Disallowing a locking level can affect concurrency adversely

15 / 20

What is the purpose of the DBCC DROPCLEANBUFFERS command?

To remove all buffers from the procedure cache

To remove all clean buffers from the buffer pool

To remove all dirty buffers from the buffer pool

To remove all buffers from the buffer pool

16 / 20

SET SHOWPLAN_TEXT is intended to return readable output for what utility?

plsql

tsql

pgsql

osql

17 / 20

One of the following statements is correct: which is it?

JDBC use parameter markers

MisGuided Plan Executions/sec represents the number of plan executions per second in which the query plan has been generated by using a plan guide

When forced parameterization is tried but fails, simple parameterization is still subsequently tried

Parameter markers are question marks (?) that replace a constant in an SQL statement

18 / 20

Which resource can be locked in SQL Server?

SID

PID

TID

RID

19 / 20

Which of the following locking hints is similar to NOLOCK?

READUNCOMMITTED

READCOMMITTED

SERIALIZABLE

PAGLOCK

20 / 20

Which of the following statements is correct?

The setting of SET SHOWPLAN_ALL is set at execute or run time and not at parse time

All of the above

SET SHOWPLAN_TEXT causes Microsoft SQL Server to execute Transact-SQL statements

The setting of SET SHOWPLAN_XML is set at execute or run time and not at parse time

Your score is

The average score is 15%

Do you think you’ve mastered SQL Server’s nuances? Now is the time to distinguish yourself as a true SQL Server advanced performance tuning virtuoso with this expert-level quiz.

In SQL Server, performance tuning is akin to wielding a finely tuned instrument in data management. It involves the art and science of optimizing the performance of your SQL Server databases. This is done by fine-tuning various elements such as queries, indexes, configuration settings, and more.

Your mastery will enhance the efficiency and speed of database operations and elevate your expertise as an SQL Server professional.

Good luck!