Press "Enter" to skip to content

Category: T-SQL

Job Threading and Thread Partitioning in SQL Server

Published 2024-05-16 by Kevin Feasel

Aaron Bertrand continues a series on threading:

In part 2 of this series, I showed an example implementation of distributing a long-running workload in parallel, in order to finish faster. In reality, though, this involves more than just restoring databases. And I have significant skew to deal with: one database that is many times larger than all the rest and has a higher growth rate. So, even though I had spread out my 9-hour job with 400 databases to run faster by having four threads with 100 databases each, one of the threads still took 5 hours, while the others all finished within 1.5 hours.

Read on to learn what Aaron did to make things move faster.

Comments closed

The Joy of Partitioned Views

Published 2024-05-16 by Kevin Feasel

Rod Edwards talks partitioned views:

This post came around when I was at a loose end one evening, and just started poking at a local sandpit database, and it got me reminiscing and revisiting / testing a few things. The devil makes work for idle thumbs and all that…

Partitioned Views…do they have a place in society anymore?

Rod does a great job of following Betteridge’s Law of Headlines, as well as saving the ‘Yes’ answer for the post itself. Partitioned views come with their own pains, though one use case Rod did not include is using PolyBase and partitioned views to move “cold” data to slower external storage.

Comments closed

Dynamic Unpivoting of Columns in T-SQL

Published 2024-05-13 by Kevin Feasel

Kristyna Ferris does a bit of twisting:

Picture this, your data ingestion team has created a table that has the sales for each month year split into different columns. At first glance, you may think “what’s the big deal? Should be pretty easy, right? All I need to do is unpivot these columns in Power BI and I’m good to go.” So you go that route, and the report works for one month. Next month, you get an urgent email from your stakeholders saying they can’t see this month’s numbers. That’s when you realize that this table will grow with new columns every month. That means that any report you make needs a schema refresh every single month. Unfortunately, Power BI will not grab new columns from a table once it’s published into the online service. The only way for the Power Query to pivot the new columns is for you to open the report in your desktop, go to Power Query, and refresh the preview to get all the columns in that table.

Which is quite the pain. But Kristyna has a solution using the UNPIVOT operator in T-SQL.

Comments closed

The NOT Keyword in SQL Server

Published 2024-05-08 by Kevin Feasel

Kevin Wilkie gets tied up in NOTs:

Today, I want to talk about the keyword NOT in SQL Server. It can be your friend or your worst enemy depending upon how you use it. Let’s delve into some examples of what I’m talking about and how it’s easy for people to mess it up.

Look, it’s not that I’m not saying you shouldn’t avoid not using NOT here. It’s just that it’s really hard to get the coveted quintuple-negative in natural speech.

Comments closed

Object Dependencies and Referencing Entities

Published 2024-05-08 by Kevin Feasel

Andy Brownsword takes another look:

Last week we looked at identifying object dependencies through performing a wildcard search on the objects such as procedures. I noted another option could be to use the views such as sys.sql_expression_dependencies and Kevin also commented about using the supporting functions such as sys.dm_sql_referencing_entities.

I wanted to briefly look at both of these options and look at how their results can differ. 

Linkception graf achieved.

Comments closed

SQL Server FAST N Query Hint

Published 2024-05-06 by Kevin Feasel

Chad Callihan hits the Turbo button:

How familiar are you with the OPTION (FAST N) query hint? It’s not one that I had ever used, so I decided to experiment with it a bit. Let’s look at what it does and how to apply it to a query.

My recollection is that this query hint highly prioritizes “streaming” operators and de-prioritizes operators that work on the whole dataset as a block, such as sorts. This means, for example, that you’ll get more nested loops joins and natural merge joins, but few sort + merge or hash match joins. It may also make some queries take considerably longer as a result.

Comments closed

Finding Object Dependencies in SQL Server

Published 2024-05-01 by Kevin Feasel

Andy Brownsword looks for references:

When looking to migrate, consolidate or deprovision parts of a SQL solution it’s key to understand the dependencies on the objects inside.

Identifying dependencies can be challenging and I wanted to demonstrate one way to approach this. We’ll start with some objects across a couple of databases:

Read on for a pair of queries that get you on the way. Reference detection is surprisingly difficult in SQL Server, especially if you have cross-server queries. Even cross-database queries may not work the way you expect.

Another option is to use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities. I wrote a blog post on the topic a long while back and included some of the caveats around these two DMFs.

1 Comment

Writing Conditional JOIN and WHERE Clauses

Published 2024-04-26 by Kevin Feasel

Erik Darling has Blondie stuck in my head:

The OR operator is a perfectly valid one to use in SQL statements. If you use an IN clause, there’s a reasonable chance that the optimizer will convert it to a series of OR statements.

For example, IN(1, 2, 3) could end up being = 1 OR = 2 OR = 3 without you doing a darn thing. Optimizers are funny like that. Funny little bunnies.

The problem generally isn’t when asking for IN or OR for a single column, with a list of literal values, the problem is usually when you:

  • Use OR across multiple where clause columns
  • Use OR in a join clause of any variety
  • Use OR to handle NULL parameters or variables

This is an excellent, detailed article and well worth the read.

Comments closed

Troubleshooting sp_getapplock Blocking

Published 2024-04-25 by Kevin Feasel

Brent Ozar has an epiphany:

I’ll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense:

  • sp_BlitzFirst @SinceStartup = 1 showed very clearly that their top wait, by a long shot, was blocking. Hundreds of hours of it in a week.
  • sp_BlitzIndex showed the “Aggressive Indexes” warning on a single table, but… only tens of minutes of locking, nowhere near the level the database was seeing overall.
  • sp_BlitzCache @SortOrder = ‘duration’ showed a couple queries with the “Long Running, Low CPU” warning, and they did indeed have blocking involved, but … pretty minor stuff. Plus, their plan cache was nearly useless due to a ton of unparameterized queries pouring through constantly, overwhelming Azure SQL DB’s limited plan cache.
  • sp_Blitz wasn’t reporting any deadlocks, either. (sp_BlitzLock doesn’t work in Azure SQL DB at the moment because Microsoft’s no longer running the default system health XE session up there. They turned that off in order to save money on hosting costs, and passed the savings on to… wait… hmm)
  • As a last-ditch hail-Mary, I ran sp_BlitzWho repeatedly, trying to catch the blocking happening in action. No dice – the odds that I’d catch it live weren’t great anyway.

Click through for the story and how all the pieces ultimately fit together.

Comments closed

Searching for a String Position

Published 2024-04-22 by Kevin Feasel

Chad Callihan goes index hunting:

SQL Server has the CHARINDEX() and PATINDEX() functions that can both be used to find the position of a value in a string. They each have some differences that make them unique and more fitting for specific situations.

Let’s look at a few examples of how each can be used.

Read on to see how to use CHARINDEX() and PATINDEX() on two statements that are total falsehoods: Kevin Conroy always will be the number 1 and best Batman.

Comments closed