Software Vendor Mistakes With SQL Server: Selecting Everything From Everywhere

Posted on by Erik Darling

Proble-Matic

It seems that most developers are either too naive or too lazy to choose just the columns that they need to satisfy a particular requirement, and so their ORM queries end up doing the equivalent of SELECT *from every table involved in the query.

I have seen some worthwhile uses for this, where people would select all the data that they needed to only make one round trip to the server rather than 5 or more. In those cases, it’s okay to break the rule, especially if the query is a Heavy Hitter™.

But this can end up causing all sorts of problems with queries. Back in the Oldenn Dayes of the internet, you’d find people talking about the overhead of metadata lookups and blah blah.

I’m sure there’s some tiny amount, but that’s hardly worth talking about in light of the other, more serious issues that you’ll encounter.

Memory Grants

Memory grants come into play most commonly when you need to Sort or Hash data in a query plan. There are some other cases, like Optimized Nested Loops, and columnstore inserts, but those are somewhat less common.

Memory grants can get really thrown off by a couple things: cardinality misestimations, and string columns. Right now, the optimizer makes a guess that every row in the column is “half full”. That means whatever the byte length of your column is.

For instance:

  • varchar(100): 50 bytes
  • nvarchar(100): 100 bytes

The wider your columns get, the bigger your memory grants get. That becomes a bigger deal on servers that are starving for memory, because those grants can really eat up a lot of memory that you need for other stuff, like caching data or query plans.

Some Columns Are Bigger Than Others

These queries will all request additional memory from SQL Server, because we’re asking it to return dated sorted by Reputation, and we don’t have any useful indexes that put the Reputation column in order. That means we need some scratch space to do the work in.

SELECT TOP (1000) 
    u.DisplayName 
FROM dbo.Users AS u 
ORDER BY 
    u.Reputation;

SELECT TOP (1000)
    u.DisplayName,
    u.Location
FROM dbo.Users AS u
ORDER BY
    u.Reputation;

SELECT TOP (1000)
    u.DisplayName,
    u.Location,
    u.WebsiteUrl
FROM dbo.Users AS u
ORDER BY
    u.Reputation;

SELECT TOP (1000)
    u.DisplayName,
    u.Location,
    u.WebsiteUrl,
    u.AboutMe
FROM dbo.Users AS u
ORDER BY
    u.Reputation;

The query plans for these are unremarkable. They all have a Sort operator in them used to order our data, but the memory grants for all of these go up, up, up.

SQL Server Query Plan Tool Tips
sunshine

We go from 284MB, to 584MB, to 1178MB, to 13GB. And worse, because of the 50% guess for column fullness, we only end up using 369MB of that 13GB.

Good luck with that.

Newer versions of SQL Server attempt to help you with this by adjusting memory grants between executions, but there are a bunch of caveats.

Lifetime

There are other problems that can occur with queries like these, but I’m going to cover them in other parts of the series because they tie into other concepts like indexing and parameter sniffing that I need to lay some foundation for first.

The memory grant thing is bad enough on its own, but it may not become a real issue until your databases reach a size where they outpace RAM by a significant margin.

That’s usually when you start seeing significant signs of memory pressure, which you can use my sp_PressureDetector stored procedure to help detect.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts