Blog

T-SQL Tuesday #175: Old Tech, New Tech, Bold Tech, Blue Tech

Invitation from Andy Leonard.

Microsoft Build 2024 was just last month (at the time of this writing) and the sheer number of announcements is almost overwhelming! You can read about the announcements from the Build Book of News and view selected sessions online.

I am honored to host this month’s T-SQL Tuesday and the topic I chose is: How do you balance the benefits and liabilities of older, existing data and software platforms compared with newer platforms? What factors do you consider? Which features motivate you to switch or stay? I’m almost positive the answer is, “It depends,” and I certainly understand because that’s also my answer! I ask that you complete that sentence, though; what does “it depend” upon?

“It Depends on…”

I spend most of my time with data platforms. I focus on data engineering so I pay particular attention to the field. My answer is: I weigh the pros and cons and (attempt to) measure the inertia of switching against sticking with the existing platforms. There’s objectivity and subjectivity in this approach. Where’s the line between the two? Excellent question.

Regarding Data Engineering

Although I continue to provide ADF and SSIS training and consulting, I am all-in on Microsoft Fabric – specifically Fabric Data Factory.

I looked at Fabric Data Factory after Fabric entered public preview during Microsoft Build 2023. I poked around a bit and decided, “Not yet.”

I looked again in November 2023 and concluded: “Huh. Ok. Time to tinker.” Since I was busy with client work, I really didn’t have time to tinker until January 2024, and what I saw impressed me mightily. I cannot recall the specific features and improvements I saw, but they weren’t there in November and they were there in January. I was hooked. Since that time I’ve been paying attention to the posts of Fabric people at Microsoft as well as the Microsoft Fabric Updates Blog (yes, there’s a blog focused solely on Fabric updates… that’s a clue).

I noticed Microsoft is investing a lot of time and energy (i.e. money) into Fabric. In fact, I’ve never witnessed Microsoft develop so much so fast in my experience, and my experience dates back to when the years began with a 1. Having years of experience isn’t good for many things, but it’s great for identifying trends that span decades.

That’s why I’m all-in.

How About You?

  1. How do you balance the benefits and liabilities of older, existing data and software platforms compared with newer platforms?
  2. What factors do you consider?
  3. Which features motivate you to switch or stay?

T-SQL Tuesday #174: Your Favorite Job Interview Question

Invitation from Kevin Feasel.

I’VE GOT A JOB FOR YOU

If you’re reading this, there’s a pretty good chance you’ve been on a job interview before. You might even have received a job at some point!

Or maybe you’re on the other side of the hiring table: you’re searching for the best possible candidate for your company’s Paper Shuffler 3 opening (with a possibility of promotion within the next 5 years to Paper Shuffler 4).

Today’s question applies to both sets of people: interviewers and interviewees. The question is, What is your favorite job interview question? There’s a lot of latitude in how you answer this, and as a spoiler, that’s the type of question I like a lot.

I HAVE A QUESTION, BY WHICH I MEAN I’M GOING TO STAND AT THE MICROPHONE FOR FIVE MINUTES AND RAMBLE ON ABOUT MY OWN PERSONAL INTERESTS TO THE EXCLUSION OF WHATEVER WE’RE ACTUALLY GATHERED HERE TO DISCUSS

Job interviews are a strange and awkward dance, where interviewers and interviewees are trying their best not to embarrass themselves too badly while simultaneously attempting to suss out whether there’s a mutual fit. Along the way, the interviewers tend to ask a bunch of questions, listen to the interviewees’ responses, make affirming noises on occasion, and quietly write down their lunch orders because they already know this person’s a hard pass but they feel like it would be rude to cut the interview short.

But what actually goes into these interview questions? Are you simply googling “DBA job interview questions” and asking people whatever pops up at the top of that list? Or maybe asking your favorite chat bot to do your job for you, like an even-more-dystopian version of Wall-E?

Hopefully not! Because this is your chance to shine, dear interviewer. And dear interviewee, as well: remember, you’re interviewing the company at the same time that their agents are interviewing you. You can and should have questions of the company and the job.

Now that I’ve given you the rough idea of the task at hand, I’ll share some of my favorite questions, both as interviewer and interviewee.

