Home / CSE MCQs / SQL Server MCQs :: SQL Server Performance Tuning,Optimization

CSE MCQs :: SQL Server MCQs

  1. Point out the correct statement :
  2. A.
    Avoid less row count SQL statements that can cause a table lock
    B.
    A blocking lock occurs when one lock causes another process to wait in a holding queue
    C.
    Lock is a done by database when any connection access a different piece of data concurrently
    D.
    None of the mentioned

  3. To remove archival compression and restore the data to columnstore compression :
  4. A.
    Use ALTER TABLE
    B.
    Use ALTER COLUMN
    C.
    Use ALTER DATABASE
    D.
    All of the mentioned

  5. Point out the correct statement :
  6. A.
    When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the source filegroup
    B.
    The Database Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row
    C.
    When SORT_IN_TEMPDB is set to ON, the default, the sort runs are stored in the destination filegroup.
    D.
    None of the mentioned

  7. ______________ allow concurrent transactions to read (SELECT) a resource.
  8. A.
    Update locks
    B.
    Shared locks
    C.
    Exclusive Locks
    D.
    All of the mentioned

  9. Point out the correct statement :
  10. A.
    On x86-based systems, creating a table or index with more than 1000 partitions is possible
    B.
    Compressing large tables or indexes can have manageability and performance benefit
    C.
    Partitioning slow tables or indexes can have the following manageability and performance benefit
    D.
    None of the mentioned

  11. To produce execution plan output by using most Showplan Transact-SQL SET options, users must have
  12. A.
    SHOW SHOWPLAN
    B.
    SHOW PLAN
    C.
    SHOWPLAN
    D.
    None of the mentioned

  13. Point out the correct statement :
  14. A.
    JDBC use parameter markers
    B.
    Parameter markers are question marks (?) that replace a constant in an SQL statement
    C.
    When forced parametrization is tried but fails, simple parametrization is still subsequently tried
    D.
    All of the mentioned

  15. Enabling the Resource Governor requires _________ permission.
  16. A.
    CONTROL SERVER
    B.
    CONTROL
    C.
    SERVER
    D.
    SELECT

  17. ____________ rolls back a user-specified transaction to the beginning of the transaction.
  18. A.
    ROLLBACK
    B.
    ROLLBACK WORK
    C.
    SAVE TRANSACTION
    D.
    COMMIT

  19. ____________ is the preferred statement for indicating errors in the transaction.
  20. A.
    SELECT
    B.
    PRINT
    C.
    RAISERROR
    D.
    All of the mentioned