Posts by Brent Ozar

SQL Server automatically chooses when to divide your query’s work across multiple CPU cores. It makes that decision based on your query’s cost. To see it, let’s throw 1,000,000 tiny rows in a table: Transact-SQL CREATE TABLE dbo.Timeless(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing VARCHAR(20)); INSERT INTO dbo.Timeless (Stuffing) SELECT TOP 1000000 'Stuff' FROM sys.all_columns…

What Should We Change About the Data Professional Salary Survey for 2019?

October 18, 2018

Salary

Every year, we publish an open Data Professional Salary Survey. Last year, 3,113 folks from 73 countries answered, so we’re doing it again this year. Here are the questions we asked last year. If there’s any changes you want to make, leave a comment and we’ll talk through it as a community. I want to make…

Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.

October 16, 2018

Locking, Blocking, and Isolation Levels

Slapping WITH (NOLOCK) on your query seems to make it go faster – but what’s the drawback? Let’s take a look. We’ll start with the free StackOverflow.com public database – any one of them will do, even the 10GB mini one – and run this query: Transact-SQL UPDATE dbo.Users SET WebsiteUrl = 'https://www.BrentOzar.com/'; 12 UPDATE…

Building SQL ConstantCare®: Separating Out the Videos

October 15, 2018

When we started offering SQL ConstantCare®, our mentoring service, I wanted to make our web site as simple as practical. Too many choices can paralyze people (and a great place to read more about that is The Paradox of Choice.) To streamline your options, I just bundled all of our self-paced recorded training videos in…

Index Tuning Week: Getting Blocking? Play “Hot or Not.”

October 11, 2018

Deadlocks, Indexing

This week, we’re all about tuning indexes. So far, we’ve covered Brent’s 5 and 5 Rule and The D.E.A.T.H. Method. Today, let’s talk about reducing blocking and deadlocking. Normally, when we think about the causes of blocking or deadlocks, we use badly written queries that modify different tables in the wrong order, resulting in a Mexican…

Index Tuning Week: My D.E.A.T.H. Method for Tuning Indexes

October 9, 2018

Indexing

This week I’m running a series of posts introducing the fundamentals of index tuning. Yesterday, I used Brent’s 5 and 5 Rule to explain why you want to aim for around 5 indexes per table, and 5 columns (or less) per index. Now, how do we get from our current database situation to a better…

LGBTQ Meetup at the PASS Summit

October 8, 2018

#SQLPass

The SQL Server community is known for its welcoming acceptance of everyone, regardless of their experience or background. The SQL Server family actively reach out to help others and bring folks together. A great example of that is the LGBTQ Meetup organized by Andy Mallon (@amtwo.) Whether you identify as gay, lesbian, bisexual, transgender, questioning,…

Index Tuning Week: How Many Indexes Are Too Many?

October 8, 2018

Indexing

This week I’m running a series of posts introducing the fundamentals of index tuning. First up, let’s talk about understanding how many indexes make sense on a table. The more nonclustered indexes you have on a table, the slower your inserts and deletes will go. It’s that simple. If you have a table with 10…

Pour One Out for Database Diagrams, and Good Riddance

October 7, 2018

Development

On the left hand side, you see SQL Server Management Studio 17.9. On the right side, 18.0. Something’s missing in the new 18.0: Rest in peace, Database Diagrams SSMS 17.9 on the left has Database Diagrams at the top. SSMS 18.0 does not. Database Diagrams are simply gone. Hallelujah! For over a decade, people have…

SQL Server Management Studio 18’s Execution Plans Will Change The Way You Look At Plan Tuning

October 4, 2018

The preview of the next version of SQL Server Management Studio is out, and it has a radical improvement to query plans that will shock and amaze you. It’s best to just show you: Estimated vs actual rows You can see the estimated and actual number of rows right there on the query plan just…

We Need to Talk About the Warnings In Your Query Plans.

October 3, 2018

