Query Tuning – Page 61

Even If SQL Server Table Variables Were Always In Memory, It Wouldn’t Make Them Better Than Temp Tables

Posted on August 9, 2022July 22, 2022 by Erik Darling

Past Perfect

You know all those pesky developer myths that never go away?

CTEs and Views can’t use indexes: WRONG
Subqueries are slower than joins: WRONG
Temp tables are bad for performance: WRONG
Table Variables are always in memory: WRONG

Okay, that’s not a complete list, but a complete list would fill the internet to its very brim and cause all sorts of overflow errors.

My job as a performance tuning consultant is to teach folks when they’ve been lead astray.

To confuse things a bit further, Microsoft allows for in-memory @table variables, but you have do a lot of work to get set up to use them.

They’re not the ones you get out of the box.

Future Mediocre

Let’s say for the sake of argument that @table variables were always more “in-memory” than #temp tables (they’re not). They’d still have a lot of the same fundamental problems:

With all that stuff in mind: who cares if they’re only in memory? There are so many downsides for most query patterns, they could all happen in quantum space and performance would still stink.

I know, they have their uses:

But most of y’all aren’t using them that wisely.

And here’s the other thing: Most of y’all don’t have enough memory to begin with, and the memory you do have you misuse in a million ways. Why do you wanna make that worse?

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Some Requests For Authors Of SQL Server Books

Posted on August 8, 2022August 8, 2022 by Erik Darling

Heal Thyself

I dislike criticism without introspection, so anything that I say here I’ll also be doing my best to apply to anything I produce.

But anyway, I’m writing this because I read a lot of SQL Server books, and there are some things about them are pretty frustrating.

This goes for both print and digital copies, though some advice is specific to print copies because I think a lot of folks still buy them.

Anyway, here are some thoughts I have. You can show this post to your editors if you want. Maybe it’s their fault, not yours.

I have no idea.

Code Formatting

While I don’t expect anyone’s coding format to be as highly-evolved to near-perfection as mine, I do prefer consistency.

Many times while reading a book, the code formatting style changes from query to query, page to page, etc.

I understand that with multiple authors, things may change between chapters (but even then, agree to something, would ya?) but it’s jarring to see queries written in several different styles throughout a book.

Most code formatting tools out there will get you at least 70% of the way to code being formatted the way you want, even if you’re particularly persnickety about things.

Code Placement

For the absolute love of Crispin Glover, stop putting code in places where it will span multiple pages.

It sucks for everyone, no matter how they’re reading the book, but all those nice little SQL Server Scrubs who bought (hopefully bought) the digital copy of your book have an awkward time copying and pasting things over to run locally.

For the Print-cesses of the world, we just don’t need that kind of page-turning, cliff-hanging suspense.

I’ve seen this more than anything, and it drives me bonkers every time. Just keep writing and say the code example is on the next page.

If your code example takes up a whole page and then some… Well, look, I don’t have an easy answer for you. You want it there for digital folks to copy, but no one with a print edition is going to write that all out from scratch.

Maybe you should just get yourself a nice GitHub gist and link to longer code examples? Use a URL shortener or GO codes to make memorable URLs?

Either way, this particular issue is in every book I’ve ever read.

Image Quality

We really need to talk about this one.

Now, I get it, you’re probably not gonna talk anyone into printing a full color SQL Server book. At least no one smart.

If you can do that, great. You’re one step closer to your book not looking like a 90’s zine about anarchy or graffiti or whatever.

The next step: use high quality images. Please, please, please, use high quality images.

No one’s going to be able to read your book and follow along without wondering if they have cataracts if they’re staring at washed-out, fuzzy images the whole way through.

Far too often, it looks like someone compressed-to-death and watered down the printer ink for every image in a book.

Tech Review

Get someone you disagree with to tech review your book. Not someone who’s wrong, just someone who has a different point of view.

This isn’t a pitch to get me to do it. Unless I love you to death, I’m probably not gonna wanna read and review your book.

