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 in my travels, I also came across five more new-to-SP1-from-RTM extended events. They occur in the “warnings” category when you’re using the XEvent GUI, and correspond, for the most part, to warnings that were added to query plans in SQL Server 2012 RTM. So if you liked these as warnings you’ll probably like them as events. They are:

plan_affecting_convert – Occurs when a type convert issue affects the plan.  The expression value indicates the conversion that can cause inaccurate Cardinality Estimation or prevent from using a seek based plan in the query plan choice.  If performance is affected, rewriting the query could help.

spatial_guess – Occurs when the optimizer cannot get enough join selectivity information.  Use this event to look at the queries where spatial index could be used and the optimizer guesses the spatial selectivity. (Note: I *always* seem to see this hint, even when there should be enough information for the optimizer to guess the spatial selectivity, and filed a Connect item for this one.)

unmatched_filtered_indexes – Occurs when the optimizer cannot use a filtered index due to parameterization.  For each such index an event is fired.  The unmatched_database_name, unmatched_schema_name, unmatched_table_name, unmatched_index_name fields give details of the filtered index that could not be used.

The “original” new SQL Server 2012 plan warnings, hash_warning and sort_warning exist in RTM in the “errors” category; they came over to extended events when all the SQL Profiler events were moved over.

There is also one additional warning.

optimizer_timeout – Occurs when the optimizer times out either due to spending too much time or hitting a memory limit.  Use this event to look at all the queries that are impacted by the optimizer timeout in a particular workload. This can be very useful when tuning a particular workload.

Finally, there is an additional replication XEvent in the replication category:

logreader_start_scan – Outputs the replnextlsn value from the database table that the first replcmds scan uses as a starting point.

Happy event-ing!


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.