Tag: create temp table sql

SQL Server’s Filtered Indexes Don’t Always Have To Be Selective

Posted on by Erik Darling

Somewhere Between

I’ve had quite a bit of luck with filtered indexes over the years. When they work well, they’re absolute aces.

But there’s a bit of trickiness to them, both for getting the optimizer to match the expression in your query to the filter on the index (they have to be an exact match), and for getting it to choose that index (parameterization is a nuisance for this).

You also need to define them correctly, which means any columns that you’re filtering on need to reside in the key or included columns as well. Not doing that risks the index not being used.

One thing I hear quite a bit — I’ve probably even said it myself at times — is that unless the index filters the number of rows down an appreciable amount, the filter might be more trouble than it’s worth.

But what if…

Camel Wides

I see queries that sometimes have a lot of predicates. Where clause, join clause, the works.

When you have queries that reliably filter on some literal values, like bits or a status or something equivalent, you may want to consider filtering your index on those even if they’re not selective in order to accomplish two things:

  1. Reduce the number of candidate key columns
  2. Reduce the effort of applying residual predicates

Designing effective indexes is tough, and it’s something I see a lot of people struggle with. Particularly with complex queries that filter, join, group by, order by, maybe even throw a window function in there for good measure…

You see what I’m getting at. For most OLTP queries, I want my indexes to be where clause centric. The faster I can filter rows down to find the ones I care about the more problems I avoid later on.

Filtering indexes is just another way of doing that.

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.

Query Store Improvements I’d Like To See For SQL Server: Get The Full Query Text From The GUI

Posted on by Erik Darling

Spirited Away

This week’s posts are a series of short, and, admittedly lazy ones about improvements I’d like to see in Query Store.

I’m away from home for the Holiday Week, and I don’t have a lot of time to write at the moment. Normally I’d put these all into one post. I do hope you, dear reader, will forgive me. In the spirit of the holidays, or something.

Problem Five

People write big queries, whether it’s an ORM, in a stored procedure, or any other vehicle for sending your ANSI torture to SQL Server’s humble ports. Unfortunately, query plans don’t store the full text.

If you’re looking at query plans in Query Store’s GUI for a big query, you get AN ABSOLUTELY ENORMOUS pop up window, with a preview of the query text. But only a preview.

You can even get the full text if you query the underlying views directly. But most people aren’t taking that dive.

Often, getting the full query text is necessary for, you know… running the query without error. If it’s cut off, you can’t do that, and right now there’s no way to fetch it from the main dashboard screens.

That’s a major shortcoming, and fixing it would make life easier for anyone who uses Query Store for query tuning.

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.

Query Store Improvements I’d Like To See For SQL Server: Choose Queries To Ignore

Posted on by Erik Darling

Spirited Away

This week’s posts are a series of short, and, admittedly lazy ones about improvements I’d like to see in Query Store.

I’m away from home for the Holiday Week, and I don’t have a lot of time to write at the moment. Normally I’d put these all into one post. I do hope you, dear reader, will forgive me. In the spirit of the holidays, or something.

Problem Four

A lot of dumb things end up in Query Store that you can’t do anything about, but which skew the graphs you have to look at and make things hard to decipher.

For example:

  • Query store queries from the GUI
  • Index rebuilds
  • Statistics updates
  • Index creation
  • Some dumb thing that runs at 3am that no one cares about

But there they are, acting like the biggest, baddest thing to ever touch SQL Server.

Except you know they’re not. And they have no business showing up when you know better. For as many options to tweak things as are getting added to Query Store, being able to quarantine some stuff would be a nice option.

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.

Query Store Improvements I’d Like To See For SQL Server: Forced Plan Association

Posted on by Erik Darling

Spirited Away

This week’s posts are a series of short, and, admittedly lazy ones about improvements I’d like to see in Query Store.

I’m away from home for the Holiday Week, and I don’t have a lot of time to write at the moment. Normally I’d put these all into one post. I do hope you, dear reader, will forgive me. In the spirit of the holidays, or something.

Problem Three

Not every application is designed well. This may be shocking news to some of you.

One area where I consistently see application developers go wrong is with parameterization. Either their ORM doesn’t use parameters at all, or it only partially parameterizes queries.

When this happens, you can end up with a lot of duplicate plans both in your plan cache and in Query Store.

For queries that have no parameterization, the Forced Parameterization database setting can be very useful, but it doesn’t help with queries that are only partially parameterized.

It would be absolutely stellar if Query Store had a mechanism for associating queries with the same query or plan hashes are “morally equivalent” even if they’re not parameterized and be able to force them to use the same query plan, when you have a good one.

This would solve many applications problems that I see out there, where the developers refuse to lift a finger to solve basic problems that end users end up feeling.

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.

Query Store Improvements I’d Like To See For SQL Server: Memory Grant Columns

Posted on by Erik Darling