But far too many books seem to suffer from “I got my friend to do this”, which leads to groupthink.

There’s no alternate viewpoint considered or explored, and you end up with one-sided highways of thought.

Doing a tech review shouldn’t be about toeing a line, it should be about making a book more technically accurate.

Concept Introduction

I’m not sure if this is a book or thought organization issue, but… If you’re in an early chapter of a book, say the first three, and you keep seeing things like “we cover this more in chapter 12” or 13 or 14 or some other far off place, you probably shouldn’t be doing it right then and there.

When you’re going to introduce a concept, you should either be covering it in that chapter, or covering it in the next one.

I see this quite a bit, and wonder if I need to skip way ahead in the book and read about this other thing, and then come back.

That’s a crappy, because it makes you feel lost in the material that’s supposed to be bringing you clarity on a subject.

Try to contain yourself, here. Maybe you’re saying too much too soon.

Auto-Errata

It happens to me every time I write a blog post. I re-read it as I’m going through, I re-read it when I’m done, and I tend to check in on things a day or so before it goes live.

When it goes live, I read through the post, and notice half-a-hundred things that I wanna fix, tweak, or expand on.

I can only imagine what that’s like at Bookscale© where you have 300+ pages of blog posts publishing at once.

As you make changes to the book, you should notify folks who bought it. Just randomly posting corrections is hard to keep track of.

GitHub is, again, a decent way to get and track feedback. It also gives you a way to announce large releases to the content so everyone knows when a new version is available.

Hopefully you’re making digital copies available to folks who purchase a physical copy. Otherwise, you’re gonna have a hard time getting a better product in their hands.

Reinventing The Wheel

Rather than take up a whole page with some reheated DMV query, give some love to community scripts out there that do the same thing, but better.

Anyone who knows better is going to pass right over that stuff anyway.

Thanks for reading!

Going Further

SQL Server 2022’s GENERATE_SERIES Doesn’t Suck Anymore

Posted on August 5, 2022August 5, 2022 by Erik Darling

Life Comes At You Fast

The summer intern at Microsoft was hard at work between CTPs. Last time around, there were some serious performance issues with our dear new friend GENERATE_SERIES.

With the release of CTP 2.1, the problems that we saw the first time around are all gone. But there are still a couple small caveats that you should be aware of.

There’s also been a change in the way you call the function, too. You not longer need the START and STOP identifiers in the function.

You just put in whatever numbers you’re into.

That’s probably a good thing.

Caveat #1: Parallelism Restrictions

Loading data into tables that have row store indexes on them can’t go parallel.

DROP TABLE IF EXISTS
    dbo.art_aux;

CREATE TABLE 
    dbo.art_aux
(
    id int NOT NULL PRIMARY KEY CLUSTERED
);

INSERT INTO
    dbo.art_aux WITH(TABLOCK)
(
    id
)
SELECT
    gs.value
FROM GENERATE_SERIES
     (
         1, 
         10000000
     ) AS gs
OPTION
(
    MAXDOP 8, 
    QUERYTRACEON 8649
);

But performance is still pretty good, here. Much better than prior.

I think most folks out there would be okay waiting a few seconds for 10 million rows to show up in a table for them.

So sequential. Much unique.

But, what if you want to load those rows in faster?

Caveat #2: Use A Heap Or SELECT INTO Instead

If you create a heap, or just SELECT INTO on instead, you’ll get a parallel load into the table.

For 10 million rows, there’s an improvement of about 1.7 seconds on my sort of crappy travel VM with 4 available CPUs.

DROP TABLE IF EXISTS
    dbo.art_aux;

CREATE TABLE 
    dbo.art_aux
(
    id int NOT NULL
);

INSERT INTO
    dbo.art_aux WITH(TABLOCK)
(
    id
)
SELECT
    gs.value
FROM GENERATE_SERIES
     (
         1, 
         10000000
     ) AS gs;

