Blogs

Index Tuning Decision Tree for SQL Server

I recently mapped out my thought process for how I approach a new instance of SQL Server when it comes to index tuning.

Continue reading

Deadlock Code for the WideWorldImporters Sample Database

If you haven’t checked out Microsoft’s new WideWorldImporters sample database for 2016, it’s a pretty cool new little database. The database makes it easy to play around with new 2016 features, and it even ships with some cool little executables to run inserts in the “workload-drivers” folder.

Continue reading

Unique Constraints vs Unique Indexes

Unique constraints and unique nonclustered indexes have a lot in common: unique constraints are implemented with a unique index behind the scenes.

While unique nonclustered indexes have a couple of additional features, I can still think of a scenario where it might be best to use unique constraints.

Continue reading

Estimated vs. Actual Number of Rows in Nested Loop Operators

This is one of those little details that confused me a ton when I was first working with execution plans.

One problem with learning to work with plans is that there’s just SO MUCH to look at. And it’s a bit spread out. So, even when looking at a single tooltip, things can be confusing.

Let’s talk about the nested loop operator, who can be particularly weird to understand.

Continue reading

Finding Your Voice as a Speaker (Dear SQL DBA Episode 15)

Learn what it’s like to find and develop your voice as a technical speaker in this week’s episode of Dear SQL DBA.

Continue reading

Do Clustered Index Columns Count Against Max Nonclustered Key Size?

SQL Server 2016 and Azure SQL Database have a lot of little perks. One little thing that changed is that the maximum key size allowed for nonclustered indexes was raised from 900 bytes to 1700 bytes.

Continue reading

Collect and Baseline Wait Statistics (Dear SQL DBA Episode 14)

What are the best tools to collect and baseline wait statistics? Should you write your own? Watch the 18 minute video or read the episode transcript below.

Continue reading

Free Poster: tempdb data files in SQL Server

The tempdb database is a strange place in SQL Server.

Continue reading

Developer Seeks DBA Career (Dear SQL DBA, Episode 13)

A C# developer wants to be a DBA, but is having a hard time landing a job. Should they take a pay cut and go for a Junior level job? Or could there be another path?

Continue reading

Does Adding NOINDEX Change DBCC CHECKDB with PHYSICAL_ONLY?

The NOINDEX option does change the behavior of DBCC CHECKDB (even if you’re already using PHYSICAL_ONLY). Here’s how the two options compare to one another, and how to see the difference yourself in a simple example.

Continue reading