A SET OF QUESTIONS

I’ll split this two ways, once as interviewer and once as interviewee.

Interviewer

Because I struggle to come up with just one question in this section, I’ll give you a few. And it’s only happenstance that none of the questions in this section actually come in the form of a question.

TELL ME WHAT YOU SEE

As an interviewer, I love open questions, some of which don’t necessarily have correct answers. For example, here’s an idea I stole from Brent Ozar. I really like showing a picture to an interviewee and asking what they see, similar to a Rorschach test but hopefully with fewer psychological hang-ups. I’ll show a person a picture like the following:

Clearly, this patient has Oedipal issues. I recommend he blind himself and go into exile.

While showing the image, I tell the interviewee something like, “I would like you to tell me what you see in the picture. There are no right or wrong answers, but I would like to hear what you find interesting about this.”

What’s important is, these pictures are simulations of real code and processes. This is a picture from Azure Data Studio that includes some T-SQL. If I’m hiring a person to be a T-SQL developer, I’ll want to hear about the code itself—with bonus points for telling me a horror story about the performance of PERCENTILE_DISC() or PERCENTILE_CONT() over large datasets and how you’ve had a great time working with APPROX_PERCENTILE_CONT() in SQL Server 2022—and recognition of the tool. This may spur on some further conversation. But what’s important is, I don’t necessarily have some set of right or wrong answers, such as “You must point out these six things on the image in order to pass.” For example, if the interviewee hasn’t used the WITHIN GROUP clause before, we might chat about that. Or maybe I’ll hear a story about using SQL Server ML Services and calculating five-number summaries in R instead. What I want to get out of this is your level of familiarity with the most important tools and code concepts that we’d expect a person to have based on our job description.

ASK YOURSELF A QUESTION

This question I stole from Sean McCown. It’s simple but powerful: “During this interview, we certainly didn’t cover everything there is to know about this field. What I’d like you to do is, ask yourself a technical question. Then, provide the answer to it.”

Sometimes, during an interview, a candidate has a rough time of it. You’re mentally ticking a lot of “wrong answer” boxes and ready to write this person off. But you could be missing something. Suppose the candidate is a replication savant, but you didn’t ask anything about replication at all during the interview. This gives the candidate a chance to talk about replication and gives you a chance to drill into the topic further. Even if your company doesn’t use replication at all and you still pass on the candidate, what this question can do is change the feeling of the interview from “How did this person possibly get past the screening?” to a more positive outcome. And there have been cases in which I’ve recommended a person for hire who didn’t do a great job in some of my questions but opened up an entirely new avenue of discussion with this question, where it helped me learn that the person was erudite but in a somewhat different field, and so we could pivot the line of questioning and even expectations for the position based on this.

From the interviewee side, there’s a bit of risk here. The candidate could, of course, ask something like, “What is 2+2?” and then provide the answer. That’s pretty weak. As an interviewer, I’ll take it as an answer, but it’s a red flag. On the other side, there’s risk from trying too hard to impress the interviewer, asking yourself a question whose answer you don’t actually know. If the interviewer does happen to know that answer and you get it wrong, that’s a self-inflicted oopsie.

TEACH ME SOMETHING

The final question I like to ask during an interview is, “Tell me something you have learned recently, with one caveat: it can be about anything except SQL Server [or whatever the job entails].”

I ask this for three reasons. First, I like to learn new things, and I have a captive audience candidate in front of me. Second, it’s a direct opportunity for the type of person who talks about “life-long” learning to walk the walk. Learning isn’t just about the job, so if learning is a passion, you’re (hopefully) picking up on things outside of your day-to-day job. Third, this gives even a junior-level interviewee an opportunity to explain a topic, allowing me to listen to this candidate’s ability to frame and explain an idea under some pressure.

Along the way, I’ve had discussions with candidates about the right way to load flatware in dishwashers, the best chicken noodle soup recipe, historical events, and characters in a then-recent anime. Did any of these questions or answers cause me to hire someone I wouldn’t otherwise have hired? No. But I will point out a correlation: 100% of the people I’d already planned to recommend for hire have had an answer to this question. Well under 100% of the people I’d already planned not to recommend for hire had an answer.

Interviewee

