Category: T-SQL

Years ago someone said “Hey – why not drop auto-created stats, since the stats you need will just get created again and you’ll end up getting rid of those you no longer need.” That *may* be a reasonable step on some systems. If the risk of bad plans on first execution of a query needed stats that have been dropped is too high, its a bad deal. If the potential concurrent cost of auto-creating dropped stats is too high, that’s a bad deal. What about analyzing query plans over some period of time to see which stats are actually used in those plans? Then auto-stats which aren’t used in that set of plans could be dropped.
That type of stats analysis could have other uses, too. Prioritizing stats manual stats updates in regular maintenance comes to mind. Or, determining what stats to create/update on an Always On Availability Group primary based on secondary activity. And troubleshooting problem queries or identifying suspicious “watchlist” stats based on highly variable queries/plans they are involved with.
So I created this blog post almost 4 years ago. And now I’ll plead with you to not use the query there… it’s awful. If you want to query trace flag 8666 style stats from plan XML, please start from the query in this post instead – its much more well behaved 🙂

Read on for the script.

Comments closed

Updating SQL Agent Job Owners With dbatools

Published 2018-06-29 by Kevin Feasel

Stuart Moore gives us two methods of updating SQL Agent job owners, one using T-SQL and the other with dbatools:

Now we all know that having SQL Server Agent jobs owned by ‘Real’ users isn’t a good idea. But I don’t keep that close an eye on some of our test instances, so wasn’t surprised when I spotted this showing up in the monitoring:
The job failed. Unable to determine if the owner (OldDeveloper) of job important_server_job has server access (reason: Could not obtain information about Windows NT group/user 'OldDeveloper', error code 0x534. [SQLSTATE 42000] (Error 15404)).
Wanting to fix this as quickly and simply as possible I just wanted to bulk move them to our job owning account (let’s use the imaginative name of ‘JobOwner’).

Click through for both scripts.

Comments closed

Performance Tuning Window Functions

Published 2018-06-29 by Kevin Feasel

Kathi Kellenberger gives us some hints on tuning queries using window functions:

The only way to overcome the performance impact of sorting is to create an index specifically for the OVER clause. In his book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions, Itzik Ben-Gan recommends the POC index. POC stands for (P)ARTITION BY, (O)RDER BY, and (c)overing. He recommends adding any columns used for filtering before the PARTITION BY and ORDER BY columns in the key. Then add any additional columns needed to create a covering index as included columns. Just like anything else, you will need to test to see how such an index impacts your query and overall workload. Of course, you cannot add an index for every query that you write, but if the performance of a particular query that uses a window function is important, you can try out this advice.

There are some good insights here.

Comments closed

Multiple SYSDATETIME In The Same SELECT May Give Unexpected Results

Published 2018-06-20 by Kevin Feasel

Louis Davidson walks through a scenario he experienced:

The data is exactly as expected, even though the other two calls would have returned .902 and .903 if simply rounded off. On the other hand, looking for differences between the time1_3 and time2_3 columns:
SELECT *
FROM #hold
WHERE DATEDIFF(MILLISECOND,time1_3,time2_3) <> –1;
Returns 133 rows. With the sysdatetime values being exactly the same:
sysdatetime1                sysdatetime2
————————— —————————
2018–06–18 20:53:21.0443399 2018–06–18 20:53:21.0443399
2018–06–18 20:53:21.0493653 2018–06–18 20:53:21.0493653
But the other columns, are incorrect for our needs, as the values are the same:
time1_3                     time2_3
————————— —————————
2018–06–18 20:53:21.045     2018–06–18 20:53:21.045
2018–06–18 20:53:21.050     2018–06–18 20:53:21.050
This was the bug we experienced! Instead of being 1 millisecond different, the values were equal.

Louis’s moral to the story is to test your assumptions. The more prosaic moral is that calls to get the current time take a non-zero amount of time.

Comments closed

Randomization With NEWID()

Published 2018-06-18 by Kevin Feasel

Michael J. Swart tests whether ORDER BY NEWID() produces a biased result:

One of his articles, Visualizing Algorithms has some thoughts on shuffling at https://bost.ocks.org/mike/algorithms/#shuffling.
He says that sorting using a random comparator is a rotten way to shuffle things. Not only is it inefficient, but the resulting shuffle is really really biased. He goes on to visualize that bias (again, I really encourage you to go see his stuff).
Ordering by random reminded me of the common technique in SQL Server of ORDER BY newid(). So I wondered whether an obvious bias was present there. So I shuffled 100 items thousands of times and recreated the visualization of bias in a heat map (just like Mike did).

