Product has been added to your cart.

SQL Server

[Video] What Percent Complete Is That Index Build?

March 26, 2020

Index Maintenance, SQL Server 2017, Videos

SQL Server 2017 & newer have a new DMV, sys.index_resumable_operations, that show you the percent_completion for index creations and rebuilds. It works, but…only if the data isn’t changing. But of course your data is changing – that’s the whole point of doing these operations as resumable. If they weren’t changing, we could just let the operations…

That's it, I'm rolling back to SQL Server 6.5

Breaking News: SQL Server 2019 CU2 Breaks Agent.

March 1, 2020

Breaking News, SQL Server 2019

That’s it, I’m rolling back to SQL Server 6.5 You reported it here in the comments, and over at SQLServerUpdates, and in this DBA.StackExchange.com question. The official word is in: yep, SQL Server 2019 Cumulative Update 2 breaks Agent jobs on some servers, causing them not to run. That official word is only in that…

Interesting Undocumented Tidbits in SQL Server 2019 CU2

February 19, 2020

SQL Server 2019

SQL Server 2019 Cumulative Update 2 is out, and Microsoft snuck in a few things that they didn’t mention in the release notes. Contained Availability Groups continue to get new investments. In the lead-up to SQL Server 2019’s release, we got a few presentations at various conferences where Microsoft folks said we’d get the ability…

#TSQL2sday: My Life Hack is an Hourglass. Yes, an Hourglass.

February 11, 2020

For this month’s T-SQL Tuesday, Jess Pomfret asked for our favorite life hacks. My life hack is a half-hour hourglass. (Halfhourglass?) Seriously, an actual physical object: I keep it on my desk. Whenever I start working on a task, I flip the glass. After 30 minutes, I need to stop and assess the progress I’ve…

SQL Server Doesn’t Encrypt Data By Default.

November 27, 2019

Create a table and insert a couple of rows: Then back up the database without using compression, and open up the backup file with a hex editor: The same trick works on the data file, too. That’s it. That’s all. That’s the post. Look, not every post is groundbreaking: sometimes it’s just about writing down…

Live Class Season Pass

Announcing Weekend Versions of My Mastering Classes

November 18, 2019

Last week, I noticed that an awful lot of you were buying my Level 2 and Level 3 Bundles with your own personal money (not company cards), so I wondered – if your company wasn’t paying for it, were they giving you time off? So I polled to ask if you were interested in taking…

Live Class Season Pass

Contest: Guess the SQL Server 2020 Release Date

November 8, 2019

When will Microsoft officially release SQL Server 2020 for download? The dust is just barely starting to settle on the 2019 box, so it’s time to guess the next one. Leave one – and only one – comment here in YYYY/MM/DD format with your release date guess. If you leave multiple comments, only the first/earliest…

SQL Server 2019 is out…now.

November 4, 2019

SQL Server 2019

After just one release candidate, Microsoft has decided it’s ready to go, apparently! Well, kinda: the official build in the release notes is 15.0.2000.5, and there’s already a “servicing update” to 15.0.2070.41. I’m not sure I wanna know what’s going on there. Anyhoo, SQL Server 2019 is available for download now. This also means it’s…

SQL Server 2019 Standard Edition Feature Limitations Are Out

October 17, 2019

SQL Server 2019

The features list by edition is out, and there’s a disclaimer at the top: This content is being updated for SQL Server 2019. The information in the article is not final. Which means if you’re really angry, and your company buys a lot of licenses, now’s your time to raise holy hell with your Microsoft…

[Video] What’s New in SQL Server 2019

October 14, 2019

SQL Server 2019, Videos

We must be getting pretty close to the official release of Microsoft SQL Server 2019. There are less than 80 days left in the year. (Interestingly, it’s been almost 60 days since Release Candidate 1 shipped – that’s unusually long for a delay between builds. Hmm.) With the clock ticking down, now’s a good time…

In-Memory Multi-Version Concurrency Control

Research Paper Week: In-Memory Multi-Version Concurrency Control

