Interview Question: What are Wait Stats in SQL Server?
This should required only a short answer, such as “It provides statistics on what SQL Server is waiting for. For example, there is a Stat for Logging (LOGBUFFER, LOGMGR_QUEUE). One of the most important ones is the wait time for writing to media (WRITELOG).” Asking for the precise name is a bit unfair – such a question would be testing rote/memory abilities and not understand of what is going on. Some more important names are IO_COMPLETION, SOS_SCHEDULER_YIELD, PAGELATCH_XX, PAGEIOLATCH_XX, etc.
You could ask about general types, for example: Resource Waits (i.e. internal resources), External Waits and Queue Waits. However, a poor response may easily occur if the question is not very carefully phrased --
However, asking what you would do to resolve a high WRITELOG or LOGBUFFER statistics is a much better question. The typical answers could include:
- If you are using a virtual machine, make sure that data disks are passthru and not virtual disks
- Change the RAID configuration, add more storage Devices
- Check the bandwidth to the storage devices
- Isolate the Log files from the database files onto different storage devices
- Install storage devices that are faster
A follow-up question could be “How do you get the stats?” This could be legitimately answered with “I have some TSQL code that I cut and paste..” Some people may reference Sys.dm_os_wait_stats. For an example of such code, see Brent Ozar code example or Paul Randal code example. As an interesting aside (unfair to ask), is that this code sample works with SQL Azure.
A common aspect of interviews are questions that may test irrelevant knowledge. Asking a MD to explain why mercury is used in a thermometer instead of water is really not asking about he needs to know. Asking a family practice candidate MD to describe the difference in SPECT scans between an Alzheimer's patient and a Chronic Lyme patient is similarly irrelevant. The best questions that I have ever been asked are the ones that the interviewer are currently struggling with. I have been asked a lot of irrelevant to the job questions, way too many IMHO.