SQL Server

How Trace Flag 2335 Affects Memory Grants

Last Updated January 8, 2019

This trace flag is documented in KB #2413549, which says, “Using large amounts of memory can result in an inefficient plan in SQL Server.” The details are a little light, so let’s run a quick experiment with: SQL Server 2017 CU 8 (14.0.3029.16) VM with 4 cores, 32GB RAM, max memory set to 28GB Stack Overflow…

A Common Query Error

Last Updated August 14, 2018

Erik Darling

SQL Server

So Many Choices When you only need stuff from a table where there’s matching (or not!) data in another table, the humble query writer has many choices. Joins In/Not In Exists/Not Exists No, this isn’t about how NOT IN breaks down in the presence of NULLs, nor is it a performance comparison of the possibilities.…

Setting Up SQL Server: People Still Need Help

Last Updated August 8, 2018

Erik Darling

SQL Server

I Like What’s Happening I wanna start off by saying that I like what Microsoft has been doing with the setup process — it made a lot of sense to add the tempdb configuration screen, and having a checkbox to turn on Instant File Initialization was amazingly helpful. Even in the cloud, people still need…

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

Last Updated August 4, 2018

Brent Ozar Unlimited Team

SQL Server, Videos

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: Office Hours Webcast - 2018/8/1Watch this video…

Do I Have A Query Problem Or An Index Problem?

Last Updated July 20, 2018

Erik Darling

SQL Server

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…

Is the CXCONSUMER Wait Type Harmless? Not So Fast, Tiger.

Last Updated August 9, 2020

Erik Darling

Execution Plans, SQL Server

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. If SQL Server doesn’t balance that work evenly across multiple threads, you can experience the CXCONSUMER and/or CXPACKET wait types. To show how SQL Server ends up waiting, let’s write…

A New tempdb Trace Flag Appears!

Last Updated July 2, 2018

Erik Darling

SQL Server

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: +

Last Updated July 10, 2018

Erik Darling

SQL Server

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

Last Updated June 27, 2018

Erik Darling

SQL Server

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

Last Updated June 27, 2018

Brent Ozar

SQL Server 2019

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

Last Updated May 30, 2018

Erik Darling

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

Last Updated May 24, 2018

Brent Ozar

SQL Server

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

Last Updated January 8, 2019

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?

Last Updated 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

Last Updated May 7, 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

Last Updated May 7, 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

Last Updated May 7, 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

Last Updated 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

Last Updated April 27, 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)

Last Updated May 20, 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…