August 22, 2019

If you’ve been doing performance tuning for several years, or graduated from my Mastering Server Tuning class, you’ve come across Read Committed Snapshot Isolation, aka RCSI, aka multi-version concurrency control, aka MVCC, aka optimistic concurrency. It’s not the way SQL Server ships by default, although it is the default for Azure SQL DB, and it’s part…

Data Driven Podcast

Podcast: Data Driven Inteview w/Me

July 30, 2019

I had the pleasure yesterday of spending an hour chatting with Andy Leonard and Frank La Vigne for the Data Driven podcast. Some of the questions they asked me include: How did you find your way into data? What’s your favorite part of your current gig? Complete this sentence: when I’m not working, I enjoy…

Help! SQL Server 2008 is Still Here!

[Video] Help! SQL Server 2008 is Still Here!

July 10, 2019

SQL Server 2008, Videos

Your company is ignoring the news: SQL Server 2008 and 2008R2 are officially out of support as of today, but nothing’s changing at your company. You still have SQL Server 2008 in production, and you’re a little nervous. How should you approach the conversations with management? Brent Ozar will help: he’s been there too, fighting…

It's hard work looking this good.

SQL Server 2008 and R2 Support Ends Tomorrow. Let’s Talk About That.

July 8, 2019

SQL Server 2008

Yes, literally, extended support ends tomorrow. Take this 2-question poll about the oldest and newest versions you’re running Read the poll results Register for my DBA Fundamentals webcast tomorrow about 2008, and we’ll talk about the poll and the results I’ve got a pretty good idea of what the poll results are going to look…

Brent Reading Book

Poll: what’s in your development database?

July 1, 2019

When the developers at your company are working on building an app, where do they work? Production, a development database restored from production, or a development database with made-up contents? One-question poll, and you can watch the live results. I’ll post the results and my thoughts on Friday. I know what I see regularly out…

Setting MAXDOP During SQL Server 2019 Setup

May 23, 2019

SQL Server 2019

Just a short note – in SQL Server 2019, the setup process has a new MAXDOP tab: The hyperlink about MAXDOP configuration points here. I did a few quick test installs: 4 cores gave me a MAXDOP 4 8 cores offered MAXDOP 8 12 cores offered MAXDOP 6 Historically, the 0 default meant unlimited cores.…

Performance Tuning in 21 Demos

I’m coming to SQL Saturday Salt Lake City in August!

May 9, 2019

I’m coming to Salt Lake City in August for SQLSaturday #887. On Friday, I’ll be teaching a one-day pre-con: Performance Tuning in 21 Demos – You’re a developer or DBA who wants to tune indexes and queries to make your application go faster. You don’t like learning by watching slides or reading white papers – you…

Are your CPU-intensive queries running slowly? Check your VM type.

May 2, 2019

When you build a VM for SQL Server in Amazon EC2, Azure VMs, or Google Compute Engine, it’s all too easy to overlook the CPU speeds that you’re getting. Most of the time, it doesn’t really matter because SQL Server isn’t usually CPU-bottlenecked: your queries are doing table scans with 16GB RAM, starving for IO throughput.…

Froid

Finding Froid’s Limits: Testing Inlined User-Defined Functions

April 11, 2019

Execution Plans, SQL Server 2019, T-SQL

This week, I’ve been writing about how SQL Server 2019’s bringing a few new features to mitigate parameter sniffing, but they’re more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let’s talk about another common cause of wildly varying durations for a single query: user-defined functions. Scalar…

1 query, 3 parameters, 3 different plans

Parameter Sniffing in SQL Server 2019: Adaptive Joins

April 10, 2019

Adaptive Joins, SQL Server 2019

So far, I’ve talked about how adaptive memory grants both help and worsen parameter sniffing, and how the new air_quote_actual plans don’t accurately show what happened. But so far, I’ve been using a simple one-table query – let’s see what happens when I add a join and a supporting index: Transact-SQL CREATE INDEX IX_OwnerUserId_Includes ON…

Menu