SQL Server

This week, Erik and Richie discuss monitoring tools, finding all unused tables across databases, query tuning, deleting vs hanging on to indexes, sharding databases, query editors, aggressively-locked indexes, why a plan would not be in the plan cache, and Richie’s current housing situation. Here’s the video on YouTube: You can register to attend next week’s Office Hours,…

Do I Have A Query Problem Or An Index Problem?

July 26, 2018

Party Up When someone says “this query is slow”, and you can rule out contextual stuff like blocking, odd server load, or just an underpowered server, what’s the first thing you look at? There’s a lot of potential culprits, and they could be hiding in lots of different places. Pick a card, any card. After…

CXCONSUMER Is Harmless? Not So Fast, Tiger.

July 24, 2018

Execution Plans, SQL Server

In Theory Let’s say you’ve got a query, and the point of that query is to take your largest customer/user/whatever and compare their activity to smaller whatevers. You may also stumble upon this issue accidentally if your query happens to capture populations like that by accident. For our purposes, let’s look at a query that…

A New tempdb Trace Flag Appears!

July 17, 2018

Coming Around Microsoft has been doing a good job of taking stuff that used to be Wizard Hat and making it part of out of the box functionality. Starting with SQL Server 2016, you didn’t need to keep these in your setup checklist: Trace flag 1118 to reduce SGAM contention Trace flag 1117 so when…

#TSQL2sday: +

July 10, 2018

For this month’s T-SQL Tuesday, He of Perfect Hair, Bert Wagner asked, “What code have you written that you would hate to live without?” There are a lot of pieces of code I frequently need to reuse when working on the First Responder Kit, like string splitting, XML PATH-ing, and converting milliseconds to some human…

Ola Hallengren’s Scripts Keep Getting Better

July 5, 2018

You Know Him, You Love Him You trust your database backups and integrity checks to him, and he has been KILLING IT on new features lately. A while back, Ola decided to Open Source his scripts on GitHub (Soon to be known as Microsoft LiveHub for Business Essentials). As more and more people embrace GitHub…

10 SQL Server Feature Requests I’ve Upvoted Recently

July 4, 2018

Brent Ozar

SQL Server 2018

In no particular order: Database Level Option for Storing Wait Stats – just like we can store query plan history in Query Store, it’d be awesome to get performance history over time. Give more info on the “String or Binary Data Would be Truncated” error – tell us which column, and what row tried to…

How Table Variables Mess With Parallelism

June 7, 2018

Execution Plans, SQL Server

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

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

Execution Plans, 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

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

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

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

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

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

Brent Ozar Unlimited Team

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

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

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