Spirited Away

This week’s posts are a series of short, and, admittedly lazy ones about improvements I’d like to see in Query Store.

I’m away from home for the Holiday Week, and I don’t have a lot of time to write at the moment. Normally I’d put these all into one post. I do hope you, dear reader, will forgive me. In the spirit of the holidays, or something.

Problem Two

When you run a query, you might get a memory grant, for example to hash or sort data. In the query plan, there are a whole bunch of columns that give you information about the memory grant:

  • SerialRequiredMemory         
  • SerialDesiredMemory          
  • RequiredMemory               
  • DesiredMemory                
  • RequestedMemory              
  • GrantWaitTime                
  • GrantedMemory                
  • MaxUsedMemory                
  • MaxQueryMemory               
  • LastRequestedMemory          
  • IsMemoryGrantFeedbackAdjusted

And if you read the query plan documentation, you’ll get some explanation of them, too.

Provide memory grant estimate as well as actual runtime memory grant information. Serial required/desired memory attributes are estimated during query compile time for serial execution. The rest of attributes provide estimates and counters for query execution time considering actual degree of parallelism. SerialRequiredMemory: Required memory in KB if the query runs in serial mode. The query will not start without this memory. SerialDesiredMemory: Memory estimated to fit intermediate results in KB if the query runs in serial mode. RequiredMemory: Required memory in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialRequiredMemory. DesiredMemory: Memory estimated to fit intermediate results in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialDesiredMemory. RequestedMemory: Memory in KB which the query requests the memory manager to grant. This can be smaller than sum of RequiredMemory and DesiredMemory if it exceeds the maximum allowed for single query. GrantWaitTime: Time in seconds if the query has to wait for successful memory grant. MaxUsedMemory: Maximum memory in KB used by the query. MaxQueryMemory: Maximum memory in KB allowed for single query. LastRequestedMemory: Memory in KB which was requested by the query from the memory manager during the last execution. IsMemoryGrantFeedbackAdjusted: Information regarding if the memory grant in this plan is adjusted based on memory grant feedback.

In Query Store, you get limited memory grant information in query_store_runtime_stats. But if you look in dm_exec_query_stats, there’s a whole lot more.

SQL Server Query Store
grim

It’s a much more complete view of the memory grant, and it’s a damn shame that you have to rely on the plan cache for this, because it’s so unreliable. Clearing out, dropping plans, recompiles, etc. etc. etc.

It would be marvelous if Query Store retained information like the size of the grant, so you can figure out if the amount of memory that it used was anywhere near reality. The ideal columns are not very useful to me, but the total and used are wonderful to compare.

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.

Query Store Improvements I’d Like To See For SQL Server: Recent Actual Plans

Posted on by Erik Darling

Spirited Away

This week’s posts are a series of short, and, admittedly lazy ones about improvements I’d like to see in Query Store.

I’m away from home for the Holiday Week, and I don’t have a lot of time to write at the moment. Normally I’d put these all into one post. I do hope you, dear reader, will forgive me. In the spirit of the holidays, or something.

Problem One

Query Store plans are equivalent to cached plans, which are equivalent to estimated plans. You get what SQL Server thought was gonna happen.

Sometimes, that can have very little in common with reality.

Other times, it can be misleading because of parameter sniffing.

Right now, SQL Server has a plan cache view in SQL Server 2019+ that holds the last actual plan. It would be really cool if Query Store tracked a configurable number of recent actual plans.

That’d really help folks out where the performance problem isn’t easy to deduce or reproduce from what they’ve got available.

Actual plans are incredibly valuable these days, with operator times included, and a whole bunch of other stuff that isn’t surfaced in estimated plans, like spills.

In real life, I see all sorts of weird things popping up as the bottleneck in query plans:

  • TOP above a SCAN
  • Repartition Streams
  • Compute Scalar

You only see the pain when operator times are involved, and Query Store needs to start collecting those to make query tuning easier.

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.

Things SQL Server vNext Should Address: Add Lock Pages In Memory To Setup Options

Posted on by Erik Darling

Consideration

This setting doesn’t get enough credit for all the good it does. Part of me thinks it should be the default for new SQL Server installs, if the amount of memory in the server is over a certain number, and max server memory is set to match.

You may not want it turned on only based on physical memory, because there are lunatics out there who stack SQL Server installs, and who install all sorts of other dimwitted things on there, too.

But since max server memory is a suggestion during setup, and perform volume maintenance tasks is included, this should be as well.

Again, it’s one less post-install step for automation-minded folks out there.

Burden

SQL Servers with large amounts of memory can especially benefit from this setting, because it allows them to access memory via a different API. The easy way to think of it is that SQL Server will get direct access to physical memory, instead of virtual memory.

Allocates physical memory pages to be mapped and unmapped within any Address Windowing Extensions (AWE) region of a specified process.