Here’s the plan:

Yes, dear reader, I even blog on vacation. That’s how much I love and care about you.

Thanks for reading!

Going Further

Why You Can’t Always Rely On Estimated Query Plans In SQL Server

Posted on August 4, 2022July 17, 2022 by Erik Darling

Simple Enough

Most of the time, if you get the estimated plan for a query, the actual execution plan will look about the same.

You’ll get the additional information of course, but the shape and most of the other attributes will match up.

Other times, things will change between the two.

Here’s a silly example I came across recently while working on demos for some other stuff.

SELECT TOP (100)
     p.Id,
     vs.UpVotes,
     vs.DownVotes
FROM dbo.VoteStats() AS vs
JOIN dbo.Posts AS p
    ON vs.PostId = p.Id
WHERE vs.DownVotes > vs.UpMultipier
AND   p.CommunityOwnedDate IS NULL
AND   p.ClosedDate IS NULL
ORDER BY vs.UpVotes DESC;

Estimated Execution Plan

Here’s the estimated plan I get for this query:

SQL Server estimates that a serial execution plan will work best, here.

Actual Execution Plan

Here’s the actual execution plan for the same query:

At runtime, SQL Server decides that a parallel plan is the better solution.

Wild Nights

SQL Server can totally estimate parallel plans. I’ve seen it a million times.

In this case, though, it doesn’t do that. If you get the estimated plan after executing the query, it’ll come back with the parallel version.

Why does this happen? Well, as SQL Server adds features to the Intelligent Query Processing, more and more decisions are made when a query executes.

Stuff like this can make estimated and actual plans look much different from each other:

Adaptive joins
Interleaved execution
Table variable deferred compilation
Batch mode on row store

Wonders of modern query optimizers, eh?

Thanks for reading!

Going Further

Let’s Stop Calling Queries “Expensive”

Posted on August 3, 2022July 17, 2022 by Erik Darling

Bad Names

When we talk about finding queries to tune, there’s an unfortunate term that gets thrown around: Expensive Queries.

Why is it unfortunate? Well, it reinforces the wrong mindset when it comes to query tuning, and leads people down the wrong path, looking at the wrong metrics.

SQL Server does have a cost-based optimizer, but those costs don’t mean anything to your hardware, or even to how long a query runs for.

Those costs are all estimates, based on two-decade old computer specs. There are many times when they’re wrong, or not properly aligned with reality.

Worse, it leads people to want to do crazy things like sort the plan cache by query cost to find things to tune.

Worse than that, they’ll look at “query cost relative to batch” to compare two queries for efficiency.

Ethically Sourced

There are many sources to find queries eating up your server hardware.

The point of this post isn’t to teach you how to use any of those things, but to teach you how to be smarter about using them in whatever way you’re comfortable.

My two favorite metrics to look at when looking for queries to tune are CPU and RAM. I explain why in the post, but the short story is that they’re reliable, real-life metrics that can be directly measured before and after to gauge progress.

I don’t look at things like reads, because those might go up or down while your query runtime doesn’t change at all.

They’re also pretty misleading if you’re looking at STATISTICS IO in a lot of circumstances, like with lookups.

Sortie

A while back I recorded a bunch of videos that show how cached/estimated plans can be really misleading when it comes to costs and all that stuff.

You can find it here:

Thanks for reading!

Going Further

Some New Stuff In SQL Server 2022 CTP 2.1

Posted on August 2, 2022August 1, 2022 by Erik Darling

New Baby

SQL Server 2022 CTP 2.1 dropped recently, and there’s some new stuff in it! What kind of new stuff?

Well, there’s some linguistic stuff:

APPROX_PERCENTILE_DISC
APPROX_PERCENTILE_CONT
DATETRUNC
IS [NOT] DISTINCT FROM
Bit manipulation:
- LEFT_SHIFT
- RIGHT_SHIFT
- BIT_COUNT
- GET_BIT
- SET_BIT