As the interviewee, I want direct answers to some of the most important aspects of the job. The job description may say one thing, but what you get from interviewers may be a different story. Here are some of the types of questions I’m liable to ask at almost any job interview:

  1. What expectations do you have around hours per week worked? For me, any number over 40 is a red flag, though your mileage may vary depending on industry and job.
  2. When is the last time people on your team worked overtime? What was the reason for that? I’m trying to determine what the overtime demands are. Sometimes you do need to put in extra hours, but it’d better be temporary and for a good reason.
  3. Is there a budget for continuing education and training? If so, how much is available?
  4. A train is heading eastbound from Lincoln, Nebraska at 68 miles per hour. Simultaneously, a train leaves a station in Chicago, heading west at 61 miles per hour. What is the nearest town (with a population of at least 5,000 people) from the point at which these two trains pass each other? If the interviewers ask me dumb riddles about irrelevant topics they got from a Google search or job hiring book from the ’90s, I get to counter-ask similar questions.

T-SQL Tuesday #173 – Has AI Helped You with Your SQL Server Job?

Invitation from Pinal Dave.

Hello, SQL Server enthusiasts! It’s time for another exciting edition of T-SQL Tuesday, and I’m thrilled to be your host for this month’s episode – Has AI Helped You with Your SQL Server Job?

As database professionals, we’re always looking for ways to improve our efficiency and effectiveness in our daily tasks. With the rapid advancements in artificial intelligence (AI) and machine learning (ML), many of us have started exploring how these technologies can assist us in our SQL Server jobs.

Your task for this month: write a blog post about how AI has helped you in your role as a SQL Server professional, and schedule it for next Tuesday, April 9.

Here are some ideas to get you started:

  • Have you used AI-powered tools to optimize your SQL queries? Share your experience and the results you achieved.
  • Has AI assisted you in detecting and resolving performance issues in your SQL Server environment? Tell us about the tools you used and the insights you gained.
  • Have you leveraged AI to improve your database design or data modeling processes? Discuss the techniques you employed and the benefits you observed.
  • Has AI helped you automate routine tasks, such as index maintenance or backup management? Share your automation journey and the time savings you achieved.

Remember, the goal is to share your personal experience and insights on how AI has impacted your work as an SQL Server professional. Don’t worry if you haven’t had a groundbreaking AI project; even small improvements and efficiency gains are worth celebrating and sharing with the community.

T-SQL Tuesday #172: Hekaton FTW (For the Win)!!!

Invitation from Todd Kleinhans.

First off, what in the world is Hekaton?

Hekaton was the internal project name within Microsoft to implement in-memory tables (IMOLTP) both with and without persisting data to disk (more on that aspect later); it was designed with a completely latch-free and lock-free data structure based on timing. Short version- if implement correctly and properly, it would give fantastic performance if used properly and correctly.

From what I have gathered over the years, it was announced with great fanfare and I even have a signed copy from Kalen Delaney of her book on the subject!

Enter Stage Left – Sad Trombone

So if this shiny new and awesome hammer was built and introduced, why didn’t it take off like wildfire? I was incredibly hopeful for a variety of reasons that will come out shortly but sadly disappointed- but then like a phoenix rising from the ashes, it once again has relevance in my daily life as a SQL Server DBA at my current employer. Slowly but surely, starting off with tempdb metadata performance using IMOLTP this is about to become an intense ride.

Are You Using Hekaton? How is it working?

I realize Hekaton is an edge-case scenario for many and yet those who need it have been waiting for several years for it to be fully implemented, regression tested, and be able to sell this awesome capability to upper management with zero to little risk.

Your Mission- Make the Case for Hekaton

How are you using Hekaton? Hits, misses, etc. – What went well and what went poorly?

Please let the community know so that we can learn from each other and have the strength and encouragement to make things better than when we inherited it.

At my current employer we are about to test and implement tempdb metadata using IMOLTP. I have future plans for using Hekaton inside of containers on a customer by customer basis in addition to some other GPU go-fast magic. More to come…

T-SQL Tuesday #171 – Describe the Most Recent Issue You Closed

Invitation and roundup from Brent Ozar.

Your readers wonder what kinds of jobs are out there in the database world, what exactly it is that you do, and what your daily grind is like. While it’d be cool to cover all of that, let’s start with something simple.

