A Fine Slice Of SQL Server
Here’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 closedKevin Hill reads the default trace to figure out who dropped a database:
We have a staging server that has a database for each client that we can test on before working in their production databases. Recently one has gone missing a few times…but nobody will own up to it.
If you want to set up an extended event to capture this information to disk separately, Jason Brimhall has you covered.
Comments closedWayne Sheffield reminds us that backups aren’t just for databases:
So how does this talk of AGs pertain to this T-SQL Tuesday topic? It should be pretty obvious – we need to periodically grab all of the .xel files generated by the cluster, and move them to a different directory, with a different retention policy. Yup… we need to back up these files. Sometimes, we need to be backing up things other than the databases themselves.
I created a PowerShell script that takes a few parameters, then moves the files from the source directory to the destination directory. And then it deletes files from the destination directory that are over x days old.
Wayne goes into more detail, including permissions required to run the script.
Comments closedJason Brimhall shows how to see if you’re using deprecated functionality in SQL Server:
Right here would be a good time to point out that your mileage may vary. Some items that are deprecated are more painful than others. Some may be deprecated and may have been on the list for 10+ years at this point. The point is, know your environment and then use good judgement to determine which items in your results need the most attention to fix and update. Why? Well, things really may break especially if you are looking to upgrade to a new version of SQL Server. Just because an item is still available in your current edition, that does not ensure it will still be available in a future release of SQL Server.
Now for the juicy stuff. As I mentioned, finding when and where a deprecated feature or syntax is employed, there are a couple of neat little events within Extended Events that can help to track each time a deprecated feature is accessed or employed. How do we do that? Use the deprecation_announcement and deprecation_final_support events. To help create sessions to track these events, I have the following script to create an “audit” session to do exactly that.
Click through for Extended Event scripts and demo code.
Comments closed