create index in sql server – Page 61

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

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.

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!

Going Further

This OVERPOWERED Hint Will Break NOLOCK Hints In SQL Server

Posted on July 26, 2022July 8, 2022 by Erik Darling

G4M3R

CREATE TABLE
    dbo.view_me
(
    id int NOT NULL
);
GO 

CREATE VIEW 
    dbo.viewed
AS 
SELECT
    vm.*
FROM dbo.view_me AS vm WITH(READCOMMITTED);
GO 

SELECT
    v.*
FROM dbo.viewed AS v WITH(NOLOCK);
GO

Msg 4138, Level 16, State 1, Line 22

Conflicting locking hints are specified for table “dbo.view_me”. This may be caused by a conflicting hint specified for a view.

Thanks for reading!

Going Further

Updates To sp_HumanEvents

Posted on July 25, 2022July 4, 2022 by Erik Darling

Touchy, Touchy

I’ve made some slight tweaks to sp_HumanEvents.

One of them fixes a bug with view creation when you use a custom schema. The rest are minor tweaks and fixes that you (hopefully won’t notice one bit), but make me feel a little bit better about the state of the code.

You can learn how to use the proc, and download the latest version over here.

I took a look through SQL Server 2022’s new Extended Events, and haven’t seen anything in there that would warrant the amount of work it takes to add something new just yet. Maybe at some point, I suppose.

Some folks had asked about adding lightweight query profiling etc. as an option, but it still doesn’t return the kind of details that make it useful for query tuning.

I mean, I guess if you believe in memes it’s okay, but… Maybe you should stop that.

Thanks for reading!