SQL Server

So You Wanna Debug SQL Server Part 1

Last Updated March 24, 2018

Start here! This is a gentle introduction to getting set up and started. I’m nowhere near as good at this as I want to be, but I’ve been inspired by a couple wizards: Bob the Ward and Paul the White. The first thing you need to do is go download WinDbg (or the debugger of your…

SQL Server 2017 CU5: Finding Problems With Parallelism

Last Updated March 22, 2018

Erik Darling

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…

How to Restore a SQL Server Database into Azure SQL DB Managed Instances Redux

Last Updated March 16, 2018

Erik Darling

Managed Instances, Microsoft Azure, SQL Server

Anything Brent Can Do There’s some things you can do with Managed Instances, and some ways you can look at data that you can’t do elsewhere. For instance, you have the ability to start an restore ASYNC — that means if your session drops, the restore will continue headlessly in the background. There’s nothing about…

Azure SQL DB Managed Instances: New Wait Stats

Last Updated March 16, 2018

Erik Darling

Managed Instances, Microsoft Azure, SQL Server

Incidental This is a long list that I haven’t had a change to dig through yet — all I did was compare them to which waits were occurring on my 2017 CU4 instance. There are about 174 of them that I found, though some may just be generated by Hekaton that I don’t have set up…

Azure SQL DB Managed Instances: We’re All GUIDs

Last Updated March 18, 2018

Erik Darling

Managed Instances, SQL Server

Six is having problems adjusting to his clone status Some funny things happen when you create databases up in a Managed Instance. For Instance (HEH!), you may expect this to yield some fruitful results, but it Manages (HAH!) to defy logic. Transact-SQL CREATE DATABASE YourMom; SELECT * FROM sys.master_files WHERE name = 'YourMom'; 12345 CREATE DATABASE YourMom; SELECT…

Azure SQL DB Managed Instances: Trace Flags, Ahoy!

Last Updated March 16, 2018

Erik Darling

Managed Instances, SQL Server

When you talk to people from Microsoft They’re all “aw shucks” about Trace Flags. “Don’t really need’em!” “Eh, haven’t used one in years.” “Sometimes they’re good for troubleshooting, but…” Then you hop on a managed instance (where you’re not allowed to turn Trace Flags on or off), and you see what they have turned on.…

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

Last Updated March 10, 2018

Brent Ozar Unlimited Team

SQL Server, Videos

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…