Development

Like Table Variables, Kinda Jeremiah wrote about them a few years ago. I always get asked about them while poking fun at Table Variables, so I thought I’d provide some detail and a post to point people to. There are some interesting differences between them, namely around how cardinality is estimated in different situations. Table…

SQL Server 2017 CU5: Finding Problems With Parallelism

Last Updated 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

Last Updated August 15, 2022

Development, Execution Plans, Parameter Sniffing

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

Last Updated March 13, 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

Last Updated May 26, 2020

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…

Announcing 2 New Online Classes: Database DevOps and Practical Real-World Performance Tuning

Last Updated March 6, 2018

Company News, Development

We’re proud to announce that in addition to our existing summer lineup: Always On Availability Groups: The Senior DBA’s Field Guide with Edwin Sarmiento, 3 days $2,995 Data Science Fundamentals with R with Steph Locke, 2 days, $1,995 PowerShell for DBAs with Drew Furgiuele, 2 days, $1,995 We’ve now got two new online training classes…

I Most Certainly Do Have A Join Predicate

Last Updated March 1, 2018

Parameter Sniffing, SQL Server

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 3

Last Updated May 26, 2020

Tara Kizer

In part 1 and part 2 of this series, I showed you the various ways to get the application’s SET options so that you can get the same execution plan as the application. If you skip that step and run the query that is timing out in the application, it could be fast in SSMS. Your…

Troubleshooting Parameter Sniffing Issues the Right Way: Part 2

Last Updated May 26, 2020

Tara Kizer

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

Last Updated May 26, 2020

Tara Kizer

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…

How to Get a Random Row from a Large Table

Last Updated March 7, 2018

T-SQL

Method 1, Bad: ORDER BY NEWID() Easy to write, but it performs like hot, hot garbage because it scans the entire clustered index, calculating NEWID() on every row: The plan with the scan That took 6 seconds on my machine, going parallel across multiple threads, using tens of seconds of CPU for all that computing…

Of Windowing Functions And Where Clauses

Last Updated 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…

Adaptive Joins, Memory Grant Feedback, and Stored Procedures

Last Updated May 26, 2020

Adaptive Joins, Execution Plans, Memory Grants, Parameter Sniffing

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…

SET IMPLICIT_TRANSACTIONS ON Is One Hell of a Bad Idea

Last Updated May 6, 2019

Development

By default, when you run a query in SQL Server, your delete/update/insert (DUI) is finished as soon as your query finishes. That’s because the default behavior of SQL Server is IMPLICIT_TRANSACTIONS OFF – meaning, SQL Server doesn’t hold your queries open waiting for a COMMIT or ROLLBACK. As an example, I’ll connect to the StackOverflow…

Fifteen Things I Hate About ISNUMERIC

Last Updated February 26, 2018

T-SQL

Yello! Transact-SQL SELECT ISNUMERIC('$') AS [What] UNION ALL SELECT ISNUMERIC('£') AS [What] UNION ALL SELECT ISNUMERIC(',') AS [What] UNION ALL SELECT ISNUMERIC('.') AS [What] UNION ALL SELECT ISNUMERIC('0e+99') AS [What] UNION ALL SELECT ISNUMERIC('2e2') AS [What] UNION ALL SELECT ISNUMERIC('12D4') AS [What] UNION ALL SELECT ISNUMERIC(',1,1,1,1,1,1,1') AS [What] UNION ALL SELECT ISNUMERIC('-') AS [What] UNION…

Computed Columns and Cardinality Estimates

Last Updated February 12, 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…

Using WITH (NOEXPAND) to Get Parallelism with Scalar UDFs in Indexed Views

Last Updated February 12, 2018

T-SQL

Scalar functions are the butt of everybody’s jokes: their costs are wrong, their STATS IO results are wrong, they stop parallelism when they’re in check constraints, their stats are wrong in 2017 CU3, they stop parallelism in index rebuilds and CHECKDB, I could go on and on. Recently, we ran across yet another scenario where…

The Many Levels Of Concurrency

Last Updated February 12, 2018

Locking, Blocking, and Isolation Levels

“Concurrency is hard” Construction begins on the Tower of Babel, 610 BCE (colorized) When designing for high concurrency, most people look to the hardware for answers. And while it’s true that it plays an important role, there’s a heck of a lot more to it. Start Optimistic Like, every other major database vendor is optimistic…

Then Why Doesn’t SQL Always Seek?

Last Updated February 6, 2018