SQL Server Questions and Answers – Wait States

«
»

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Wait States”.

1. Which of the following requires VIEW SERVER STATE permission on the server?
a) sys.dm_os_states
b) sys.dm_os_wait_stats
c) sys.dm_os_wait_states
d) none of the mentioned
View Answer

Answer: b
Explanation: sys.dm_os_wait_stats returns information about all the waits encountered by threads that executed.

2. Point out the correct statement.
a) 510: CXPACKET indicates parallelism
b) SQL Server does not always waits for something, a disk io, a place in the processor queue, a lock
c) Few variations of PAGEIOLATCH waits usually indicate a problem with the disk subsystem
d) All of the mentioned
View Answer

Answer: a
Explanation: The coordinator thread in a parallel query always accumulates these waits.

advertisement

3. How many types of wait occur in SQL Server?
a) 2
b) 3
c) 4
d) 5
View Answer

Answer: b
Explanation: SQL Server mainly supports three types of waits: Resource waits, Queue waits and External waits

4. The contents of sys.dm_os_wait_stats can be reset by running which of the following command?
a) DBCC PERF (‘sys.dm_os_wait_stats’, CLEAR);
b) DBCC SQLP (‘sys.dm_os_wait_stats’, CLEAR);
c) DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);
d) None of the mentioned
View Answer

Answer: c
Explanation: DBCC SQLPERFcommand resets all counters to 0.

advertisement
advertisement

5. Point out the wrong statement.
a) LCK_M_XX wait is often a symptom of blocking
b) PAGELATCH_XX wait occurs when SQL Server is attempting to latch a page in memory
c) Few variations of PAGEIOLATCH waits usually indicate a problem with the disk subsystem
d) All of the mentioned
View Answer

Answer: c
Explanation: All variations of PAGEIOLATCH waits usually indicate a problem with the disk subsystem.

6. ______ waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available.
a) Resource
b) Queue
c) Lock
d) External
View Answer

Answer: a
Explanation: Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects.

advertisement

7. Which of the following wait occurs when a task is waiting for I/Os to finish?
a) ASYNC_NETWORK_IO
b) AUDIT_LOGINCACHE_LOCK
c) AUDIT_ON_DEMAND_TARGET_LOCK
d) AUDIT_XE_SESSION_MGR
View Answer

Answer: a
Explanation: AUDIT_XE_SESSION_MGR occurs when there is a wait on a lock that is used to synchronize the starting and stopping of audit related Extended Events sessions.

8. When does BROKER_TO_FLUSH wait type event occur?
a) When the Service Broker queue task handler tries to shut down the task
b) When the Service Broker transmitter is waiting for work
c) When the Service Broker lazy flusher flushes the in-memory transmission objects to a work table
d) All of the mentioned
View Answer

Answer: c
Explanation: BROKER_SHUTDOWN tries to shut down the task.

advertisement

9. Which of the following wait type is for internal use only?
a) ASYNC_NETWORK_IO
b) AUDIT_LOGINCACHE_LOCK
c) HADR_DBR_SUBSCRIBER_FILTER_LIST
d) AUDIT_XE_SESSION_MGR
View Answer

Answer: c
Explanation: The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers that correspond to availability databases.

10. _____________ wait type applies to SQL Server 2014 only.
a) LCK_M_U_ABORT_BLOCKERS
b) HADR_WORK_QUEUE
c) HADR_XRF_STACK_ACCESS
d) HADR_WORK_POOL
View Answer

Answer: a
Explanation: LCK_M_U_ABORT_BLOCKERS occurs when a task is waiting to acquire an Update lock with Abort Blockers.

advertisement

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.

Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!
advertisement
advertisement
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He is Linux Kernel Developer & SAN Architect and is passionate about competency developments in these areas. He lives in Bangalore and delivers focused training sessions to IT professionals in Linux Kernel, Linux Debugging, Linux Device Drivers, Linux Networking, Linux Storage, Advanced C Programming, SAN Storage Technologies, SCSI Internals & Storage Protocols such as iSCSI & Fiber Channel. Stay connected with him @ LinkedIn | Youtube | Instagram | Facebook | Twitter