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 #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 #164: Code That Made You Feel A Way

Invitation and roundup from Erik Darling.

This month’s topic challenges you to think back to the last time you saw code that made you feel a thing. Hopefully a positive thing.

I’m not a thesaurus, so the list isn’t exhaustive, but think along the lines of: surprise, awe, inspiration, excitement.

Or maybe it was just code that sort of sunk its teeth into you and made you want to learn a whole lot more.

This won’t be my submission, but I think a great example is a piece of query tuning magic by Paul White. It was one of the first articles I ever read as a young SQL Developer.

It was one of many lightbulb moments (and, crap, I should have said lightbulb moment up there when I was telling you what kind of code I want you to think about) that I’ve had in my career.

The only rule I have is (outside of the normal T-SQL Tuesday rules) is that it can’t be your own code.

It should also probably be code that you won’t get sued for showing or talking about. Please be diligent in abiding by copyrights.

Other than that, it’s the normal pack of rabbits: submissions have to be posted next Tuesday, the 11th of July.

T-SQL Tuesday #161 – Having Fun with T-SQL

Invitation and writeup from Reitse Eskens.

So, what to blog about this month. Well, it’s just been April fools day and as you’re reading this, you survived. Congratulations! But it did spark a question; what fun are we having with our code? And I’m not talking about commit messages in the Git repository or funny comments inside the code. I’m just as guilty on that part as the next programmer, but I’d like to focus on something else.

What are your most fun script, procedures or statements that you’ve written. Let me give two examples to set a bit of a stage.

The first fun script I wrote is one that has some history with it. About ten years ago when my wife was pregnant we were in the garden discussing the future. We were pulling out some weeds, trimming back some plants and enjoying the spring weather. For some reason we got onto the long term future and there a long running joke emerged. Our kid would have 18 years with us, when he would turn 18, the main present would be a set of moving boxes. Let’s call it a hint. Every now and then the joke serves it’s purpose to as a lightning rod when things don’t go like we like. The remark “well, only X years to go” relieves some of the stress. Nothing more serious than that. Until some co-workers got wind of the joke and asked for more precision. So, I wrote a very small piece of code that resulted in a number of results, the amount of years, day, hours, minutes and seconds until his 18th birthday.

CREATE OR ALTER PROCEDURE sp_howlong
AS
DECLARE @birthdate DATETIME;
SET @birthdate = '2013-01-01 00:00:00'; -- enter the correct birthday here
SET @birthdate = DATEADD (YEAR, 18, @birthdate);
WITH getData
AS ( SELECT
CONVERT (VARCHAR(12), DATEDIFF (SECOND, SYSDATETIME (), @birthdate) / 60 / 60 / 24 / 365) AS ' Year(s) '
, +CONVERT (VARCHAR(12), DATEDIFF (SECOND, SYSDATETIME (), @birthdate) / 60 / 60 / 24 % 365) AS ' Day(s) '
, +CONVERT (VARCHAR(12), DATEDIFF (SECOND, SYSDATETIME (), @birthdate) / 60 / 60 % 24) AS ' Hour(s) '
, +CONVERT (VARCHAR(2), DATEDIFF (SECOND, SYSDATETIME (), @birthdate) / 60 % 60) AS ' Minute(s) '
, +CONVERT (VARCHAR(2), DATEDIFF (SECOND, SYSDATETIME (), @birthdate) % 60) AS ' Second(s) ')
SELECT CONCAT_WS (':', [ Year(s) ], [ Day(s) ], [ Hour(s) ], [ Minute(s) ], [ Second(s) ]) AS [This is how long…]
FROM getData;

What this procedure does is getting the birthdate as it happened, adding 18 years because that’s the target. The select then calculates the differences based on on the modulo function (the % sign). As I’m converting to seconds, I can work my way down from years to seconds by changing the modulo.

I’ve used this technique in some customer cases as well to determine if a certain record had expired its valid date or not.

