Press "Enter" to skip to content

Category: T-SQL Tuesday

The Joy of sp_HumanEvents

Published 2023-09-13 by Kevin Feasel

Erik Darling makes a pitch:

While 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 closed

Capturing Autogrowth Events in SQL Server

Published 2023-09-13 by Kevin Feasel

Ben 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 closed

T-SQL Tuesday 164 Roundup

Published 2023-07-26 by Kevin Feasel

Erik 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 closed

Optimizing for Readability or Performance

Published 2023-07-14 by Kevin Feasel

Hugo 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:

  • Start with simple, readable code
  • Move to more complex, faster performance in spots which are necessary
  • Document why the code is more complex with illuminating comments, so that way a future developer (including future you) won’t say, “What was this yokel thinking, doing this complicated thing when there’s an easy approach like this?”
Comments closed

Creativity, Learning from Code, and the APPLY Operator

Published 2023-07-13 by Kevin Feasel

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 closed

The Five Stages of Code Review Grief

Published 2023-07-13 by Kevin Feasel

Shane 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 closed

Understanding String Concatenation with FOR XML PATH

Published 2023-07-12 by Kevin Feasel

Brent Ozar did some noodling:

The 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().

Comments closed

The Tally Table Splitter

Published 2023-07-12 by Kevin Feasel

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.

Comments closed

T-SQL Query Transformations and Performance

Published 2023-07-12 by Kevin Feasel

Erik Darling isn’t satisfied with “good enough”:

Query tuning is hard work, especially when queries run for a long time and you have to test various changes trying to get it down to a reasonable runtime.

There are, of course, things you pick up intuitively over time, and figuring out where problems in actual execution plans has gotten easier with operator runtimes.

Beyond basic query tuning intuition comes the really creative stuff. The stuff you’re amazed anyone ever thought of.

Click through for two really interesting examples.

Comments closed