MCQOPTIONS
Saved Bookmarks
MCQOPTIONS
This section includes 67 Mcqs, each offering curated multiple-choice questions to sharpen your SQL Server knowledge and support exam preparation. Choose a topic below to get started.
| 51. |
Which of the following formula is used for calculating the effective MAX %? |
| A. | 100 – sum(MIN %) |
| B. | min(X,Y) |
| C. | max(X,Y) |
| D. | Effective MAX % – MIN % |
| Answer» C. max(X,Y) | |
| 52. |
Which is the most restrictive isolation level in transaction? |
| A. | Repeatable Read |
| B. | Snapshot |
| C. | Read Committed |
| D. | SERIALIZABLE |
| Answer» E. | |
| 53. |
Which of the following occurs when two connections need access to same piece of data concurrently and the meanwhile another is blocked because at a particular time, only one connection can have access? |
| A. | Lock |
| B. | Block |
| C. | Deadlock |
| D. | Exclusive lock |
| Answer» C. Deadlock | |
| 54. |
_________ uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache. |
| A. | pool_name |
| B. | plan_handle |
| C. | sql_handle |
| D. | none of the mentioned |
| Answer» C. sql_handle | |
| 55. |
Which of the following query returns object names from partition IDs? |
| A. | SELECT OBJECT_NAME(p.object_id) AS TableName , |
| B. | SELECT OBJECT_NAME(p.object_id) AS TableName , |
| C. | SELECT OBJECT_NAME(p.object_id) AS TableName , |
| D. | None of the mentioned |
| Answer» B. SELECT OBJECT_NAME(p.object_id) AS TableName , | |
| 56. |
Which of the following query deletes a job candidate? |
| A. | BEGIN TRANSACTION; |
| B. | BEGIN TRANSACTION; |
| C. | BEGIN TRANSACTION; |
| D. | All of the mentioned |
| Answer» B. BEGIN TRANSACTION; | |
| 57. |
Internal checkpoint is generated in response to which of the following event? |
| A. | A database snapshot is deleted, whether explicitly or internally for DBCC CHECK |
| B. | Bringing a SQL Server failover cluster instance (FCI) offline |
| C. | Bringing a SQL Server failover cluster instance (FCI) online |
| D. | None of the mentioned |
| Answer» C. Bringing a SQL Server failover cluster instance (FCI) online | |
| 58. |
Which of the following query enables the Resource Governor? |
| A. | ALTER RESOURCE GOVERNOR CONFIGURE; |
| B. | ALTER RESOURCE RECONFIGURE; |
| C. | ALTER RESOURCE GOVERNOR RECONFIGURE; |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 59. |
Which of the following design strategy can reduce the occurrence of blocking locks? |
| A. | Use non-clustered indexes on high-usage tables |
| B. | Use clustered indexes on high-usage tables |
| C. | Use clustered indexes on less-usage tables |
| D. | Make sure that CREATE and DROP statements use an existing index |
| Answer» C. Use clustered indexes on less-usage tables | |
| 60. |
____________ is a hard cap limit on the CPU bandwidth for all requests in the resource pool. |
| A. | MIN_CPU_PERCENT |
| B. | CAP_CPU_PERCENT |
| C. | MAX_CPU_PERCENT |
| D. | MAX_MEMORY_PERCENT |
| Answer» C. MAX_CPU_PERCENT | |
| 61. |
Which of the following mode has exclusive range and exclusive resource lock? |
| A. | RangeX_X |
| B. | RangeS_U |
| C. | RangeS_S |
| D. | RangeI_N |
| Answer» B. RangeS_U | |
| 62. |
The document containing the XML schema for the XML output by SET STATISTICS XML is located at ______________ |
| A. | \Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xml |
| B. | \Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xsl |
| C. | \Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xsd |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 63. |
Which of the following syntax is valid? |
| A. | SET SHOWPLAN_XML { ON | OFF } |
| B. | SET SHOWPLAN { ON | OFF } |
| C. | SET SHOW_XML { ON | OFF } |
| D. | None of the mentioned |
| Answer» B. SET SHOWPLAN { ON | OFF } | |
| 64. |
To produce execution plan output by using most Showplan Transact-SQL SET options, users must have _________ |
| A. | SHOW SHOWPLAN |
| B. | SHOW PLAN |
| C. | SHOWPLAN |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 65. |
The way that a statement can be physically executed is called _____________ |
| A. | query plan |
| B. | execution plan |
| C. | query execution plan |
| D. | all of the mentioned |
| Answer» E. | |
| 66. |
__________ returns detailed information about how the statements are executed. |
| A. | SET STATISTICS XML |
| B. | SET SHOWPLAN_TEXT |
| C. | SET STATISTICS PROFILE |
| D. | All of the mentioned |
| Answer» C. SET STATISTICS PROFILE | |
| 67. |
Which of the following is valid syntax for SET STATISTICS XML? |
| A. | SET STATISTICS XML { OFF } |
| B. | SET STATISTICS XML { ON | OFF } |
| C. | SET STATISTICS XML { ON } |
| D. | All of the mentioned |
| Answer» E. | |