Press "Enter" to skip to content

Category: T-SQL Tuesday

A Starting Point for Data Protection

Published 2023-10-11 by Kevin Feasel

Deborah Melkin asks some questions:

If we start expanding things beyond just the technology and functionality, we can really see where the concept of data protection becomes much larger and more complex.

I admit that I’m not really up-to-speed on the technical aspects of encryption or data protection. That doesn’t fall under a lot of the work that I do. But there’s another side to data protection that’s worth talking about. It’s about knowing your data. This is where I’ve been spending a lot of my time these days.

When I ask if you know your data, I’m asking if you can answer the following questions:

Read on for some of the types of questions you’ll want to think about.

Comments closed

The Search for Extended Events Information

Published 2023-09-15 by Kevin Feasel

Grant Fritchey stays on the first page:

Here’s their paraphrased (probably badly) story:

“I was working with an organization just a few weeks back. They found that Trace was truncating the text on some queries they were trying to track. I asked them if they had tried using Extended Events. They responded: What’s that? After explaining it to them, they went away for an hour or so and came back to me saying that had fixed the problem.”

We all smiled and chuckled. But then it struck me. This wasn’t a case of someone who simply had a lot more experience and understanding of Profiler/Trace, so they preferred to use it. They had literally never heard of Extended Events.

Why?

This led Grant to perform some search engine shenanigans and what he found was curious. A couple of points with search engines, though:

  • Search engine results will differ based on your location (IP address) and whether you are signed in or not. Google is particularly selective about this stuff. It might also affect Bing, but let’s face it: if you’re using Bing to search for anything other than images, you’ve already resigned yourself to failure.
  • In my case, a search for “extended events” (without quotation marks) did show quite a few pages which I’d consider reasonable for the topic: a Microsoft Learn quickstart article on using extended events, Brent Ozar’s extended events material, a SQL Shack article on the topic, etc. A good number of these links are content from the past 5 years, as well.
  • Grant mentions the “page 1” effect in search engines, and he’s absolutely right. The vast majority of people performing a search never leave the first page of results. This is part of why Google went to an infinite scrolling approach rather than showing explicit numbered pages.
Comments closed

The Benefits of Extended Events

Published 2023-09-14 by Kevin Feasel

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 closed

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