About a month ago, I wrote a series of blog entries on the Selective XML Index introduced in SQL Server 2008 SP1. Just to summarize before I start up again, the Selective XML Index (I’ve abbreviate as SXI) is a feature introduced to allow indexing of only certain paths in an XML column. It uses [...]
Bob Beauchemin
SQL Server Blog
Choosing Paths for Selective XML Index – Part 1
Posted on: January 7, 2013 11:12 am
New XEvent events in SQL Server 2012 SP1
Posted on: January 7, 2013 9:34 am
I’ve been looking a bit more into the SQL Server 2012 SP1 release, mostly to find diagnostics related to the Selective XML Index (SXI). I found some additional extended events for SXI (there are four of them in the “index” category) and I’ll cover them in, as well as exercise them, the next posting. But [...]
Reacting to XEvents in almost real-time
Posted on: November 29, 2012 2:48 pm
I've heard it said that the main difference between SQL Server Extended Events and SQL Server Event Notifications is that you can't "react to" Extended Events. Event Notifications are written to the service broker service of your choice, and multiple Event Notifications can even be routed to the same service, even on different machines. But [...]
Extended Events in Windows Azure SQL Database
Posted on: July 12, 2012 7:49 pm
Continued from the previous post, "Some new/upcoming Windows Azure SQL Database DMVs and diagnostics"… Read that one to find out who Vinod and Michal are. So, before you all run out and try it, this isn't working yet. But, like the WASD event log view, the pieces seem to be in place. So, standard disclaimer [...]
SQL Server and Pooled vs. Non-pooled connections
Posted on: May 26, 2012 5:08 pm
A variation of this question came up a few weeks ago on a mail list that I subscribe to and it seems to come up a lot. "Can SQL Server distinguish a which connections use connection pooling?" The answer depends on how you ask the question, and I've tried to ask it in such a way [...]
XEvents in SQL Server 2012: No more “lazy XML” in event harvesting scripts
Posted on: January 23, 2012 12:49 pm
I've said before how thrilled I am will the new extended event UI in SSMS for SQL Server 2012. However, you might be one of the early adopters who made up their own scripts to define extended event sessions, and use hardcoded scripts to harvest the results. So, you may run into this problem if [...]
Using SQL Server 2012 tools with older database versions
Posted on: November 21, 2011 2:44 pm
Last week, while teaching a class based around SQL Server 2008 R2, I happened to "brag up" the new tools in SQL Server 2012, mainly the Extended Event graphic user interface in SSMS and the ability of Database Tuning Advisor to tune from the plan cache. This, of course, immediately brought up the question: "Do [...]
Correlating client and server-side trace in SQL Server Denali
Posted on: August 19, 2011 11:28 am
I'm not usually one for repeating stuff you can find in BOL. Usually, a waste of your time and mine. But this feature, for SQL Server Denali, is pretty well buried; well at least from me, and I knew what I was looking for. I've always been interested in (well, wrote a whitepaper on) using [...]
The Denali XEvent SMO library
Posted on: April 22, 2011 7:13 am
I've been looking at the Extended Event library and PowerShell provider "directory" in Denali and decided that I like it. It's a traditional SMO set of classes, which means there's not a whole lot new to learn. All the SMO libraries connect to a "store" using either an integrated security default connection or an SqlStoreConnection. [...]
XEvent in Denali – When an error number isn’t error anymore
Posted on: April 18, 2011 1:47 pm
I've been going through the XEvents in Denali CTP1 in a fairly rigorous amount of detail, looking for what's changed and specifically for breaking changes. Running all my old event sessions… found one. One (well OK, quite a few) of my "for illustration" XEvent Sessions have a predicate that looks at the error number for [...]
SQL Server Denali CTP1 – PowerShell and XEvents
Posted on: November 9, 2010 1:47 pm
Just having a look at PowerShell support in SQL Server Denali CTP1. In Denali you must install PowerShell V2 or it will be installed as part of the installation process. Although the minishell architecture of SQLPS is unchanged so far. Rumor is that changing this (maybe into a module, like Chad Miller did?) might in [...]
Secrets, hints, and tips for SQL Server Extended Events
Posted on: November 8, 2008 2:37 am
I've been doing talks and demos on SQL Server 2008 Extended Events for a while now, it's one of my favorite parts of the product. Per session waitstats, SQL stack, built-in system health session, what's not to like? Before my talk at TechEd EMEA for IT Professionals last week (which went really well), I discovered something that [...]
A GUI visualizer/editor for SQL Server 2008 Extended Events
Posted on: August 31, 2008 9:29 am
One of my favorite new features of SQL Server 2008 is extended events. I've written a bunch of blog entries on 'em (use the search, type in Extended Events). So a few days ago, I recieved an email from Jonathan Kehayias directing me to his new program on Codeplex, the SQL 2008 Extended Events Manager, [...]
Getting an activity ID with Extended Events
Posted on: April 7, 2008 6:54 pm
It's easier than you'd think… When you use the SQL Server 2008 CREATE EVENT SESSION DDL statement with extended events, you specify: Events to be captured (e.g. sqlserver.error_reported) Actions to be fired to add more information (e.g. sqlserver.sql_text) Predicates for conditional capture (e.g. where sqlserver.error_reported.error = 547) Event target to collect the events (e.g. package0.ring_buffer) [...]
XEvent: Some final followup questions (slight return…)
Posted on: August 20, 2007 11:54 pm
Last blog entry on SQL Server Extended Events for a while. But…a couple of questions came up since I wrote my first blog entry on SQL Server Extended Events. What are the major advantages to SQL Server Extended Events? Is this really using the Crimson event system? There's a couple of reasons that come [...]
Finishing XEvent package items, syntax, and semantics (moon turn the tides…)
Posted on: August 20, 2007 10:07 pm
This won't be as long of an entry because I'm trying to finish describing the items that you can use in an XEvent session, that is, the items that exist in a package. Events Targets Actions Predicates Maps Types Let's do types and maps. A type is simply [...]
More about XEvent: actions and targets (still raining, still dreaming)
Posted on: August 20, 2007 9:04 pm
It's another rainy day in Portland in summer, so I thought I'd stay inside and write more about SQL Server 2008 Extended Events. I wanted to finish things off by talking about actions and predicates. Need to make a detour at targets, too. I noticed the BOL examples (my point is to try not to repeat things [...]
More about SQL Server 2008 extended events: packages, events, and event sessions
Posted on: August 19, 2007 8:16 pm
It's summertime in Oregon and its been nice and warm out (75-85F) lately. But today its raining (or specifically, the skies are quite ominous right now), so it's time to write more about my latest favorite subject: SQL Server 2008 Extended Events. The SQL Server 2008 extended events introduce quite a bit of new terminology, but [...]
Making the XEvent ETW target run
Posted on: August 16, 2007 7:20 pm
When starting out with XEvent support, I thought it would be good to start with the ETW target, although you can capture and catagorize events in buckets with the async bucketizer target, and pair related events (like obtain lock/release lock) with the pair matching target. Both VERY cool. But I just wanted a raw, vanilla trace, [...]
XEvent in SQL Server 2008
Posted on: August 16, 2007 6:49 pm
When I'm doing problem solving, its always good to have too much information rather than too little. With this in mind, I was quite interested in looking at SQL Server Extended Events (XEvent support) in SQL Server 2008. You could always get diagnostic information in SQL Server through DBCC and SQL Trace/SQL Profiler. In SQL [...]
