A Fine Slice Of SQL Server
Arun Sirpal compares on-prem extended events to what’s available in Azure SQL Database:
There are 22 actions and 261 events. Naturally less than your local based SQL Servers, for example on my local 2014 machine running the above query returned 50 actions and 284 events.
There are a few subtle differences and a couple not-so-subtle differences, so it’s worth digging into if you plan to spin up an Azure SQL Database database.
Comments closedErik Darling shows how to diagnose a high recompilation problem:
So yeah, that function seems to get up to something once for every ID you pass in. Remember that in our STUFF… query, we grabbed the TOP 10 each time. In the XE session, each time we call the proc, the string splitting function compiles and executes code 10 times. Bummerino. That’s the life of a loop.
On SQL Server 2016 (and really, with any non-looping code), we can get around the constant compilations with a simple rewrite. In this case, I’m calling 2016’s STRING_SPLIT function instead of the MSTVF function.
Read on for more.
Comments closedKendra Little shows how to query the histogram target for Extended Events:
I like using the histogram target because it’s relatively lightweight — you can “bucket” results by what you’re interested in. In my case, I was interested seeing the cumulative number of file_read events by file name.
But there’s one problem: the histogram target is stored in memory, not in a data file. If you want to query that data and store it off in a table, it’s not obvious how to do that.
Click through to figure out how to do that.
Comments closedHere’s the Connect Item involved – written by Michael Swart.
Trace flag 2390 can cause large compile time when dealing with filtered indexes. (Active)
https://connect.microsoft.com/SQLServer/feedback/details/2528743/Huh. “What does that have to do with clustered columnstore indexes?” you might ask. No, really, it’d be a personal favor if you *did* ask…
Ask and then read on. This is the kind of post I’d send to someone wanting to learn how to troubleshoot issues.
Comments closedDave Mason looks at two Connect items related to working with extended events:
One Event Behind
I another post, I wrote that the XEvents event_stream target is regularly “one event behind”. There is an existing Connect item seeking a fix to this problem: QueryableXEventData and “Watch Live Data” one event behind. If you use the “Watch Live Data” grid for XEvents in SSMS, this is an important issue and worthy of your upvote. It’s also important if you ever want to access XEvent data programmatically with C# or PowerShell because the QueryableXEventData class uses the event_stream target and is also subject to the issue.
Read on for more details.
Comments closedDave Mason builds a Powershell script to parse Extended Events information:
Powershell takes center stage for this post. Previously, I showed how to handle a SQL Server Extended Event in C# by accessing the event_stream target. We can do the same thing in PowerShell. The code translates mostly line-for-line from C#. Check out the last post if you want the full back story. Otherwise, continue on for the script and some Posh-specific notes.
Read on for the code.
Comments closedDave Mason writes a custom app to read extended events data:
It took me a while to make the transition from SQL Profiler to Extended Events. Eventually I got comfortable enough with it to use it 100% of the time. As I read more about the XEvents architecture (as opposed to just “using” XEvents), I gained a deeper appreciation of just how great the feature is. My only gripe is that there isn’t a way to handle the related events from within SQL Server using T-SQL. DDL triggers can’t be created for XEvents. And they can’t be targeted to Service Broker for Event Notifications (not yet, anyway). For now, the one way I know of to handle an XEvent is by accessing the event_stream target via the .NET framework. I’ll demonstrate with C#.
9/10, would have preferred F# but would read again.
Comments closedDave Mason looks at an issue in which the Extended Events viewer seems to be consistently behind:
The database_detached event was clearly received by the ring_buffer and event_file targets. But if we return to the “Live Data” tab/grid in SSMS, we still have nothing. I don’t have an explanation for this. I know, that’s not exactly gratifying. Nonetheless, let’s press on.
Read on for alternatives.
Comments closedChoose the script that works for you. You can:
Use a simple Extended Events trace to get deadlock graphs via the sqlserver.xml_deadlock_report event
Use a Server Side SQL Trace to get deadlock graphs (for older versions of SQL Server, or people who like SQL Trace)
Use a (much more verbose) Extended Events trace to get errors, completed statements, and deadlock graphs. You only need something like this if the input buffer showing in the deadlock graph isn’t enough, and you need to collect the other statements involved in the transactions. You do this by matching the transaction id for statements to the xactid for each item in the Blocked Process Report. Warning, this can generate a lot of events and slow performance.
I’d default to script #1 and look at #3 in extreme scenarios.
Comments closedKendra Little has a few Github gists showing how to configure the blocked process report:
I wanted a friendly way to share code to configure and manage the Blocked Process Report, so I’ve created a gist on GitHub sharing TSQL that:
Enables the Blocked Process Report (BPR)
Collects the BPR with an Extended Events trace
Collects the BPR using a Server Side SQL Trace (in case you don’t care XEvents or are running an older version of SQL Server)
Lists out the Extended Events and SQL Traces you have running, and gives you code to stop and delete traces if you wish
Click through for the code.
Comments closed