SQL Server, thanks for coming today. Have a seat. Yes, this might seem odd – there are a lot of us here. And yes, we are locking the door behind you. We all care about you – all of us care very deeply – but we need to talk about some warning signs. Specifically, the…

When You Need to Tune A View, Don’t Just Get Its Plan

September 28, 2018

Say your database has a view, and everybody’s queries use it. Let’s take the Stack Overflow database and create this view: Transact-SQL CREATE OR ALTER VIEW dbo.vwQuestionsAndAnswers AS SELECT q.Title, q.Id AS QuestionId, a.Id AS AnswerId, a.Body AS Answer, uQuestioned.DisplayName AS Questioner_DisplayName, uAnswered.DisplayName AS Answerer_DisplayName, COUNT(DISTINCT vQ.Id) AS VotesOnQuestion, COUNT(DISTINCT uVotedQuestion.Id) AS UsersWhoVotedOnQuestion FROM dbo.Posts…

New Class Week: Developer’s Guide to SQL Server Performance Tuning

September 27, 2018

You’re stuck with a database server that’s not going fast enough. You have a hunch that the biggest bottleneck is inside the database server somewhere, but where? In this one-day class, you’ll learn how to identify which queries are killing your server, what parts of the database server are holding you back, how to tackle…

New Class Week: Fundamentals of Azure Data Factory

September 26, 2018

No Comments

Azure Data Factory, or ADF, is an Azure PaaS (Platform-as-a-Service) that provides hybrid data integration at global scale. Use ADF to build fully managed ETL in the cloud – including SSIS. Join Andy Leonard – author, blogger, and Chief Data Engineer at Enterprise Data & Analytics – as he demonstrates practical Azure Data Factory use cases. In this course, you’ll…

New Class Week: Performance Tuning in 21 Demos

September 25, 2018

No Comments

We first unveiled this class for the PASS Summit, but due to high demand, we’re doing an online version, too. 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 jump right…

What’s New in SQL Server 2019 System Tables

September 24, 2018

SQL Server 2019

The free SQL Server 2019 preview download is out, and here are quite a few things to check out that aren’t documented yet: New System Objects Starting with new stored procedures: sys.sp_add_feature_restriction sys.sp_autoindex_cancel_dta sys.sp_autoindex_invoke_dta sys.sp_cloud_update_blob_tier sys.sp_configure_automatic_tuning sys.sp_diagnostic_showplan_log_dbid sys.sp_drop_feature_restriction sys.sp_execute_remote sys.sp_force_slog_truncation sys.sp_internal_alter_nt_job_limits sys.sp_rbpex_exec_cmd sys.sp_set_distributed_query_context sys.sp_set_session_resource_group sys.sp_showinitialmemo_xml sys.sp_xa_commit sys.sp_xa_end sys.sp_xa_forget sys.sp_xa_forget_ex sys.sp_xa_init sys.sp_xa_init_ex sys.sp_xa_prepare sys.sp_xa_prepare_ex sys.sp_xa_recover sys.sp_xa_rollback…

What’s New in SQL Server 2019’s sys.messages: More Unannounced Features

September 24, 2018

SQL Server 2019

The system table sys.messages holds error messages that SQL Server needs to return to end users. When a new build comes out, I love checking this table to see what features Microsoft has coded, but isn’t quite ready to announce yet. When SQL Server 2017 came out, that’s how I unmasked snapshot materialized views, a…

What’s New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)

September 24, 2018

SQL Server 2019, T-SQL

For over a decade, SQL Server’s handling of table variables has been legendarily bad. I’ve long used this Stack Overflow query from Sam Saffron to illustrate terrible cardinality estimation: Transact-SQL declare @VoteStats table (PostId int, up int, down int) insert @VoteStats select PostId, up = sum(case when VoteTypeId = 2 then 1 else 0 end), down…

What’s New in SQL Server 2019: Adaptive Memory Grants

September 24, 2018

Execution Plans, 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

September 22, 2018