The AllocateUserPhysicalPages function is used to allocate physical memory that can later be mapped within the virtual address space of the process. The SeLockMemoryPrivilege privilege must be enabled in the caller’s token or the function will fail with ERROR_PRIVILEGE_NOT_HELD.

I generally advise people with good chunks of memory to enable this setting. There are very few good reasons not to on big boxes, and that’s why it should be called out in the installation process. Enabling it later means rebooting, and that sucks.

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.

Happy Thanksgiving From Darling Data

Posted on by Erik Darling

Why Are You Here?

In the fine tradition of a turkey being pardoned from becoming the centerpiece of a meal, I’m pardoning you from having to read a blog post about SQL Server.

Wherever you are, whoever you are, have a grand day!

Thanks for reading!

Things SQL Server vNext Should Address: Add Cost Threshold For Parallelism To Setup Options

Posted on by Erik Darling

Cracky

Unless you’re running a data warehouse, I can’t think of a good reason to leave this at the default (5) for most any workload.

Look at any SQL Server setup checklist not written by SharePoint admins, and you’ll see people tell you to change this setting to something higher than 5.

What you change it to is not what I’m here to talk about. I’m Team Start With 50, but you can be whatever team you want and we can still be friends.

I mean, unless you’re going to tell me I should look at the plan cache to figure that out, then we are mortal enemies because you are full of shabby ideas. The plan cache is full of lies and bad estimates, and totally unreliable in the long term.

You could probably make better guesses based on Query Store, but Cost Threshold For Parallelism is, unfortunately, not a database-level setting, and they’d still just be guesses. About estimates. So, you know… Cool your jets, hotshot.

But since MAXDOP is not only available, but also offers guidance for a correct setting to the installer, why not this one? It is, after all, an important counterpart.

If anything, it’d be nice to give folks who care about automation one less post-setup step to handle. To me, that’s a glaring omission.

Costy Things

Of course, maybe it’s time to get Cost Threshold For Parallelism some help. Cost is, after all, just a reference metric.

It can be good, it can be bad. High cost plans can run fast, low cost plans can run slow.

With all the intelligent stuff being built into the product, perhaps it’s time for it to consider things in addition to plan cost for whether a query is eligible for parallelism or not.

Imagine this scenario: You set Cost Threshold For Parallelism to 50, and a really important query that costs 20 query bucks comes along and runs slowly and single threaded every single time it executes. It never stands a chance at going parallel, unless you drop Cost Threshold For Parallelism way low for the whole server.

Your only option other than lowering Cost Threshold For Parallelism is using an unsupported trace flag (8649), or an unsupported USE hint (ENABLE_PARALLEL_PLAN_PREFERENCE).

It sure would be nice if there were a supported override that you could set, say a maximum CPU threshold for a serial plan. I don’t think you could change this in flight, but you could potentially have it act like memory grant feedback, and adjust between executions.

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.

Creating SQL Server Indexes: Stored Options vs. Creation Options

Posted on by Erik Darling

No Lies

When you create an index, there are a lot of options. Recently while working with nice people who pay me, I’ve gotten the same question a few times.

I like to hand off index change scripts to people to help them drop unused indexes, merge duplicative indexes, and add in helpful missing indexes. When I do, I always specify some options along with them to help the create process along, like MAXDOP, ONLINE, and SORT_IN_TEMPDB.

The thing is, those settings aren’t ones that kick in automatically next time you rebuild the index or something; you have to specify them each time.

Here are index creation options:

  • SORT_IN_TEMPDB
  • DROP_EXISTING
  • ONLINE
  • RESUMABLE
  • MAX_DURATION
  • MAXDOP

Here are stored index options:

  • PAD_INDEX
  • FILLFACTOR
  • IGNORE_DUP_KEY
  • STATISTICS_NORECOMPUTE
  • STATISTICS_INCREMENTAL
  • ALLOW_ROW_LOCKS
  • ALLOW_PAGE_LOCKS
  • OPTIMIZE_FOR_SEQUENTIAL_KEY
  • DATA_COMPRESSION

Roll Out

Where you use these options is situational.

For example, only Enterprise Edition can create indexes online, or using a parallel plan. You can sort in tempdb for any of them, though. I might use different DOPs depending on the size of the server, and of course if I’m creating a column store index (for those, DOP 1 is sometimes a good idea).

For the stored options, I leave most of them alone. I always start Fill Factor off at 100, and with page compression turned on. Those are both things you can adjust or remove later if they turn out to not be ideal, but I love testing them out.

Data compression is especially useful on Standard Edition servers with a limited buffer pool (128GB) and large data sets, but can be just as useful on Enterprise Edition when that antichrist VM admin refuses to part with a single more GB of memory.

And hey, maybe in the future as more workloads move to 2019+, I’ll get to spend more time optimizing for sequential keys.

Maybe.

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.