SQL Server Archives | Page 157 of 164

What Do You Wish SQL Server Was Better At?

Posted on February 26, 2019May 14, 2022 by Erik Darling

Two Words

When I think about things that I see causing people performance pains in SQL Server, Microsoft has been doing a good job of starting to go after them.

If you asked me in 2015 what Microsoft should do, I’d have said…

Well, I posted about it a couple times.

Some of these have snuck into the product, and I think some others might start to make surprise appearances.

If you look at what a lot of people have been blogging, presenting, and getting rich off of over the years, it’s not hard to track down common issues.

Scalar Functions
Multi-Statement Table Valued Functions
Table Variables
Non-SARGable predicates
Parameter Sniffing
Optional parameters

A whole bunch of stuff on that list has been addressed. Scalar functions have been froided, and table variables deferred for 2019, MSTVFs were interleaved in 2017, and to some extent parameter sniffing got a stern talking to via adaptive joins and memory grant feedback. There may not be a good way to address SARGability, but hey, consultants have to make money fixing something.

Mercedes Bends

What’s next? Well, a couple white papers from Microsoft have come out sort of recently that point to parameter sniffing getting some more attention.

If you don’t feel like reading all that, just imagine SQL Server starting to cache more than one version of a plan, and using different parts from different plans depending on what parameters are passed in. Sort of like a choose your own adventure game for query plans. But hopefully with fewer Discipline Crabs.

Yeah. That’s hard as hell.

I don’t expect to see it in SQL Server next year, but when I see multiple research papers about the same subject, I start to get hopeful. And even when it shows up, you can bet your best drawers it’s gonna be a rocky road to getting it right.

You Can’t Forget

There might be people out there who depend on current behavior. Every fix might be a breaking change for someone. That’s why nearly every feature has a trace flag to turn it off.

There can be cases where the wrong plan is the fastest plan. Heck, plan costing is bizarre. I can’t imagine how hard it would be to tinker with optimizer inner workings. Like, at all. But the way some costs are estimated these days doesn’t make a ton of sense. It’s like if you still estimated how long it would take you to get somewhere based on the average ground speed of a horse. No offense to the equestrians among us, but no one’s riding a horse from NYC to Seattle.

But What About You?

What do you wish SQL Server did better? It doesn’t have to be performance related. Just anything that you run into that makes your day harder.

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.

My SQLBits Schedule and Precon Trailer!

Posted on February 25, 2019May 14, 2022 by Erik Darling

HELLO MANCHESTER!

If you’re in town for SQLBits this week, come say hello to me. I have stickers.

Now that’s a sticker!
cc: @stickermule pic.twitter.com/OBWsg5tKIq

— Erik Darling Data (@erikdarlingdata) February 7, 2019

I’m running a precon on Thursday, and I have a regular session on indexes Friday.

The session Friday is attached to a fundraiser, and I’d be ever so grateful if you’d consider donating.

Trailer Park

Here’s the trailer for my precon. I rather like it. Hopefully you do, too.

Thanks for reading!

Going Further

Implicit vs Explicit Spools In SQL Server Query Plans

Posted on February 22, 2019May 14, 2022 by Erik Darling

Spools Of A Feather

There are plenty of spools in query plans, and they’re all pretty well labeled.

Index
Table
Rowcount
Window

They can be either eager or lazy.

An eager spool will take all the rows at once and cache them, and a lazy spool will only go get rows as needed.

But what else can act like a spool?

Phases On

In general, a blocking operator, or as my upside down friend Paul calls them, “phase separators” can act as a spool.

A spool, after all, is just something that keeps track of some rows, which is exactly what a Sort or a Hash do.

They keep track of rows that arrive, and either sort them according to a need, or create a hash table of the value.

While either of these happen, any downstream work in the query have to wait for them to complete. This is why they’re called blocking, stop and go, or, more eloquently, phase separators.

Eager spools have the same basic feature: wait for all the rows from downstream to arrive, and perform an action (or just feed them to another operator).

Here’s an example of a Sort acting as a spool:

DROP TABLE IF EXISTS #waypops;

CREATE TABLE #waypops
(
  UserId INT
  , PRIMARY KEY NONCLUSTERED (UserId) WITH (IGNORE_DUP_KEY = ON)
);

INSERT #waypops WITH(TABLOCKX) 
       ( UserId)
SELECT b.UserId
FROM dbo.Badges AS b
WHERE b.Name = N'Popular Question';