Your mission for this week: write a blog post about the last ticket you closed, and schedule it for next Tuesday, February 13.

It doesn’t have to be T-SQL. T-SQL Tuesday has evolved to cover all kinds of data topics.

The task/issue doesn’t have to be indicative of your overall career. Our database jobs cause us to do all kinds of oddball things through the day. Go into your ticket system, help desk system, list of Github issues, or task list right now, look at the last task you checked off, and blog about that.

Don’t include company specifics or anything that might get you in trouble. Just talk in general terms about:

  • Why the task was created (an error popped up, a user had a problem, your boss had an idea, whatever)
  • General terms about work you had, what online resources you found helpful, how long it took
  • How often that kind of task pops up in your queue

T-SQL Tuesday #170 – Learning from Abandoned Projects

Invitation and write up from Reitse Eskens

appy new year to all of you avid bloggers! I hope most of you had a great time during the past weeks, though most of you have seen the sad news of the loss of an excellent member of the Sql Family. Let’s keep Leila in our minds and send strength to Reza.

I was having a nice chat with Steve last year on possible subjects for this fun monthly blog event. And my initial inspiration was “what have you learned from abandoned SQL projects”. Yes, let’s kick off the year on a happy subject, talk about failures.
But wait, you should look on this one in a positive way, like Buck Woody (T) does

We all learn by doing stuff, but we learn most by ‘failing’, working on things that are less than optimal. If you have the time to look back on projects, to review them, you usually see where things went wrong. I think that most of us have been in projects like that and have learned from that.

Let me give you an example. A few years ago we started a project where we had to use an Azure Sql database as the target database. Sources were on-premises and with some magic wizardry, the data landed in the Azure database. But the performance was less than expected. Because deadlines we had to rush through a number of options and ended up with a database we had to reconfigure a number of times after the project finished. When we did the review, this was a bit of a sore point because I felt (and feel) responsible for this part.
During the review, I decided to dig into the different tiers and sku’s of Azure Sql databases and find out their differences in behaviour. This lead to a series of blog posts (that have to be revised as a lot of things changed since writing them). But the key is, I can now give a better advice and reduce the number of reconfigurations.

Now, I could go on, but the main intent of this blog is to trigger your stories; what projects did you abandon but learn a lot from OR what’s your favourite learning from a failure.

As it’s january, I’ll give you some slack on the subject and how much you digress.

T-SQL Tuesday #169 – Thank You

Invitation from Kay Sauter

T-SQL Tuesday No. 169 – December 2023 I’ve got the honor to invite you to the blog party SQLTuesday, tagged as #tsql2sday, for this December 2023. Many thanks to Steve Jones (blogtwitter) for letting me host this month’s blog party!

December is the last month of a year. I believe December is the season of being thankful, or at least looking back. I also believe that we generally do not say “thank you” enough to people. So I invite and encourage you to say thank you to anyone who helped you:

  • Work colleagues or employers
  • Mentors
  • Friends
  • Family

Your post does not have to be personal, it can also be just a story you anonymize people so that the reader who it actually matters, will know that you appreciate them. If you even can incorporate some code, please do! Happy blogging and happy SQLTuesday! Please publish your blog article on December 12 2023.

T-SQL Tuesday #168 – Mature Window Functions

Invitation and roundup from Steve Jones.

We’ve had window functions in SQL Server for a decade now, since SQL Server 2012.

This month I’m asking you to write on how window functions have made your life easier. A few ideas for you:

  • What problems have you solved with a window function? Bonus points for lead/lag/first_value/last_value
  • Have you used the SQL Server 2022 enhancements in any queries?
  • How has performance improved for you with a window function
  • Draw a picture of a window with a spatial function – more extra points

Give us some specifics, with real world problems. Obfuscate the data, at least if you have my name in your dev system, but help others understand how they might solve a complex aggregate using a Window function. The more specific examples, the more others might get help from one of the posts.

T-SQL Tuesday #167 – Encryption and Data Protection

Invitation and roundup from Matthew McGiffen.

