Correlating SQL Server 2014 cardinality events and query plan

I’ve been doing some investigation of the new cardinality estimator in SQL Server 2014. I’ve always thought the best way to see how the estimation process worked was through the query_optimizer_estimate_cardinality XEvent. I wrote about this event in an earlier CTP1 post. Joe Sack used this event to show how the new estimator uses techniques […]

Choosing Paths for Selective XML Index – Part 1

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 […]

New XEvent events in SQL Server 2012 SP1

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

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

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

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

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

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

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

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

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

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

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

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

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…)

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…)

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)

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

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

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

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 […]