A SQL Server query plan — Enjoy the silence

The Sort is in the same order as the index it’s reading from, but just reading from the index wouldn’t provide any separation.

Just Passing By

This is weird, niche stuff. That’s why I’m posting it on a Friday. That, and I wanna bully someone into writing about using a hash join to do the same thing.

Thanks for reading!

Going Further

Join Me For Free SQL Server Q&A Every Friday At Noon!

Posted on February 21, 2019May 14, 2022 by Erik Darling

Q&A4U

Every Friday at Noon EST, I answer your SQL Server questions live on YouTube.

I understand, not everyone can make it, or YouTube might be blocked at work.

If it is, you can ask via email, but keep in mind I’m going to answer during the webcast, not with a personal response.

If you like Twitter better, drop me a line there.

See you there!

Going Further

How SQL Server Can Handle Complex Query Predicates (Sometimes)

Posted on February 20, 2019May 14, 2022 by Erik Darling

Optimizer Optimizes

Sometimes, the optimizer can take a query with a complex where clause, and turn it into two queries.

This only happens up to a certain point in complexity, and only if you have really specific indexes to allow these kinds of plan choices.

Here’s a haphazard query:

SELECT COUNT(*) AS records
FROM dbo.Posts AS p
WHERE ( 
           p.PostTypeId = 1
       AND p.AcceptedAnswerId <> 0
       AND p.CommentCount > 5
       AND p.CommunityOwnedDate IS NULL
       AND p.FavoriteCount > 0
      )
OR   (
          p.PostTypeId = 2
      AND p.CommentCount > 1
      AND p.LastEditDate IS NULL
      AND p.Score > 5
      AND p.ParentId = 0
     )
AND (p.ClosedDate IS NULL);

There’s a [bunch of predicates], an OR, then a [bunch of predicates]. Since there’s some shared spaced, we can create an okay general index.

It’s pretty wide, and it may not be the kind of index I’d normally create, unless I really had to.

CREATE INDEX whatever 
    ON dbo.Posts (PostTypeId, CommentCount, ParentId)
         INCLUDE(AcceptedAnswerId, FavoriteCount, LastEditDate, Score, ClosedDate, CommunityOwnedDate);

It covers every column we’re using. It’s a lot. But I had to do it to show you this.

The optimizer took each separate group of predicates, and turned it into a separate index access, with a union operator.

It’s like if you wrote two count queries, and then counted the results of both.

But With A Twist

Let’s tweak the where clause a little bit.

SELECT COUNT(*) AS records
FROM dbo.Posts AS p
WHERE ( 
           p.PostTypeId = 1
       AND p.AcceptedAnswerId <> 0
       AND p.CommentCount > 5
       OR p.CommunityOwnedDate IS NULL --This is an OR now
       AND p.FavoriteCount > 0
      )
OR   (
          p.PostTypeId = 2
      AND p.CommentCount > 1
      AND p.LastEditDate IS NULL
      OR p.Score > 5 -- This is an OR now
      AND p.ParentId = 0
     )
AND (p.ClosedDate IS NULL)

We don’t get the two seeks anymore. We get one big scan.

Is One Better?

The two seek plan has this profile:

Table 'Posts'. Scan count 10, logical reads 30678
Table 'Worktable'. Scan count 0, logical reads 0
Table 'Workfile'. Scan count 0, logical reads 0

 SQL Server Execution Times:
   CPU time = 439 ms,  elapsed time = 108 ms.

Here’s the scan plan profile:

Table 'Posts'. Scan count 5, logical reads 127472

 SQL Server Execution Times:
   CPU time = 4624 ms,  elapsed time = 1617 ms.

In this case, the index union optimization works in our favor.

We can push the optimizer towards a plan like that by breaking up complicated where clauses.

SELECT COUNT(*)
FROM (
SELECT 1 AS x
FROM dbo.Posts AS p
WHERE ( 
           p.PostTypeId = 1
       AND p.AcceptedAnswerId <> 0
       AND p.CommentCount > 5
       AND p.CommunityOwnedDate IS NULL
       AND p.FavoriteCount > 0
      )  

UNION ALL

SELECT 1 AS x
FROM dbo.Posts AS p   
WHERE (
          p.PostTypeId = 2
      AND p.CommentCount > 1
      AND p.LastEditDate IS NULL
      AND p.Score > 5
      AND p.ParentId = 0
     )
AND (p.ClosedDate IS NULL)
) AS x

