So you think you’re a SQL MCM Part IV– waits and Extended Events

As a recap for anyone who has made it this far, here are the other three blog entries in the series:

• http://blogs.prodata.ie/post/So-you-think-your-a-SQL-MCM-Test-Yourself-Part-I.aspx
• http://blogs.prodata.ie/post/So-you-think-your-a-SQL-MCM-Test-Yourself-Part-II.aspx
• http://blogs.prodata.ie/post/So-you-think-you’re-a-SQL-MCM-Part-III.aspx

This weeks questions are on waits and extended events. OK admission – I suck at extended events. Never really got on the gravy train with SQL 2008 and still clinging onto the hope that RML utilities will be upgraded to support SQL 2012. I’d even do the upgrade myself if the source code was on codeplex, such is my aversion to extended events. Waits on the other hand are the bread and butter of performance tuning – to be a SQL MCM I would expect you not only need to be able to articulate main wait types, but at least share some horror stories of situations where you’ve seen them. Mere mention of CXPACKET should be enough for a one hour debate.

Waits (not just wait stats)

1. Can you define “signal wait time” ?

2. In the DMV ‘sys.dm_os_wait_stats’ does the column “wait_time” include or exclude signal wait time?

3. What does a high “signal wait time” usually mean ?

4. What does a high wait time but low signal wait time mean ?

5. What is the difference between WRITELOG and LOGBUFFER wait types ?

6. When running a query what could cause an IO_COMPLETION wait type ?

7. When running a query what could cause an ASYNC_IO_COMPLETION wait type ?

8. What can cause PAGELATCH_XX ?

9. If you  have a RESOURCE_SEMAPHORE wait for a large query how could you resolve this?

10. How could you resolve THREADPOOL waits on a server with a high number of concurrent users?

11. How would you determine why a log file was continually growing ?

12. What can sys.dm_io_virtual_file_stats show you that you cannot see from perfmon counters ?

Extended Events (Assume SQL 2008R2)

1. What is a predicate WRT extended events ?
2. Is a predicate at the event or session scope ?
3. Explain the difference between sys.dm_xe_packages and sys.dm_xe_objects ?
4. Name some targets available in SQL 2008R2 (six available) ?
5. How big is an asynchronous buffer by default ?
6. What happens if an event is bigger than this default size?
7. Explain the difference between the three options for dealing with a full extended events buffer using EVENT_RETENTION_MODE
8. If the services is restarted will an xevents session continue ?
9. Why would you want to use extended events to track page splits instead of just using the performance counter ?
10. What is the purpose of this xevents node “//RingBufferTarget/event/action/value”