How useful any of that will be depends on how you use SQL Server. I’ve never heard anyone yearning for bit manipulation functions, but perhaps I travel in the wrong circles.

Inside You

As far as “unannounced” stuff, here’s some of what I noticed:

There’s a new database scoped configuration called LEDGER_DIGEST_STORAGE_ENDPOINT. The name seems fairly self-documenting, but I can’t picture myself using ledger enough to explore it further.

There are a bunch of new objects, too:

+------------------------------------------------------------+----------------------------------+
|                            name                            |            type_desc             |
+------------------------------------------------------------+----------------------------------+
| dm_db_xtp_hash_index_approx_stats                          | SQL_INLINE_TABLE_VALUED_FUNCTION |
| dm_tran_distributed_transaction_stats                      | VIEW                             |
| extgov_attribute_sync_objects_synchronizing                | VIEW                             |
| sp_change_feed_configure_parameters                        | SQL_STORED_PROCEDURE             |
| sp_dw_physical_manifest_file_table_insert                  | EXTENDED_STORED_PROCEDURE        |
| sp_dw_physical_upsert                                      | EXTENDED_STORED_PROCEDURE        |
| sp_help_change_feed_table                                  | SQL_STORED_PROCEDURE             |
| sp_manage_distributed_transaction                          | EXTENDED_STORED_PROCEDURE        |
| sp_md_discovery_stats_staleness_detection                  | EXTENDED_STORED_PROCEDURE        |
| sp_reset_dtc_log                                           | EXTENDED_STORED_PROCEDURE        |
| sp_trident_create_credential_to_access_internal_md_storage | EXTENDED_STORED_PROCEDURE        |
+------------------------------------------------------------+----------------------------------+

Again, their usefulness will depend on how you use the product.

Here are some new extended events:

query_ce_feedback_telemetry
query_feedback_analysis
query_feedback_validation
resumable_add_constraint_executed

I don’t think I’ll need to add any of these to sp_HumanEvents, but I’m sure you’ll see them in lots of Microsoft demos.

There are also some new wait stats, but I think I’ll be ignoring them.

CDC_SCAN_FINISHED
CORRUPTED_PAGE_PROCESS
DW_DB
DW_WS_DB_LIST
NATIVE_SHUFFLE_WRITE_BUFFER_DEQUEUE
PARQUET_INDEX_BUILD_MANIFEST_SYNC
PURVIEW_POLICY_SDK_PREEMPTIVE_SCHEDULING
PWAIT_S3_TEMP_CREDENTIAL
RBAC_AAD_GROUP_INFO
SPINLOCK_EXT
WAIT_EXTGOV_PERMCACHE_DECISIONLOCK
WAIT_EXTGOV_PERMCACHE_RESOURCELOCK

Nothing all that juicy this time around. Maybe next time!

Thanks for reading!

Going Further

How SQL Server 2022’s Parameter Sensitive Plan Feature Can Make Query Store Confusing

Posted on August 1, 2022July 15, 2022 by Erik Darling

Psssssp

Thanks for watching!

Going Further

Be Careful With Memory Recommendations When Installing SQL Server Standard Edition

Posted on July 29, 2022July 12, 2022 by Erik Darling

Friday I’m Not Here

This is a short post to warn you about the memory recommendation tab in the SQL Server installer.

Let’s say you’re doing the smart thing and giving your Standard Edition install 192 GB of RAM:

Why is this smart? Because Standard Edition’s 128GB memory limit is only on the buffer pool. SQL Server can use memory over and above that for other stuff, like:

Query memory grants
Compressed backups
Other caches/Lock memory, etc.

The problem is that the installer’s recommendation only reflects the buffer pool limit. It doesn’t actually consider that memory can get used over that, and you end up with this recommendation:

That’s 128GB exactly, which is daft to the back teeth.

The number I usually start with here is 184320, which is 180GB. That gives Windows about 12GB to work with.

Thanks for reading!

Going Further

