This site is dedicated to MS SQL Server ™ Internals information. Tools and information about internals, bases on private research are presented here. Part of the tools provided will actively change your running sqlservr process, so be sure to download the right version, almost all tools are per version, and be very careful in production environments. The author is not responsible for any damage to your SQL Server environment, data or career. Other tools will only use direct memory access. None of the tools need any 'traditional' database connection.

Background.

In the Oracle world the concept of wait event based performance analysis was first introduced mid 90's by Anjo Kolk    , a now well know Oracle Guru and the Father of YAPP, Yet Another Performance Profiling method. Please refer to his website www.oraperf.com to find more information and download the (Oracle) YAPP whitepaper, it's an Oracle specific paper, but it's worth a read to understand the background and importance of wait events. The latest and greatest addition is the ‘Optimizing Oracle Performance’ book by Cary Millsap and Jeff Holt (O’Reilly press): it explains everything about the Oracle wait interface, and why you should trace per user session.

Here (155K) you can download my presentation I gave on the DatabaseForum 2003 in Lalandia, Denmark (www.miracleas.dk) about 'waiting' in SQL Server 2000. This is a first attempt (to my knowledge) to widely introduce the concept about wait event based performance analysis in SQL Server. The presentation tries to provide some background on what information is available in SQL Server about 'waits events', and why they are possibly important. I also wrote this article (pdf) early 2004 for the Danish Oracle Ekspert magazine on the same subject. (Available with permission from OracleEkspert magazine)

Currently SQLServer2000 only maintains wait statistics at the server level. The dbcc command, sqlperf(waitstats) can be used to query this wait information. SQLServer2005 Beta2 shows a little more wait events, but still only on the server level (or UMS scheduler level to be more precise). It does show detailed latch, and table and index wait statistics however, a big improvement compared to SQLServer2000. Although still very limited compared to Oracle's 300+, documented, wait events, some are very relevant for the SQLServer performance analyst.

Until recently the best description I found on the internet sofar, is an article written by Cathan Kirkwood which can be found here on the sqlservercental.com website. Just recently (as of May 2004) I found that a guy from Microsoft, Tom Davidson, updated an already available paper (www.sqldev.net/misc/waittypes.htm). The end result is an excellent article and, hurray!, very good explanation of the wait statistics! You can find the article and paper on winnetmag.com..Also recently added is the MS article KB822101.

In 2007 I published 2 articles on the wait subject at Simple-talk.com

http://www.simple-talk.com/sql/performance/sql-server-wait-events-taking-the-guesswork-out-of-performance-profiling/

And this one going deeper on SQLOS scheduler issues:

http://www.simple-talk.com/sql/performance/taking-the-guesswork-out-of-sql-server-performance-profiling-part-2/

With SQL Server 2008 and up we have Extended Events that can track wait statistics (among other events) on the session level. These days, 2011, there is a lot of information on how to do this, and better documentation is available on individual wait events. In 2008 I published a paper on getting wait info from Xevents here: http://www.simple-talk.com/sql/performance/investigating-sql-server-2008-wait-events-with-xevents/ 

On the Tools page, you can download some old SQL Server 2000 internals tools, most of them used in the 2003 presentation. Oracle techies will recognize a lot of stuff which they see in their daily life, but were unavailable in SQL Server.

Any feedback, questions etc are welcome, Please contact me at:

mailto:mariob@sqlinternals.com