Posts by Brent Ozar

Home
Brent Ozar

Brent Ozar

1785 posts

https://www.brentozar.com

I make Microsoft SQL Server go faster. I love teaching, travel, and laughing. I live in California with my wife Erika.

SQL ConstantCare® Population Report: Fall 2019

December 2, 2019

Brent Ozar

SQL ConstantCare

Our SQL ConstantCare® service lets users send data in about their SQL Servers and get health & performance advice via email. Users can opt in to share their anonymized data to get better recommendations overall. With 3,568 database servers sharing data as of November 28, there’s a lot of useful information in here for the…

SQL Server Doesn’t Encrypt Data By Default.

November 27, 2019

Brent Ozar

SQL Server

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…

How to Use CrystalDiskMark 7 to Test Your SQL Server’s Storage

November 26, 2019

Brent Ozar

Storage

I bet you wanna know whether your storage is hot or not, and there’s no quicker, easier way to get a rough idea of your storage’s capabilities than to fire up CrystalDiskMark. This month, CrystalDiskMark released an all-new version 7, and it makes for even better testing. Start by downloading it and installing it –…

New Multi-Server Emails for SQL ConstantCare®

November 25, 2019

Brent Ozar

Company News, SQL ConstantCare

No Comments

With our SQL ConstantCare® service, you install a service that polls your SQL Servers once a day, and then we send you a daily email telling you the most important tasks to perform on that server to make it faster and more reliable. We’re all about minimizing the amount of emails you get from us…

How to Think Like the Engine: When a Seek Isn’t

November 21, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine, Indexing

In our last episode, I introduced the concept of scan predicates: execution plan operations that weren’t able to seek directly to the rows they needed. Let’s take another query: Transact-SQL DropIndexes; GO CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); GO SELECT Id FROM dbo.Users WHERE LastAccessDate > '1800/01/01' AND DisplayName = N'Brent Ozar'; GO…

How to Think Like the Engine: Index Column Order Matters a LOT.

November 20, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine, Indexing

We’ve been working with the clustered index of the Users table, which is on the Identity column – starts at 1 and goes up to a bajillion: And in a recent episode, we added a wider nonclustered index on LastAccessDate, Id, DisplayName, and Age: Transact-SQL CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); GO 123…

Writing Diagnostic Queries is Hard Because SQL Server Still Has (Tiny) Bugs.

November 19, 2019

Brent Ozar

sp_BlitzIndex

When I first got started working with SQL Server, I assumed it was flawless because it came in a sealed box from a ginormous corporation. I figured they’d tested every edge case, and that there was no way things were gonna go sideways. These days, even though I read CU KB articles and giggle about…

Announcing Weekend Versions of My Mastering Classes

November 18, 2019

Brent Ozar

SQL Server

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…

So, uh, you can, like, draw me in SSMS.

November 16, 2019

Brent Ozar

Humor

Adrian Sullivan and Michael J. Swart have way too much time on their hands, because Adrian just posted this T-SQL gist, which draws … me: It uses the spatial results feature in SSMS. My mind is blown. I’m totally going to use that in a demo. Thanks, Adrian! I’m already envisioning all kinds of crazy…

I’m coming to SQL Saturday Stockholm! Registration is open now.

November 15, 2019

Brent Ozar

Conferences and Classes

Hello again, Sweden! I’m coming to SQL Saturday Stockholm in May 2020. I’ll be teaching my Performance Tuning in 21 Demos workshop: 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 want to…

SQL ConstantCare® Now Gives Index Advice, Too.

November 14, 2019

Brent Ozar

SQL ConstantCare

When I was a DBA, I got so sick and tired of monitoring tools and scripts yelling at me to make a million different changes – most of which didn’t really have any measurable impact on performance or reliability. Sure, I want things to go fast, but I only have so many hours in the…

The Many Problems with SQL Server’s Index Recommendations

November 13, 2019

Brent Ozar

Indexing

These days, I jump back & forth a lot between SQL Server and Postgres. (We use AWS Aurora Postgres to store SQL ConstantCare®‘s data.) Whenever I come back to the sweet, sweet graphical execution plans in SQL Server Management Studio, I breathe a sigh of relief. Dang, these things are so much easier for me…

Announcing a New Live Online Class: How I Use the First Responder Kit

November 12, 2019

Brent Ozar

Company News, First Responder Kit Updates

During the hands-on labs for my Mastering classes, when I’m answering an attendee question, this kind of thing happens a lot: Attendee: “How would I find the query causing that missing index recommendation?” Me: “Oh, good question – you can use the sp_BlitzCache @SlowlySearchPlansFor parameter. Here’s how it works.” (Does a quick demo) Attendees in…

How to Think Like the SQL Server Engine: Included Columns Aren’t Free.

November 11, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Indexing

In our last cliffhanger episode, I said that if we ran this query: Transact-SQL UPDATE dbo.Users SET Age = Age + 1 WHERE Id = 643; 123 UPDATE dbo.Users SET Age = Age + 1 WHERE Id = 643; And we had this index: Transact-SQL CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); 12 CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON…

Would you attend the Mastering classes on a weekend?

November 9, 2019

Brent Ozar

Company News

I notice that a lot of you are springing for my Black Friday deals with your own money rather than the company’s money. That got me wondering – if you’re paying for it out of pocket, maybe your company isn’t going to give you free time at work to attend the class either. So, should…

Contest: Guess the SQL Server 2020 Release Date

November 8, 2019

Brent Ozar

SQL Server

112

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…

How to Think Like the SQL Server Engine: Should Columns Go In the Key or the Includes?

November 7, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Indexing

In our last episode, in between crab rangoons, I had you create either one of these two indexes: Transact-SQL CREATE INDEX IX_LastAccessDate_Id_Includes ON dbo.Users(LastAccessDate, Id) INCLUDE (DisplayName, Age); GO CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); GO 1234567 CREATE INDEX IX_LastAccessDate_Id_Includes ON dbo.Users(LastAccessDate, Id) INCLUDE (DisplayName, Age);GOCREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age);GO And I said…

How to Think Like the SQL Server Engine: Building Wider Indexes to Deal with Bad T-SQL

November 6, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Indexing

In our last episode, we were running into problems with these two queries: Transact-SQL SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate BETWEEN '2018-08-31' AND '2018-09-01' ORDER BY LastAccessDate; SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE CAST(LastAccessDate AS DATE) = '2018-08-31' ORDER BY LastAccessDate; 123456789 SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate BETWEEN…

How to Think Like the SQL Server Engine: When Statistics Don’t Help

November 5, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Statistics

In our last episode, we saw how SQL Server estimates row count using statistics. Let’s write two slightly different versions of our query – this time, only looking for a single day’s worth of users – and see how its estimations go: Transact-SQL SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate BETWEEN '2018-08-27' AND…

How to Think Like the SQL Server Engine: Using Statistics to Build Query Plans

November 4, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Statistics

In our last episode, SQL Server was picking between index seeks and table scans, dancing along the tipping point to figure out which one would be more efficient for a query. One of my favorite things about SQL Server is the sheer number of things it has to consider when building a query plan. It…