SQL Server

Home
SQL Server

How Table Variables Mess With Parallelism

June 7, 2018

This Is A Parallel Query Hi There This Is A Temp Table Transact-SQL CREATE TABLE #Posts (OwnerUserId INT); 1 CREATE TABLE #Posts (OwnerUserId INT); This Is A Parallel Insert Into A Temp Table Sort of asterisk (The Insert isn’t parallel, but the part of the plan leading up to it is) This Is A Table…

SQL Server Setup Feature List Changes 2008-2017

May 25, 2018

Brent Ozar

SQL Server

brento

If you happen to install several SQL Servers in a row, you’ll notice differences from over the years in the feature list. SQL Server 2008 Ah, a simpler time. When you got to the feature selection screen, here were your choices: SQL Server 2008 feature selection In case you’ve forgotten, Business Intelligence Development Studio (BIDS)…

A Strange Place For A Memory Grant

May 24, 2018

Erik Darling

Execution Plans, Memory Grants, SQL Server

If You Hang Around Fetish Bars Long Enough… You learn things about memory grants in execution plans. Stuff like the things that usually need memory Hash Join Hash Match Sort Parallelism Exchanges But there’s something that rarely gets discussed, even in the deepest, darkest, dankest dungeons. Let’s loosen the clamps and look at it. Keys…

Informal Poll: What Are Your Favorite Error Log Messages?

May 11, 2018

Erik Darling

SQL Server

I Know You Have Yours Gone Fishin’ I have mine. When I go looking at a server for the first time, I wanna know about stuff like corruption, stack dumps, 15 second I/O warnings, frozen I/O, and non-yielding schedulers. You know, the real meaty, hair on fire stuff. Hm. Meat on fire. I’m hungry. Despite…

Where Clustered Index Keys Dare

May 11, 2018

Erik Darling

Indexing, SQL Server

Colonel Clustered We’ve blogged a couple times about how clustered index key columns get stored in your nonclustered indexes: here and here. But where they get stored is a matter of weird SQL trivia. You see, it depends on how you define your nonclustered index. “It Depends” We all scream for dependencies! Hooray! If you…

When Query Plans Lie Part 2

May 9, 2018

Erik Darling

Execution Plans, Indexing, SQL Server

Getting Weirder In Part 1, we looked at how query plans can tell us little lies. Now we’re going to look at how those little lies can turn into bigger lies. Adding An Index Right now, this is our query: Transact-SQL SELECT u.UpVotes, u.DownVotes FROM dbo.Users AS u WHERE u.Reputation >= 100000; 123 SELECT u.UpVotes,…

The DeWitt Clause: Why You Rarely See Database Benchmarks

May 8, 2018

Brent Ozar

SQL Server

Back in the early 1980s, Dr. David DeWitt – who you might know from past PASS Summit keynotes, formerly of Microsoft (LinkedIn) – was working on measuring database performance. His team wrote a benchmark, ran it against Oracle, and Oracle didn’t fare well in the results. Oracle reacted as one would expect – they were…

Parameter Fluid Optimization

May 4, 2018

Erik Darling

SQL Server

Optimizer Studies I don’t have a real job, which means that if something strikes my fancy, like staring at a spot on the wall for an hour, I can do it. Occasionally things are a bit more inspired, and so I go poking around what SMRT PPL might be up to. All you nice people…

I’m speaking at SQL Saturday NYC

May 1, 2018

Erik Darling

SQL Server

No, Not Brent Just me. And a few dozen other people, on May 19 (2018). I have sessions on the query optimizer, and all the stuff I think is cool in SQL Server 2017. Away Days You know, I get it. The weather is just finally getting nice in these parts. No one wants to…

[Video] Office Hours 2018/4/25 (With Transcriptions)

April 29, 2018

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Brent, Erik, Tara, and Richie to discuss cloud server disaster recovery, Availability Groups troubleshooting, SQL Server deadlocks, slow tempdb latency, migrating to Azure, SQL Server 2017 gotchas, how to set up your dev environment when looking for a job, best allocation unit size for disks hosting data files and good replacement for linked…

So You Wanna Debug SQL Server Part 2

April 19, 2018

Erik Darling

SQL Server

Birth Control Debugging, and, heck, even learning about debugging, is a time-consuming and often unhelpful process when it comes to tracking down issues in SQL Server. It is arduous and tedious, and yields little chance of making you any friends. So why write about it? Mostly because no one else is willing to — at…

Parking Garages And Predicates

April 17, 2018

Erik Darling

Execution Plans, Indexing, SQL Server

Hotels on Wheels Parking garages and predicates have a “lot” in common. GET IT? No no but seriously. They do. If you’ve ever lost your ticket, forgotten where you’ve parked, or just driven around hopelessly waiting to see an empty spot, you probably wish there were a better way to do things. Transact-SQL CREATE TABLE…

So You Wanna Debug SQL Server Part 1

March 24, 2018

Erik Darling

SQL Server

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

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

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…