The second one is more work related but fun nonetheless. It’s one I didn’t think of myself but it was heavily inspired on the work from Brent Ozar. I’m a great believer in attribution, and as this is mostly his work, check out the link to get a quick working setup and adjust it to your needs.


The reason for this script came from a customer who wanted to know if all databases were up and running if a server went into a failover, reboot or whatever. We discussed the issue shortly and, having paid attention in classes of Brent, I came up with a procedure that runs after startup and checks the state of all the databases. If all the databases are online and running, it will send an email stating everything is OK. If one of the databases didn’t get to the normal state, the email will have a line for each database with the state it was in when the procedure ran. Of course, this isn’t watertight and fails if either the mailserver is down or the server never returns to normal running, but that is being monitored elsewhere.

Now this script has been running for years and just one simple ‘out of order’ message has been seen: Database Offline. Every other database has resumed without hesitation or error. Yes, some database servers are just summer children.

So without further ado, time to hit your keyboard and write about your funny scripts, code.

T-SQL Tuesday #156 – Production Code

Invitation from Tom Zika.

I’m a learner by example, so when I started programming (not so long ago), I tried to find existing solutions on various Q&A sites or blogs, as one might.

After a while, I noticed one sentence repeating often enough that it stuck with me:

“This is not a production-grade code”.

So here’s my invitation: “Which quality makes code production grade?”

You might think: “Production code is code that runs in production, duh.”

But let’s help out the newbies who look for a bit of concrete guidance.
Please be as specific as possible with your examples and include your reasoning.

I’m not limiting the scope to just the SQL; it can be anything.

T-SQL Tuesday #155 – The Dynamic Code Invitation

Invitation from Steve Jones.

I saw a post recently where someone noted they used Excel to help build dynamic SQL for their job. I thought that was a) creative, and b) similar to something I’ve done. In fact, that will be my post for this month.

However, while many of the experts decry dynamic SQL as a poor way of solving problems, it is not going away. In fact, it works really well for many situations and problems, albeit not necessarily a high volumes of data. There also are security concerns.

My invitation this month is to write about producing SQL dynamically in some way. Let us know about any of these things:

  • a problem you solved
  • a creative use of technology to build SQL
  • security concerns
  • a place where dynamic SQL failed you
  • a way to convert dynamic SQL to something cleaner
  • anything else that relates to code producing code

T-SQL Tuesday #151 – Coding Standards

Invitation and roundup from Mala Mahadevan.

My invite is about coding standards, or what I now call Linting Rules, for T-SQL. What are the T-SQL coding rules that are appropriate for where you work and what you do? If there are exceptions to those rules, state them too! If this is enough, read the blog party rules below and get started!!

  • Your post must be published on Tuesday June 14, 2022.
  • Your post must contain the T-SQL Tuesday logo (see above) and the image must link back to this blog post.
  • Trackbacks should work, but if not please put a link to your post in the comments section so everyone can see your contribution! (My comments are moderated so please don’t worry if yours doesn’t appear right away, I will make sure it does!)
  • If you are on twitter include the hash tag #tsql2sday – it helps with RT-s and visibility!!

More on why I picked this topic as below –

When I started out as a DBA two decades ago, I had a list of rules that I would carry with me into every job I went..these are things I look for in T-SQL code and try to enforce as standard. Some examples were casing rules, minimized usage of SELECT STAR, equating the right data types in columns, avoiding NOLOCK hint and so on. Standards ensure quality and consistency in code.

Standards differ for each firm, depending on what is appropriate for an environment..it is even possible to have varying standards in the same company, depending on the environment and what is appropriate for a database. This is an excellent article on what are the different components that comprise coding standards, and why we need them. I am also a big proponent of automated code checking for standards – there are lots of tools available for doing this – SQL Prompt, which is a personal favorite of mine, and many others as listed here.

