Press "Enter" to skip to content

Category: T-SQL

Handling SQL Agent Dates and Durations

Published 2025-03-10 by Kevin Feasel

Andy Mallon disparages some Microsoft intern’s summer of 1996 project:

SQL Agent’s schema is older than me. It handles dates, times, and durations like it’s 1980 by using integers instead of date/time data types. My buddy Aaron Bertrand talks more about Dating Responsibly so that you can have a good datetime with your own database.

I was writing a query to pull recent job failures from SQL Agent’s msdb job history, and knew that I didn’t want to deal with the wonky date/time formats. Specifically, I was querying msdb.dbo.sysjobhistory to find the Start Time, End Time, and Duration of job runs that failed. If you aren’t familiar with that table, you can look at it over in the docs.

Andy does point out the built-in function but then explains why a separate function is superior. Andy also happens to furnish that function, so check it out.

Comments closed

Performance Comparison: Tally Table vs GENERATE_SERIES()

Published 2025-03-06 by Kevin Feasel

Steve Jones performs a pair of tests:

I had someone reach out about generate_series() recently, saying they hadn’t realized this was a new feature in SQL Server 2022. They were wondering if it was better than using a tally table.

I didn’t want to do an exhaustive test, but I thought I’d take a minute and try a couple simple things just to see.

Steve used the CTE-based tally table builder, building based on cross joining spt_values. This is one of the classic approaches. The performance differences aren’t enough on their own to justify large-scale changes if you’re using a classical tally table, though it is good to see that GENERATE_SERIES() does perform well. And if you’re not familiar with the power of a tally table, here is one great explanation of the concept.

Comments closed

Avidity KPIs in T-SQL

Published 2025-03-05 by Kevin Feasel

I have a new video:

In this video, we will take a look at two KPIs for measuring avidity. We will also show off how to use ranking window functions to order groups of customers.

Click through for the video. There are far too many measures of avidity for me to do a good job explaining them all, and so many of them are closely tied to the specific nature of the business, but hopefully this at least gives you ideas of how the business side may look at user avidity or stickiness.

Comments closed

Making a Query SARGable

Published 2025-03-05 by Kevin Feasel

Haripriya Naidu explains SARGability:

Having the right index is helpful, but are you using the predicate (WHERE clause) correctly to make efficient use of that index?

This is where the term SARGable comes into play. SARGable stands for Search ARGumentable. If SQL Server is able to limit the search space while evaluating the predicates and can seek right at the page(s) to get the values, then it is SARGable.

Read on for an explanation of why this is important, as well as several examples of what is SARGable versus what isn’t. The most important thing about SARGability is that you pronounce it like “Sarge” and not “sarg.”

Comments closed

Seeding an Availability Group via T-SQL Snapshot Backup

Published 2025-02-28 by Kevin Feasel

Anthony Nocentino jump-starts an availability group:

In this post, the fifth in our series, I want to illustrate an example of using the T-SQL Snapshot Backup feature in SQL Server 2022 to seed Availability Groups (AGs) with storage-based snapshots. Efficiently seeding an Availability Group is essential for maintaining high availability and ensuring effective disaster recovery. With the introduction of T-SQL Snapshot Backup in SQL Server 2022, snapshots can now be created at the storage layer. This advancement significantly speeds up the initialization of secondary replicas, particularly in environments that handle large databases.

This post will walk through a PowerShell script that effectively seeds an AG using T-SQL Snapshot Backup, dbatools, and Pure Storage FlashArray.

Click through for the script and the process.

Comments closed

Removing Unnecessary Delete Operations

Published 2025-02-28 by Kevin Feasel

Monica Rathbun truncates some data:

SQL Server performance issues often stem from easy fix bottle necks that can be fixed with the right tuning strategies. This short blog will focus on the DELETE statement.

Click through for the full story. One minor correction that I’d offer is that the TRUNCATE TABLE command is logged, which is why you can roll it back in a transaction. The logging process is much less intensive than with DELETE because of the deferred drop logic that Paul talks about in the link.

That aside, Monica’s key point is absolutely correct: DELETE operations tend to be quite slow, especially as the number of records you need to delete increases. This is where techniques like batching delete operations can help reduce the pain level, but if you’re deleting every (or almost every) row from a table, there’s probably a better method. Unless replication is involved in the mix—in that case, there are no good methods for anything because replication hates us.

Comments closed

Finding what Changed in a SQL Server Cumulative Update

Published 2025-02-28 by Kevin Feasel

Brent Ozar does some sleuthing:

Over the last several years, Microsoft has been putting less and less effort into Cumulative Update documentation. We used to get full-blown knowledge base articles about fixes, but these days, we get a collection of footnotes with deceiving hyperlinks that look like they’re going to lead to more information – but they simply lead back to themselves.

So whenever a new Cumulative Update drops at SQLServerUpdates.com, before I install it, I like to:

  1. Log my dev server’s list of current sys.all_objects, all_columns, messages, configurations, etc to tables
  2. Apply the update
  3. Query the new contents of sys.all_objects, all_columns, etc to see what new ones were added

Click through for a script that does just this. It’s not as good as having detailed patch notes, but it beats having nothing.

Comments closed

Sales KPIs in T-SQL

Published 2025-02-26 by Kevin Feasel

I have a new video:

In this video, we will take a look at several useful KPIs for measuring sales. We will also demonstrate the utility of the APPLY operator, the WINDOW clause, and the APPROX_PERCENTILE_CONT() function.

I enjoyed putting this one together, because I enjoy it any time I have a chance to talk about the APPLY operator.

Comments closed