Et voila!

Which has this profile:

Table 'Posts'. Scan count 2, logical reads 30001

 SQL Server Execution Times:
   CPU time = 329 ms,  elapsed time = 329 ms.

Beat My Guest

The optimizer is full of all sorts of cool tricks.

The better your indexes are, and the more clearly you write your queries, the more of those tricks you might see it start using

Thanks for reading!

Going Further

How Well Do You Know Your SQL Servers?

Posted on February 19, 2019May 14, 2022 by Erik Darling

This Is Not An Exit Interview

Talking to lots of different types of people about SQL Server gives you a pretty good idea about what you’ll find on their SQL Servers

Stuff they didn’t know they should change
Stuff they knew they should change
Stuff they thought they changed
Stuff they changed, but then…

The last one is tough to sort out. Most likely, lots of people have enough access to make changes.

Ditto in those environments, there’s no change management, no monitoring (or at least none that tracks settings changes), and it’s not like SQL Server is set up out of the box to notify you when someone changes something.

Here are some examples:

Someone turned off the CHECKDB job
Someone changed the log backup schedule to not run at night
Someone changed a parallelism setting
Someone changes fill factor

Questions For You

Do you monitor these things?
Does your monitoring tool monitor these things?
Do you have a favorite tool to check these things?

If you don’t, and you like PowerShell, check out Rob Sewell’s Pester checks. If you prefer T-SQL, there’s always sp_Blitz.

If you’re totally lost about what to do, drop me a line. These are the kinds of things I love to help people with.

Thanks for reading!

Going Further

Does sp_executesql WITH RECOMPILE Actually Recompile Query Plans In SQL Server?

Posted on February 18, 2019May 14, 2022 by Erik Darling

No, No It Doesn’t

But it’s fun to prove this stuff out.

Let’s take this index, and these queries.

CREATE INDEX ix_fraud ON dbo.Votes ( CreationDate );

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= '20101230';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= '20101231';

What a difference a day makes to a query plan!

Hard To Digest

Let’s paramaterize that!

DECLARE @creation_date DATETIME = '20101231';
DECLARE @sql NVARCHAR(MAX) = N''

SET @sql = @sql + N'
SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= @i_creation_date;
'

EXEC sys.sp_executesql @sql, 
                       N'@i_creation_date DATETIME', 
                       @i_creation_date = @creation_date;

This’ll give us the key lookup plan you see above. If I re-run the query and use the 2010-12-30 date, we’ll re-use the key lookup plan.

That’s an example of how parameters are sniffed.

Sometimes, that’s not a good thing. Like, if I passed in 2008-12-30, we probably wouldn’t like a lookup too much.

One common “solution” to parameter sniffing is to tack a recompile hint somewhere.

Recently, I saw someone use it like this:

DECLARE @creation_date DATETIME = '20101230';
DECLARE @sql NVARCHAR(MAX) = N''

SET @sql = @sql + N'
SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= @i_creation_date;
'

EXEC sys.sp_executesql @sql, 
                       N'@i_creation_date DATETIME', 
                       @i_creation_date = @creation_date
                       WITH RECOMPILE;

Which… gives us the same plan. That doesn’t recompile the query that sp_executesql runs.

You can only do that by adding OPTION(RECOMPILE) to the query, like this:

SET @sql = @sql + N'
SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= @i_creation_date
OPTION(RECOMPILE);
'

A Dog Is A Cat

Chalk this one up to “maybe it wasn’t parameter sniffing” in the first place.

I don’t usually advocate for jumping right to recompile, mostly because it wipes the forensic trail from the plan cache.

There are some other potential issues, like plan compilation overhead, and there have been bugs around it in the past.

Thanks for reading!

Going Further

Come See Me In Boston On May 10th!

Posted on February 15, 2019May 14, 2022 by Erik Darling

So Where In Boston Are You From? Weymouth?

I’ll be presenting for NESQL at the User Group on the 9th, and for a full day of training on May 10th, delivering material from my sold out SQLBits session.

This isn’t your typical training session with the usual suspects causing performance problems.

We’ll be looking at the horrible things that happen to queries when servers are overloaded, and solving some really tough query problems that no amount of hardware will fix.

If you sign up early, it’s only $150. Prices will go up by $50 soon, and I’d much rather see you spend that on a bottle of water at Fenway.

See you there!