SQL Server

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2019

When you run a query, SQL Server guesses how much memory you’re going to need for things like sorts and joins. As your query starts, it gets an allocation of workspace memory, then starts work. Sometimes SQL Server underestimates the work you’re about to do, and doesn’t grant you enough memory. Say you’re working with…

Leaked: SQL Server 2019 Big Data Clusters Introduction Video

Last Updated September 22, 2018

SQL Server 2019

Psst – you’re probably not supposed to see this yet, but look what @WalkingCat found: SQL Server 2019 Vision pic.twitter.com/zbjI28FYSu — WalkingCat (@h0x0d) September 21, 2018 What the video says Growing volumes of data create deep pools of opportunity for those who can navigate it. SQL Server 2019 helps you stay ahead of the changing time…

A Simple Stored Procedure Pattern To Avoid

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

Get Yourself Together This is one of the most common patterns that I see in stored procedures. I’m going to simplify things a bit, but hopefully you’ll get enough to identify it when you’re looking at your own code. Here’s the stored procedure: Transact-SQL CREATE OR ALTER PROCEDURE dbo.you_are_not_clever ( @cd DATETIME NULL) AS BEGIN…

Quirks When Working With Extended Events To Track Locks

Last Updated September 13, 2018

Worse Than Mobile Browsing I have a love/hate relationship with Extended Events. Yes, they’re powerful. Yes, you can track interesting things. But they’re just not intuitive, like, 10 years on. Part of the problem is that the narrative from Microsoft has been that it’s a full replacement for Profiler and Traces. How many monitoring tool…

Can Forced Parameterization Go Wrong?

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

App Like That If you’ve got the kind of application that sends bare-assed strings to SQL Server, you may end up with a weird choice. Brent will be sitting on one shoulder telling you to use Forced Parameterization. I’ll be on the other shoulder asking if you’ve really thought this whole thing through while you…

Announcing SQL Server 2019

Last Updated September 10, 2018

SQL Server 2019

TEASE Who Let The Docs Out? Ignite must be coming up. If you head over to Microsoft’s GitHub repo, you can peruse around for stuff updated recently. Maybe you’ll create an account. Maybe you’ll start contributing to open source projects. Maybe you’ll quietly slip into a world of solitude for days on end. Happy Saturday!…

Locks Taken During Indexed View Modifications

Last Updated September 2, 2018

Locking, Blocking, and Isolation Levels, SQL Server

Frankenblog This post has been nagging at me for a while, because I had seen it hinted about in several other places, but never written about beyond passing comments. A long while back, Conor Cunningham wrote: This same condition applies to indexed view maintenance, but I’ll save that for another day :). AFAIK he hasn’t…

Foundational Material: Microsoft SQL Server Book and Blogs From The Past

Last Updated August 13, 2018

What Did Dinosaurs Watch On TV? These are some of my favorite books and blogs from Microsoft from the way-back machine. I can’t say every bit of information is still 100% true and should be followed to the letter, but hey, that’s what happens. This is stuff I consider foundational material, though. I’ve learned a…

Wait Stats When VSS Snaps Are Slow

Last Updated August 8, 2018

Deus Redux A while back I wrote about the Perils of VSS Snaps. After working with several more clients having similar issues, I decided it was time to look at things again. This time, I wanted blood. I wanted to simulate a slow VSS Snap and see what kind of waits stats I’d have to…

How Trace Flag 2335 Affects Memory Grants

Last Updated January 8, 2019

Memory Grants, SQL Server

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

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

Brent Ozar Unlimited Team

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

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: You can register to attend next week’s Office Hours,…

Do I Have A Query Problem Or An Index Problem?

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

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

Last Updated August 9, 2020

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

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

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

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