A Fine Slice Of SQL Server
Tom Zika talks extended events:
If you look at the Tags section of my blog, you can see that Extended Events is one of the top tags. Also, my first (and currently only) public speaking session was on Troubleshoot Real-World Scenarios with Extended Events. So you could say I am a fan.
Read on to see why, including the type of things you can do with extended events, as well as the normal (because true) complaints.
Comments closedWhile my relationship with Extended Events is complicated for many reasons:
- Awful documentation
- Hardly any guidance on usage
- Almost nothing useful about what type of target to use when
- Everything stored in XML
- Slow, unfriendly GUI in SSMS
My need to use them while consulting outweighs my gripes and grievances about how Microsoft has chosen to write about, use, and present the data to you.
That’s where my stored procedure sp_HumanEvents comes in handy.
In fairness, Erik put his virtual money where his virtual mouth is, and sp_HumanEvents is put together quite well.
Comments closedRob Farley defends SQL Trace…sort of:
This month, Grant Fritchey (@scarydba) asks us to write about Extended Events (XE). He mentions that they were a topic once before (it was in 2015 – more than 8 years ago), and mentions about how they deserve more attention than that.
I kinda agree, but I kinda don’t.
Read on for Rob’s take on the matter.
Comments closedBen Miller shares an extended event session with us:
I wanted to share one of the Extended Events I always put on a server when I am in charge of it. It has to do with File growths and captures some important things for me. Before you say that it is in the system_health extended events session, I know that it is there. I have had system_health sessions cycle pretty fast and there are a lot of other events in that trace, so I decided to make my own for just that specific thing so that I can archive the sessions and keep the disk clean as well as pull this information into a table and analyze data in a tabular way instead of mining XE files.
Read on for that script and what it does in practice.
Comments closedErik Darling is stuck on a feeling:
For this most recent T-SQL Tuesday, I challenged bloggers (using the term challenge weakly here) to think of the last time code they saw made them feel a feeling.
I wasn’t terribly specific about what kind of feelings were in play, and so I kind of expected some negative ones to creep in. Most of the results were overwhelmingly positive.
Click through for Erik’s round-up of entrants.
Comments closedHugo Kornelis talks trade-offs:
But I wanted to contribute anyway. So here is a recent example of code that probably would have made me feel a way if I had been the type of person that gets emotional over code. Or put differently, here is the story of how I gained performance by reducing readability and maintainability.
For the record, and to prevent confusion, I am not going to name actual customers, nor name the ERP system used, and the description I give is highly abstracted away from the original problem, and heavily simplified as well. I describe the basis of what the issue was with the code I encountered and how I fixed it, but without revealing any protected information.
My internal motto is:
Rob Farley covers one of my favorite operators:
SQL Server 2005 was released, of course, in 2006, and I had been running the Adelaide SQL Server User Group since September 2005. Information about the new features had been coming through, and I’d been at TechEd Australia 2005 – my first since 1999. I was still an application developer at the time (well, a manager, but still getting my hands dirty), but saw the data as the most important part of my applications. When the T-SQL enhancements in SQL Server 2005 came through, there were two things that caught my eye (I know they were available in Oracle before SQL Server, but I was focusing more on the Microsoft platform by then). They were the OVER clause, and APPLY.
This is all part of a broader story about reading code to learn from it.
Comments closedShane O’Neill has an experience:
You have code that keeps coming up as a high consumer of CPU on our systems? OK?
It had gotten to the stage where it kept appearing in
sp_WhoIsActive? What’s that? And should it not be “whom is active”? Well, agree to disagree.Let’s see the code so. Wow, that’s a small scroll bar! Yeah, that’s one of ours.
I should note that “Who is active?” is correct, as the sessions we are inquiring after are the subject of the question rather than the direct or indirect object, and there is no prepositional phrase which would affect the decision.
Comments closedThe first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.
And that same thing probably happened the next several times, too.
But eventually, I took a deep breath and read the code more closely to understand what it was doing.
I do like to joke that this is cryptic code that gets handed down from generation to generation, with each generation saying “Don’t touch the code, for you do not understand it.” But as of SQL Server 2017, you don’t need to do this anymore and can use STRING_AGG().
Steve Jones talks tally tables:
That being said, years ago I got an article from Jeff Moden on the tally table. I hadn’t used this, and was fascinated. I know Itzik had written about numbers tables early on, but it hadn’t caught my attention. However, in a follow-up, Jeff wrote about a splitter function, which would use the tally table to split strings efficiently. This is the function (credit to Jeff in his article):
Click through for that function. The CLR-based tally table function is still faster, but if you can’t have CLR in your environment, and you split strings so frequently that you need a splitter, and STRING_SPLIT() just doesn’t do it for you (or you’re on an older version of SQL Server), this is a good solution. It’s also the foundation for a principle I have with T-SQL query tuning: sometimes you need to think in terms of pivoted or unpivoted data and operate on it versus the raw data. Here, we’re unpivoting a string into one row per character to get what we need out of it much more efficiently than if we tried to work the string by itself.