I’ve used this pattern to good effect, but definitely pay attention to Michael’s warning at the end.

Comments closed

Using RAISERROR Instead Of PRINT

Published 2018-06-07 by Kevin Feasel

Randolph West recommends using RAISERROR WITH NOWAIT rather than PRINT for printing messages:

Read that last line again. It’s saying that the PRINT command needs to fill some sort of memory buffer before you see any output. Sometimes you’re lucky, and sometimes you’re not. Erland Sommarskog says the same thing in the series Error and Transaction Handling in SQL Server (Part 1: Jumpstart Error Handling | Part 2: Commands and Mechanisms | Part 3: Implementation).
If you want immediate output from your long-running process, use RAISERROR instead of PRINT, and make use of the WITH NOWAIT parameter.
Naturally I can’t leave you hanging with a 150-word blog post, most of which is quoting Erik, so I decided to do some digging. The question I posed myself was “How big is the PRINT buffer?”

I always use error level 10, as that’s the minimum level guaranteed to print to the console immediately and it does not write to error logs or stop operations.

Comments closed

There’s Only One Way To Order

Published 2018-05-31 by Kevin Feasel

Matthew McGiffen notes that there is only one way to order, and that is to use the ORDER BY clause:

Everyone, at the beginning of their SQL career, get’s told that it is important to include an ORDER BY if they want the results ordered. Otherwise the order in which they are returned is not guaranteed.
But then you run queries a lot of times that don’t need a specific order – and you see that they (at least seem to) come out in the same order every time. You could (almost) be forgiven for thinking you can rely on that.
There was even a question on a Microsoft SQL certification exam a few years ago that asked what the default order was for records returned by a simple SELECT – the answer it was looking for was that it would be according to the order of the clustered index. So you can rely on that – right?
Wrong. The question was a bad question, and the answer was incorrect. Let’s look at this in action.

Order is never guaranteed to be stable unless you specify a unique ordering using ORDER BY.

Comments closed

Grouping By Nothing In SQL

Published 2018-05-30 by Kevin Feasel

Lukas Eder points out a subtlety of the GROUP BY clause:

SELECT count(*)
FROM film
GROUP BY ()
This will yield:
count |
------|
1000 |
What’s the point, you’re asking? Can’t we just omit the GROUP BY clause? Of course, this will yield the same result:
SELECT count(*)
FROM film
Yet, the two versions of the query are subtly different.

Great post and also shows a case when GROUP BY () isn’t supported.

Comments closed

Tic-Tac-Toe In T-SQL

Published 2018-05-29 by Kevin Feasel

Riley Major implements Tic-Tac-Toe in T-SQL:

It turns out there’s a concept called bitmasking which can work a lot like this cardboard cut-out process. (Props to Dylan Beattie for his quick visual demonstration at NDC Minnesota which drove this point home.) First, you represent your game state with a bunch of bits (“OXOOOXXXX” yields “0100011110” for our example above, remembering that we’re padding that last 0 just to make the powers 1-based instead of 0-based) and then you represent your winning state with a bunch of bits (“0000001110” for our example winning state here). Now you use the magic of “bitwise math” to compare the two.
For our use, we want to find out whether our mask exposes the winning three bits. We want to block everything else out. With bits, to check if both items are true, you use “AND” (0 and 0 is 0; 0 and 1 is 0; 1 and 1 is 1). If we apply that “AND” concept to each bit in our game, it will squash out any values which don’t match. If what we have left matches the mask (fills in all of the space we can see through), then we have a match and a win.

The twist in all of this is that the end result doesn’t quite work as expected, but it was interesting watching the process. That said, there’s a good reason why we don’t use T-SQL as a primary language for development…

Comments closed

Finding Procedure Parameters Which Don’t Match Column Names

Published 2018-05-29 by Kevin Feasel

Shane O’Neill has a process to update procedures to make input parameter names match output column names:

I was asked to standardise stored procedures we use for common support cases before we hand them over to IT Helpdesk.
One of the comments that came back from the Helpdesk while testing was that the parameter names that they had to put values in for didn’t match what they saw in the application.
Luckily for me (or unluckily) the application was a third party developed one and they didn’t bother renaming the columns. So if the column is called create_date in the database then the application is going to show create_date.
However, if I created a parameter called DateCreated or even CreateDate, then they don’t want it.

Shane has a Powershell script which uses the Find-DbaStoredProcedure method in dbatools; click through to see it in action.

Comments closed