For those of you who don’t know, T-SQL Tuesday is a monthly blog party where pople are invited to blog on a chosen subject related to SQL Server. You’re invited to join in whether you’re an experienced blogger, or have never blogged before. It’s a nice way to get started writing with a ready audience who’ll view your post.

Any of you who know me will know I have a keen interest in SQL Server Encryption and Data Protection so that’s the topic for this month’s blog party.

Any contributions relating to that topic are most welcome, and treat it as you see fit. Maybe you have a technical detail or discovery you want to share. Maybe you have a useful “how to”. Equally welcome are stories about encryption gone wrong – or incidents where data wasn’t protected sufficiently, and bad things happened. Or stories with a happy ending. It’s a deliberately wide topic, and I’d love a wide variety of contributions.

T-SQL Tuesday #166 – Why Not Extended Events?

Invitation from Grant Fritchey.

With 165 T-SQL Tuesday events, two, just two, this one, T-SQL Tuesday #166, and another one back in 2018 or 2019 (I forget and I’m far too lazy to go look) have been on Extended Events.

At conferences I’m frequently the only one doing sessions on Extended Events (although, sometimes, Erin Stellato is there, presenting a better session than mine). I did a session at SQL Konferenz in Germany earlier this week on Extended Events. Hanging out in the hallway at the event (which was great by the way), I was talking with some consultants. 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?

Search Engines

I did a search on BingGoogle and DuckDuckGo. The results were instructive.

The top result on Bing was to a 14 year old StackOverFlow post. To say the least, yeah, it’s not showing anyone how to use Extended Events. It talks about DMVs in addition to Trace/Profiler.

The top result in Google was a site I’ve never even heard of before, Sit24x7.com. It talked about DMVs, and nothing else. I couldn’t find a publish date on the article, but since it didn’t talk about sys.dm_exec_procedure_stats, but only talked about query_stats, either the person writing it was ignorant of more recent DMVs (and, let’s be clear, saying that procedure_stats is recent is s stretch), or this is a very old article indeed.

The top result in DuckDuckGo was a post on SQLShack written in 2017 using examples from SQL Server 2016. The tools used were, kind of oddly, Activity Monitor within SSMS and Query Store. No mention of Profiler/Trace, Extended Events, or even DMVs. The second result was the 14 year old Stack overflow post.

If you were looking to identify a long running query, you might be lead to believe the consensus is that the only tool to use is DMVs.

The Whole First Page

The common wisdom is that people never go beyond the first page of search results (I’ve no idea if this is true). So, what’s on the first page?

Well, Bing had eleven results that weren’t ads when I ran the query linked above. It wasn’t until links 7 and 8 that Extended Events are mentioned. Further, links 7 & 8 were the same article, just published in two different places with a few edits between them. Four of the links were to Microsoft and NONE (zero, 0, zip, nicht) of those mentioned Extended Events, although they did talk about DMVs. Of the top 10, most of the links were old. Many 10 years or more. The two links, 7 & 8, were the only ones to mention Extended Events.

With Google I saw 10 non-sponsored links on the first page. Many of them were duplicates of the links in Bing, just in a different order. Link 4 was to the same article I found in Bing. Link 5 was to a new source that did had Extended Events as the #1 tool for gathering query performance metrics. There was only one Microsoft link, duplicated from Bing, and it didn’t list Extended Events. Just like with Bing, most of the links were old. Many of the links from Google were older than the ones from Bing.

DuckDuckGo was just a little better. The 3rd and 4th slots had two different articles talking about Extended Events with the 3rd slot being the same article from both Bing and Google and the 4th slot being a new one. Three of the slots were Microsoft and again, no mention of Extended Events. And, once more, many of the links are minimum 7 or 8 years old, but some being 13 or 14 years old.

Conclusion

We can have a lot of discussion about the technical aspects of Extended Events. We can also talk about whether or not you should, or shouldn’t use Extended Events. The simple fact of the matter is, there’s a good chance that people aren’t using Extended Events, not because they’re problematic, hard, contain XML, muscle memory, or any of the other issues that I, and others, bring up, but instead, because they simply don’t know that they exist.

So, if you are #TeamXE, not only do we have to overcome years of bad information and indifference due to a poor launch (2008 XE just wasn’t good, let’s be honest), but the fact that the way search engines work, Extended Events may be hidden from many people.