Execution Plans

SQL Server 2017 CU5: Finding Problems With Parallelism

March 22, 2018

Execution Plans, SQL Server 2017

Snow Is Temporary Cumulative Update 5 dropped for SQL Server 2017. Hooray. Poor Taryn. Nooooooooo, I just patched! — Taryn (@onlybluefeet) March 20, 2018 These new servicing models sure do make me glad I don’t have a real job. I’m also happy because I get to write weird queries to test interesting new functionality. This SQL…

Why Multiple Plans for One Query Are Bad

March 21, 2018

Development, Execution Plans

I’m going to demo this using the Stack Overflow public database. We’ll use the Users table – which has exactly what you think it has, everyone who’s asked/answered/commented at StackOverflow.com. I need to search for people by their DisplayName, so I’ve created an index on that: Transact-SQL CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName); 1 CREATE INDEX…

If You Can’t Index It, It’s Probably Not SARGable

March 20, 2018

Execution Plans, Indexing

Thumbs Rule OK? When writing queries, it’s really common to want to take shortcuts to express some logic. I know, it “works fine”, and the results are “right”, and you have “more important” things to do. But take a minute here for future you. Just think of reading this post as a continuation of the…

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

March 15, 2018

Execution Plans

sp_prepare For Mediocre You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)! Great posts, Kendra! Following the same theme, we found this issue while looking at queries issued from JDBC. Specifically, the prepared statement class seems to cause queries to hit the density vector…

I Most Certainly Do Have A Join Predicate

March 9, 2018

Execution Plans

FREAK OUT You wrote a query. You joined tables. You have the right ON clause. You have the right WHERE clause. But the query plan has a problem! BugBurg How Could This Happen To Me? Oh, relax. You’re not crazy. You just assumed the worst. Like me whenever I feel pain near my liver. Call…

Troubleshooting Parameter Sniffing Issues the Right Way: Part 2

March 7, 2018

Execution Plans

In part 1 of this series, I showed you how to get the compiled parameter values for an execution plan that you are investigating. You’ve identified it as a bad execution plan. You’ve already fixed the production emergency by removing the bad plan from the plan cache. You’re almost ready to start testing for a…

Troubleshooting Parameter Sniffing Issues the Right Way: Part 1

March 6, 2018

Execution Plans

Many of you are troubleshooting Parameter Sniffing issues the wrong way in production. In this three-part series, I’ll show you how to troubleshoot it the right way: Getting the Compiled Parameter Values Getting the App’s SET Options Avoiding the Density Vector This is part 1 in the series. Parameter Sniffing 101 When a query suddenly…

Of Windowing Functions And Where Clauses

March 2, 2018

Execution Plans, SQL Server

Seen One, Seen’em All This isn’t about indexing! I promise. In fact, I’ve dropped all my indexes for this. It’s just that, well, I’ve seen this happen with two clients in a row, so maybe it’s time to blog about it. If you run this query, you get a fairly obvious plan. Transact-SQL WITH nonsense…

Joe Sack query bucks

Adaptive Joins, Memory Grant Feedback, and Stored Procedures

February 28, 2018

Adaptive Joins, Execution Plans, Memory Grants

Not Exactly The Catchiest Name There’s a TL;DR here, in case you don’t feel like reading the whole darn thing. Batch mode memory grant feedback works with stored procedures It takes several runs to adjust upwards to a final number It seems to only adjust downwards once (in this case by about 40%) This isn’t…

Computed Columns and Cardinality Estimates

February 23, 2018

Development, Execution Plans, SQL Server

:thinking_face: When most people think about computed columns, they don’t think about cardinality estimates. Heck, I’m not sure most people think about cardinality estimates. At all. Ever. One of the few people who has ever responded to my emails does, and I didn’t even have to threaten him. Do you think about computed columns? If…

Then Why Doesn’t SQL Always Seek?

February 16, 2018

Execution Plans, Indexing, SQL Server

Scan The Man There seems to be a perpetual battle waged against the Index Scan. At some point it was declared that scans were inferior to seeks, and all energy should be dedicated to eradicating them. Much like asking why the whole plane isn’t made out of the stuff the black box is made out…

Memory Grants: SQL Server’s Other Public Toilet

February 15, 2018

Execution Plans, Indexing, Memory Grants

Sharing Is Caring When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants. The problem becomes itself when queries start to over and under estimate their practical needs. Second Hand Emotion Queries ask for memory to do stuff. Memory is a shared resource. What kind of stuff? Well, usually…

Functions and “the use of indexes”

February 1, 2018

Execution Plans, Indexing, SQL Server

Wordy Bird This sentence gets repeated a lot. You know the one. “Functions prevent the use of indexes.” Well, knowing you, it’s probably just your indexes. I’ve seen your indexes. Which Indexes? In the SUPERUSER database (yeah, I know, I’m cheating on Stack Overflow), all the tables have a PK/CX on an Id column, which is…

How Much Can One Row Change A Plan, Part 4

January 19, 2018

Execution Plans, SQL Server

Monte Carlo Or Bust In Part 3, I showed you how two queries with TOP clauses can produce wildly different plans. To figure some stuff out about why the plans changed, let’s focus on the Nested Loops Join plan. Why? Because when I force the Hash Join plan, the results are no different. I get…

How Much Can One Row Change A Plan, Part 3

January 18, 2018

Execution Plans, SQL Server

All The Rage We’ve asked how much one column and even one row can change a plan before. The doctor says I’m not allowed to read blog comments anymore on account of my blood pressure, but the AI I wrote in PowerShell assures me they were well-received. So here we go again, ONCE MORE INTO THE FRAY!…

Brent Ozar reading

“But It Worked in Development!” – 3 Hard Performance Problems

January 15, 2018

Development, Memory Grants

You’ve been performance tuning queries and indexes for a few years, but lately, you’ve been running into problems you can’t explain. Could it be RESOURCE_SEMAPHORE, THREADPOOL, or lock escalation? These problems only pop up under heavy load or concurrency, so they’re very hard to detect in a development environment. In a very fast-paced session, I’ll…

SQL Server 2017 CU3 adds optimizer row goal information in query plans

January 12, 2018

Execution Plans, SQL Server 2017

Row Whats? Like it or not, the optimizer has row goals. We’re not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I’ll be adding it to the FRK in an upcoming release. I am going to give you a round up…

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

January 11, 2018

Execution Plans, Memory Grants, SQL Server 2017

Where have you been all my life? Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills. They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill…

SQL Server 2017 CU3 adds CXCONSUMER waits, doesn’t mention it

January 10, 2018

Execution Plans, SQL Server 2017, Wait Stats

The revolution will not be documented At the PASS 2017, Pedro Lopes (don’t call him low-pez) from Microsoft mentioned that a new parallelism wait was getting added soon. That wait, if you’re too darn tootin’ lazy to click, is called CXCONSUMER. According to Pedro’s slide, but not the ENTIRELY MISSING DOCUMENTATION, this wait is the…

SQL Server 2017 CU3 adds execution statistics for scalar-valued, user-defined functions

January 9, 2018

Execution Plans, SQL Server 2017

Update This seems to finally be fixed as of CU12 for SQL Server 2017. If you’re not already patched up, head over to the link to get there. Estimated Plans Need Not Apply This is, of course, only available in actual plans. All together now: If a query uses one or more user-defined scalar functions…

Menu