Stored Procedures vs sp_executesql In SQL Server: Is One Better Than The Other?

Posted on July 28, 2022July 12, 2022 by Erik Darling

Basically

I get this question a lot while working with clients, largely in a couple specific contexts:

Me telling someone they need to use dynamic SQL in a stored procedure
Applications sending over parameterized SQL statements that are executed with sp_executesql

Often, the dynamic SQL recommendation comes from needing to deal with:

IF branching
Parameter sensitivity
Optional parameters
Local variables

Even in the context of a stored procedure, these things can really suck performance down to a sad nub.

But The Code

Now, much of the SQL generated by ORMs terrifies me.

Even when it’s developer misuse, and not the fault of the ORM, it can be difficult to convince those perfect angels that the query their code generated is submaximal.

Now, look, simple queries do fine with an ORM (usually). Provided you’re:

Paying attention to indexes
Not using long IN clauses
Strongly typing parameters
Avoiding AddWithValues

You can skate by with your basic CRUD stuffs. I get worried as soon as someone looks at an ORM query and says “oh, that’s a report…” because there’s no way you’re generating reasonable reporting queries with an ORM.

Procedural Drama

The real upside of stored procedures isn’t stuff like plan reuse or caching or 1:1 better performance. A single parameterized query run in either context will perform the same, all things considered.

Where they shine is with additional flexibility in tuning things. Rather than one huge query that the optimizer has to deal with, you can split things up into more manageable chunks.

You also have quite a bit more freedom with various hints, trace flags, query rewrites, isolation levels, etc.

In other words: eventually your query needs will outgrow your ORMs ability to generate optimal queries.

Until then, use whatever you’re able to get your job done with.

Thanks for reading!

Going Further

SQL Server 2022: Database Scoped Configurations And Feature Accessibility

Posted on July 27, 2022July 11, 2022 by Erik Darling

Overlooked

In my quest to tell you about new stuff in SQL Server 2022, I forgot to take a look at new things in sys.database_scoped_configurations.

It turns out that’s important, because some stuff has to be turned on if you want it to work. Here’s a list of new database scoped configurations for SQL Server 2022:

+------------------+-----------------------------------------------+-------+------------------+
| configuration_id |                     name                      | value | is_value_default |
+------------------+-----------------------------------------------+-------+------------------+
|               29 | ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY       |     0 |                1 |
|               31 | CE_FEEDBACK                                   |     1 |                1 |
|               37 | DOP_FEEDBACK                                  |     1 |                0 |
|               26 | DW_COMPATIBILITY_LEVEL                        |     0 |                1 |
|               27 | EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS         |     1 |                1 |
|               34 | MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT        |     1 |                1 |
|               33 | MEMORY_GRANT_FEEDBACK_PERSISTENCE             |     1 |                1 |
|               35 | OPTIMIZED_PLAN_FORCING                        |     1 |                0 |
|               28 | PARAMETER_SENSITIVE_PLAN_OPTIMIZATION         |     1 |                1 |
|               25 | PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES |  1440 |                1 |
+------------------+-----------------------------------------------+-------+------------------+

Some stuff is on by default, some stuff isn’t. You might notice DOP_FEEDBACK and OPTIMIZED_PLAN_FORCING are both off by default, here (even though I turned them on).

ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;

Invitation Only

You might think that Microsoft is so excited about new features (especially in CTP builds) that they want them to be easy to preview and test by people who care enough to do that stuff.

No so much, it turns out.

Heck, I’ve seen several presentations that said Query Store would be on by default in new databases created on SQL Server 2022 instances, but I haven’t had that happen yet and that’s not even a new feature.

Perhaps, like the daftly-hidden Azure Nonsense© in the installer, this will be changed in a future version, but no one tells me anything.

Anyway, not much here beyond this. Mind your database scoped database configurations when you’re testing out new features. Also make sure to check for extended events that help you track down if/when heuristics for those features kick in.

Thanks for reading!