Utilize SQL Server Extended Events in SSMS

SQL Server 2008 introduced a new eventing architecture called Extended Events or Xevents. With Xevents, you can monitor and troubleshoot SQL Server and bring together both database and Windows OS events using event tracing for Windows (ETW) which means that it is a very powerful mechanism that can be used by many tools including Windows debugging and troubleshooting tools and integrating into monitoring tools for Windows.

But there is not an out of the box tool to set-up Extended Events and SQL Server Management Studio (SSMS) does not have any capabilities to provide support for Extended Events yet, even in SQL Server 2008 R2. Personally, I do not find Xevents intuitive or easy to configure and set-up.

But I came across Jonathan Kehayias’ Codeplex tool for managing extended events in SSMS. I’ve been using it today and I LOVE IT! Kudos to Jonathan for sharing this awesome tool with the community on Codeplex. I recommend that you download it and try it if you would like to begin using the power of extended events to monitor SQL events. I am currently making use of the SQLOS events.

BTW, just to add: based on my experience with add-ins and GUI-based solutions on Codeplex, I found Jonathan’s Extended Events Manager to be excellent compared to many other tools that I’ve tried.

Let’s Talk about SQL Server Performance … Part Uno

Ok, there is no way to effectively blog about SQL Server performance. It’s a HUGE topic. But I need to add some content to this blog as a starting point. I’ve been giving it a lot of thought, that is, where do I start in this blog around SQL Server performance?

What I’m going to do is to use a recent customer correspondence that was a very broad ask for more information on SQL Server performance. I’ve cobbled that together into a series of focus areas below with links to details in the form of whitepapers and on-line articles.

So what I will do going forward for MSSQLDUDE blog, is to dive into each of these areas as a focus area for SQL Server performance with summarizations of some of these very large, lengthy, detailed and complex whitepapers. My intent here will be to provide my casual readers with a way to quickly grasp the concepts around SQL Server performance monitoring & tuning.

So, away we go!

We have to start somewhere, so a good starting point is to understand Clustered Indexes on tables vs. Heap tables. Then I would make use of the awesome out-of-the-box SQL Server tool called the Database Engine Tuning Advisor that can analyze your queries and give you guidance on where you need additional index coverage. For those who wish to really dig deeper into SQL Server’s engine internals to understand performance, there is a very detailed whitepaper here on plan caching from the unbeatable SQL Server CAT team at Microsoft in Redmond.

SQL Server uses the out-of-the-box concurrency mode, READ COMMITTED, to prevent dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or “phantom” data. Snapshot Isolation concurrency mode was first delivered in SQL Server 2005 and can be useful in very high-transaction OLTP systems. Use the out-of-the-box SQL Profiler tool to identify blockers or deadlocks and read here more about Snapshot Isolation.

Hardware & Storage Level
There is very important guidance in this best-practice page regarding how to configure storage, in particular, to maximize performance of your SQL Server instances. A note here is that what I find most frequently at customer sites using SQL Server is that not enough attention was paid up-front into understand the ways that SQL Server accesses data from TempDB and how SQL Server reads from disk. Also, SQL Server is very good at scale-up in SQL Server & Windows Server 2008, taking advantage of kick-butt hardware where it can utilize literally hundreds of virutal processors and 2 TBs of RAM. So if you are a DBA or developer, make sure your storage and system admins read through the whitepapers on configuring the right server hardware and SAN techniques that are particular to SQL Server and are different than you will see in Oracle’s database recommendations.

Memory Pressure
It is critical that, as a DBA, you actively monitor for memory pressure on your SQL Server boxes and measure and update your baselines regularly to determine hardware strategy and possible scale-out or scale-up requirements. Here is a link on how to identify memory pressure on your SQL Server box using the Windows Server System Monitor.