Performance Tuning and Optimizing SQL Databases Quiz Questions and Answers

____________ is the preferred statement for indicating errors in the transaction.

Answer :
  • RAISERROR

_____________ displays an estimate of the current amount of disk space used by a table.

Answer :
  • sp_spaceused

In _______________ index instead of storing all the columns for a record together, each column is stored separately with all other rows in an index.

Answer :
  • Column store

Which of the following determines the type of resource to which the lock applies?

Answer :
  • sp_lock

The Activity Monitor in SQL Server Management Studio graphically displays information about______.

Answer :
  • All of the above

Index page deallocation during a ________ operation is always fully logged.

Answer :
  • Drop Index

__________ backups are taken in bulk or full recovery models only.

Answer :
  • Log

To produce execution plan output by using most Showplan Transact-SQL SET options, users must have _________.

Answer :
  • SHOWPLAN

Which of the following is used to validate the database objects?

Answer :
  • DBCC CHECKDB

Which of the following command defragments clustered and secondary indexes of the specified table?

Answer :
  • DBCC INDEXDEFRAG