Monitoring SQL  «Prev  Next»

Understanding sp_who - Quiz

Each question is worth one point. Select the best answer for each question.
1. Examine the following sp_who output:
spidstatusloginamehostnameblkdbnamecmd
51sleepingapp_userAPPSRV010SalesDBAWAITING COMMAND
52suspendedapp_userAPPSRV0151SalesDBUPDATE
53suspendedreport_userRPTSRV0252SalesDBSELECT
54runninganalystANALYSTPC0SalesDBSELECT
55sleepingapp_userAPPSRV010SalesDBAWAITING COMMAND
56backgroundsa 0masterLOG WRITER
57sleepingetl_svcETLBOX0SalesDBAWAITING COMMAND
58suspendedreport_userRPTSRV0253SalesDBSELECT
Spid 51 is, directly or indirectly, causing which other spids to be blocked?
Please select all the correct answers.
  A. 52
  B. 53
  C. 54
  D. 55
  E. 57
  F. 58

2. Using the same sp_who output:
spidstatusloginamehostnameblkdbnamecmd
51sleepingapp_userAPPSRV010SalesDBAWAITING COMMAND
52suspendedapp_userAPPSRV0151SalesDBUPDATE
53suspendedreport_userRPTSRV0252SalesDBSELECT
54runninganalystANALYSTPC0SalesDBSELECT
55sleepingapp_userAPPSRV010SalesDBAWAITING COMMAND
56backgroundsa 0masterLOG WRITER
57sleepingetl_svcETLBOX0SalesDBAWAITING COMMAND
58suspendedreport_userRPTSRV0253SalesDBSELECT
How many different logins are being used to connect to SQL Server?
Please select the best answer.
  A. 4
  B. 5
  C. 6
  D. 8

3. Using the same sp_who output:
spidstatusloginamehostnameblkdbnamecmd
51sleepingapp_userAPPSRV010SalesDBAWAITING COMMAND
52suspendedapp_userAPPSRV0151SalesDBUPDATE
53suspendedreport_userRPTSRV0252SalesDBSELECT
54runninganalystANALYSTPC0SalesDBSELECT
55sleepingapp_userAPPSRV010SalesDBAWAITING COMMAND
56backgroundsa 0masterLOG WRITER
57sleepingetl_svcETLBOX0SalesDBAWAITING COMMAND
58suspendedreport_userRPTSRV0253SalesDBSELECT
How many different hosts are the users connecting from?
Please select the best answer.
  A. 3
  B. 5
  C. 4
  D. 7

4. In sp_who output, a session shows status sleeping, but another session's blk column points to it. What does this indicate?
Please select the best answer.
  A. The sleeping session is idle but may still be holding locks from an earlier, uncommitted transaction
  B. This combination is impossible; sp_who is returning corrupted data
  C. A sleeping session can never actually hold a lock
  D. The sleeping session has already committed its transaction and will clear shortly

5. What happens when you run KILL against a session that is in the middle of a large, uncommitted transaction?
Please select the best answer.
  A. The session is removed instantly with no rollback
  B. The transaction is automatically committed before the session ends
  C. The client application is also terminated immediately
  D. SQL Server rolls back the transaction, which can take as long as the original work took to perform

6. An sp_who check returns 50 rows under the same login and application name, most of them sleeping, with only one or two sessions actually running or suspended. What does this most likely indicate?
Please select the best answer.
  A. A connection leak that is about to crash the server
  B. Normal connection pooling behavior from the client application
  C. Fifty distinct human users are simultaneously active right now
  D. sp_who is returning corrupted or duplicated rows