Updated April, 2008: new xp_sqlstats version for SQLServer 2005 for build 3042, new trace (and readwait) documentation. (all included in the zipfiles on the Tools page)  

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). Some more recent articles of me can be found at http://www.simple-talk.com/sql/performance

Currently SQLServer2000 only maintains wait statistics at the server level. The dbcc command, sqlperf(waitstats) can be used to query this wait information. SQLServer2005 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.

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

Any feedback, questions etc are welcome, I'm actively looking for people with whom I can share experiences with my tools. Please contact me at:

mailto:mariob@sqlinternals.com