Posts by Brent Ozar

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

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…
Read More
Portrait in T-SQL

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

Humor
6 Comments
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…
Read More
How I Use the First Responder Kit

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

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…
Read More

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

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…
Read More

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

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…
Read More

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

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…
Read More

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

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…
Read More

SQL Server 2019 is out…now.

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…
Read More

My Annual Black Friday Sale is On Now! Save 50-82%.

Company News
The time is finally here, the moment you’ve been waiting for all year: your chance to save 50-82% on my training classes and online services. Everything on the site is 50% off during November, but you can save even more with these 3 bundles: Level 1: Fundamentals$295Recorded Class Season PassSQL ConstantCare®The Consultant ToolkitNormal price: $1,385Save…
Read More

My annual Black Friday sale starts tomorrow. Here’s what you wanna know.

The time is almost here – the moment you’ve been waiting for all year. I’ve sharpened my pencils to take the prices as low as I can possibly take ’em: Level 1: Fundamentals$295Recorded Class Season PassSQL ConstantCare®The Consultant ToolkitNormal price: $1,385Save $1,090 (79%)Buy NowLevel 2: Live Classes, No VM$995Live Class Season Pass(does not include lab…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}