SQL Server

[Video] Office Hours 2018/03/07 (With Transcriptions)

Last Updated March 10, 2018

This week, Brent, Erik, Tara, and Richie discuss database restores, opening a case with Microsoft, buying a pre-built server vs building your own, auto-shrinking all databases with a single command, SQL Server dev environment options, the best way to learn Git, excessive memory grants, and more. Here’s the video on YouTube: You can register to…

Troubleshooting Parameter Sniffing Issues the Right Way: Part 3

Last Updated May 26, 2020

Tara Kizer

Parameter Sniffing, SQL Server

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…

Of Windowing Functions And Where Clauses

Last Updated March 2, 2018

Erik Darling

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…

Computed Columns and Cardinality Estimates

Last Updated February 12, 2018

Erik Darling

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…

Let’s Give The Optimizer A Name

Last Updated February 17, 2018

Erik Darling

Humor, SQL Server

Clunky As my favorite monkey likes to say, the hardest things do with computers are: 1. Naming things 4. Asynchronous processing 2. Cache invalidation 5. Off by one errors Things are tough for Microsoft, too. Take “the optimizer” for instance. It’s based on something called the Cascades Framework. No one wants to say “Cascades Framework”,…

Then Why Doesn’t SQL Always Seek?

Last Updated February 6, 2018

Erik Darling

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…

New SQL Server Management Studio 17.5: It’s Classified

Last Updated February 16, 2018

Brent Ozar

Breaking News, SQL Server 2017

SQL Server Management Studio 17.5 is out, and new in this release is a SQL Data Discovery & Classification feature. The idea is that it’ll scan your database, identify columns containing potentially sensitive data, and help you become compliant with regulations like PCI, HIPAA, and GDPR. Let’s see how it works on the Stack Overflow…

[Video] Office Hours 2018/1/17 (With Transcriptions)

Last Updated February 4, 2018

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Tara, Erik, and Richie discuss 3rd-party tools for object-level restore, patching, stringing multiple CTEs, Meltdown and Spectre, instance stacking, organizing DBA teams, DBCC checks, moving local databases to Amazon RDS, CTEs vs temp tables, and backups and restores. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to…

[Video] Office Hours 2018/1/31 (With Transcriptions)

Last Updated February 5, 2018

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Brent, Tara, and Richie discuss a simple way to fill tempdb, tools for retrieving data from transaction logs, tools for diagraming server architecture, their favorite counters for checking if SQL server on VMWare has sufficient memory, SSRS subscription issues, change requests, thread pool issues, best practices for SELECT * into, failover SQL Server…

Functions Can Still Use Indexes, Kinda.

Last Updated May 1, 2021

Erik Darling

Execution Plans, Indexing, SQL Server

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. Functions can use indexes, but even so, that doesn’t mean that they’re going to perform as well as queries without functions. Which Indexes? In the SUPERUSER database (yeah, I know, I’m…

Other People’s GitHubs: Fork, Clone, and Pull

Last Updated January 30, 2018

Erik Darling

SQL Server

Let’s say you found an interesting repository (code project) on Github, and you wanted to make a contribution to it. Here’s how it works. Profilin’ Did you know that Richie has a stored procedure call sp_DataProfile? I didn’t even know he had a website until today. I wish he’d talk about this stuff more. In…

Froid: How SQL Server 2019 Will Fix the Scalar Functions Problem

Last Updated June 25, 2020

Brent Ozar

SQL Server, SQL Server 2019

Scalar functions and multi-statement table-valued functions are notorious performance killers. They hide in execution plans, their cost is under-estimated, the row estimates are way off, they cause queries to go single-threaded, I could go on and on. Microsoft is bringing a fix in SQL Server 2019, and thanks to a newly published paper, we know…

Computed Columns: Reversing Data For Easier Searching

Last Updated January 19, 2018

Erik Darling

Computed columns, Development, SQL Server

During Training We were talking about computed columns, and one of our students mentioned that he uses computed columns that run the REVERSE() function on a column for easier back-searching. What’s back-searching? It’s a word I just made up. The easiest example to think about and demo is Social Security Numbers. One security requirement is…

How Much Can One Row Change A Plan, Part 4

Last Updated January 17, 2018

Erik Darling

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

Last Updated January 17, 2018

Erik Darling

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!…

When Does Index Size Change Index Choice?

Last Updated January 13, 2018

Erik Darling

Indexing, SQL Server

Size Matters? One thing I hear a lot is that the optimizer will take the size of an index into account when choosing which one to use. The tl;dr here is that it may make more of a difference when it comes to index width (think the number of columns in an index) than it…

[Video] Office Hours 2018/1/10 (With Transcriptions)

Last Updated January 18, 2018

Brent Ozar Unlimited Team

SQL Server, Videos

This week Brent, and Richie discuss validating backups using the backup set table, how to figure out if you’re a Mid-Level or Senior DBA, rollbacks, compatibility modes while upgrading SQL Server versions, MAXDOP settings, and Brent’s car purchasing tip. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our…

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

Last Updated March 22, 2018

Erik Darling

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

Last Updated May 26, 2020

Erik Darling

Execution Plans, Memory Grants, Parameter Sniffing, 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 add a new CXCONSUMER wait type

Last Updated August 9, 2020

Erik Darling

Execution Plans, SQL Server 2017, Wait Stats

SQL Server 2017’s new CXCONSUMER wait type was originally announced by Microsoft’s Pedro Lopes, and now it’s out. Here’s what it means for performance tuners. According to Pedro, this wait is the “safe” type of parallelism wait, as opposed to the CXPACKET wait type, which means work isn’t evenly balanced across all of our cores. Pedro blogged…