Just a quick shout-out today to MSSQLTIPS for this article today on an introduction to what is, IMO, the most under-rated cool new SQL Server 2008 feature: Extended Events. X-Events is a SQL Server sub-system that is always on and registers information that is available to you as a DBA, about your system. It is a light-weight (in terms of system impact) version of SQL tracing and provides information that you can interogate about what is good and what is bad on your SQL Server. You can even integrate the events with the Windows Server Event Tracing subsytem.
SQL Server DBAs have been using server-side tracing, default trace and client-side tracing with SQL Profiler, for many years. So switching to a new mechanism for SQL Server monitoring and troubleshooting is new and unnatural. But make no mistake, Extended Events is THE way to monitor SQL Server events going forward. Denali will see a big increase and improvement in using X-Events.
Perhaps if SQL Server 2008/R2 had shipped with a nice GUI tool or integrated with SSMS, adoption of extended events would be greater. But you can download and install the free Codeplex add-in for Extended Events which you can get to from the link at the top of my post from the MS SQL Tips article. The free download for the x-events explorer very much simplifies setting-up and querying events. Even so, it is still a learning curve because with extended events in SQL Server, you have to learn about event targets and sessions and how to configure those.
In all honesty, I myself, do not use extended events very much yet. But, I’m no longer a full-time DBA or developer. Now I just try to help those who are in that role! But I do think this can be a very helpful way to get to the bottom of the good & bad of your SQL Server installations.