Press "Enter" to skip to content

Category: T-SQL

Managing tempdb Activity

Aaron Bertrand has built a solution:

Managing tempdb growth tends to be a very reactive exercise. We size tempdb using a predictive ruleset based on expected workload, database size, concurrency, and other imperfect factors. Then someone does something silly and fills the T: drive. If we catch it in time, we may be able to kill the offending session or add space to prevent the workload from dying. Then we tell them not to do it again; but they will, of course, so we expand the drive again and hope this time it’s big enough. But it rarely is. It’s like a baseball game that’s tied in the 5,662nd inning. Each team keeps scoring a single run in every new inning; the game will never end.

Our existing “drive full” alarms start going bonkers as soon as a drive is more than 90% full. We have a dedicated drive for tempdb data, and we pre-allocate the data files to just under 90% of the available space. This prevents any growth events while they’re using tempdb within the bounds of its current size. But when we fill the space already allocated to the data files, they grow. After one or two growths, we have crossed the threshold for drive space alerts, and it probably won’t be long before the volume fills and the workload grinds to a halt.

Click through for the solution scripts and Aaron’s explanation of it all.

Leave a Comment

The Limits of CHOOSE()

Steve Jones performs a test:


I was looking over the CHOOSE() function while editing an article at SQL Server Central. I realized there isn’t a documentation note on how many parameters can be included. I decided to test.

This post looks at the tests and results.

Click through to see how many parameters Steve was able to cram in before the whole thing fell apart. This is definitely a place where Swart’s 10% Rule comes into play.

Leave a Comment

ROLLUP, CUBE, and GROUPING SETS in T-SQL

Louis Davidson works with three amigos:

To get the total, there are a few ways, which I will demonstrate in the next sections on the aggregation functions, ROLLUP, CUBE, and then GROUPING SETS. Grouping sets are the most flexible, in that they let you do what the other two can do, but with more control. But knowing the others is important because it is how most ad hoc queries will be written.

It’s uncommon that I use GROUPING SETS() in my code, but every time I do, I’m thankful that it exists.

Comments closed

XACT_ABORT vs TRY_CATCH

Jared Westover explains the difference:

After creating a SQL script and a pull request, a savvy developer left a comment on the PR because they were confused about why I was using both XACT_ABORT and TRY…CATCH. They thought that including both in one script was redundant. That got me thinking: is it true that setting XACT_ABORT ON makes TRY…CATCH redundant?

The most interesting part of this post is the cases in which you would use both in the same script.

Comments closed

Time Zones and Offsets in SQL Server

Erik Darling doesn’t know what time it is:

Or maybe you just started a new job where they cared about time zones and then your other jobs didn’t. So any, all kinds of crazy things can happen. But when you’re working with time zones, especially if you have dates that are not time zone oriented in any way, like for example, this one, this is just a date time too.

And getting used to the functions and how they work and behave can be a little tricky at first. It can be a little mind numbing. So if we run this query, what I want to show you is just we start off with a date time too that has no time zone affiliated with it.

In November, Erik will be right about Eastern time being UTC-5, as that’s Eastern Standard Time. Or maybe Erik is secretly in Indiana, in one of the counties that stays on Eastern Standard Time year-round. Hopefully I didn’t secretly dox him here.

Comments closed

The Basics of GROUP BY

Louis Davidson gets back to basics:

For a while now, I really have wanted to learn the Windowing Functions in great detail. I know them well enough to make it through a lot of needs, but there are a lot of intricacies that are hard to remember. As I noted in my very first Blogging for Programmers post, writing for your own future needs is some of the best and easiest writing you will do.

When I wrote my “SQL Techniques you should know” presentation, the topic that took the longest was Window Functions. Because of their similarity to GROUP BY, I figured this was the best place to start.

Read on as Louis works through the concept.

Comments closed