Several tools currently do linting on many relational platforms, not just SQL Server. Almost all of them though, have rules that the author(s) think are best for the worlds they work in, and do not include other conditions which they have not encountered yet. A common example I like to use is unnamed primary keys on temporary tables. There is nothing inherently wrong with having an inline primary key constraint/index on a temporary table – but if you use Query Store, plan forcing on a plan that uses this temp table will not work simply because the constraint gets named differently each time. When I started to look for a linting tool for where I work – I ran into so many rules that were non-existent or not applicable to my environment with outside tools that I decided to write my own using ScriptDOM – a Microsoft-provided library that was created specifically for this purpose.

It would help greatly if we had a collection of rules that people use to pick from and enforce as appropriate for their environments. It will also help me to code some of these into ScriptDOM and put it out on GitHub, if the rule is one that ScriptDOM can find easily. So, re-stating the call for this month – What are the T-SQL coding rules that are appropriate for where you work and what you do? If there are exceptions to those rules, state them too!

T-SQL Tuesday #143 – Short code examples

Invitation and wrap-up from John McCormack.

T-SQL Tuesday this month is going back to basics and its all about code. I’d like to know “What are your go to handy short scripts”?

What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.

e.g. I manage a lot of SQL agent jobs. Quite often, I need to find out which job has a certain t-sql string in the command so I’ll run:

SELECT * from msdb..sysjobs sj 
JOIN msdb..sysjobsteps sjs 
on sj.job_id = sjs.job_id 
where sjs.command like 'backup log%'

Of course, there are many other ways to find this out including DBATools commands but sometime I just revert to memory for convenience.

Another one I like is to get the estimated completion rate of a backup or restore. Now there are better scripts than this but sometimes, nothing beats getting a quick estimation back from a couple of lines of memorised t-sql.

SELECT percent_complete pc,*
FROM sys.dm_exec_requests
order by pc desc

My invitation to you for this month’s #tsql2sday is…

I would like you to share with the community what your go to script snippets are and why you find them useful. By sharing these, you will undoubtedly be helping someone who hasn’t thought of doing it that way, and hopefully you’ll pick up some handy hints as well.

  • Any language is fine, not just t-sql
  • Please share as many as you wish
  • Perhaps you never do this and always work off saved scripts or convert your snippets to stored procedures? Tell us why this works for you.

T-SQL Tuesday #136 – Blog About Your Favorite Data Type (Or Least Favorite)

Invitation and wrap-up from Brent Ozar.

Your mission: write a blog post about your favorite data type, and schedule it for next Tuesday, March 9.

Mad about money? Feverish about float? Tell us what you use your favorite data type for, and what people need to know before they get started using it. Data types have so many interesting edge cases, like when Aaron Bertrand taught us to use lower case for our data types. (I still don’t do that, but for the record, I do feel guilty.)

T-SQL Tuesday #118 – Your Fantasy SQL Feature

Invitation and roundup from Kevin Chant.

Recently I’ve had to submit suggestions to Microsoft about Azure DevOps and SQL Server.

I will admit a couple of the suggestions had certainly been in my head for a while. In fact, I wish I had suggested them sooner.

Because of this reason I have chosen this topic for this month’s T-SQL Tuesday. For those of you who are new to T-SQL Tuesday you can read more about it in detail here.

My invitation to yourselves this month is to write a post about a fantasy SQL Server feature you’ve got in mind.

It can be absolutely anything at all related to SQL Server. For example, it could be about a new SQL Server operator to improve queries or a new service that does something amazing.

So, you can stretch your imagination as far as you want to. In fact, I actively encourage it.

Of course, if you have already submitted a suggestion to Microsoft about something that you are particularly keen on than you are welcome to post about that instead.

However, one piece of advice if you do post about a suggestion you have submitted already. I recommend a link to your suggestion as well for all to see.

Something else you might be tempted to do is submit your suggestion about SQL Server to Microsoft before you publish your post.

Again, something else which I encourage you to do if you have something in mind. In fact, I’ll even provide the link to the SQL Server suggestion site here.