SQL Server Archives - Page 4 of 49 - Brent Ozar Unlimited®

So You Wanna Debug SQL Server Part 1

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

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

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

March 16, 2018

Erik Darling

Managed Instances, Microsoft Azure, SQL Server

No Comments

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

March 16, 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!

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)

March 11, 2018

Brent Ozar Unlimited Team

SQL Server, Videos

No Comments

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

March 8, 2018

Tara Kizer

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

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

February 23, 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

February 21, 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?

February 16, 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

February 15, 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)

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)

February 4, 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 and “the use of indexes”

February 1, 2018

Erik Darling

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…

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

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 Might Fix the Scalar Functions Problem

January 29, 2018

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. The Connect item about their performance has historically been one of the top-voted complaints out there. At Summit…

Computed Columns: Reversing Data For Easier Searching

January 24, 2018

Erik Darling

Computed columns, Development, SQL Server

dba, development, performance tuning, 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

January 19, 2018

Erik Darling

Execution Plans, SQL Server

No Comments

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…