Proposed SQL Server defaults: max server memory

A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ad hoc workloads to enabled Set max server memory (MB) to a
-> Continue reading Proposed SQL Server defaults: max server memory

The case of scalar UDF inlining, where context is everything

Here’s an interesting story for you this week. As part of the new Intelligent Query Processing improvements introduced in the upcoming SQL Server 2019, we find a new feature called scalar UDF inlining. This post is not about scalar UDF inlining exactly, but IQP-adjacent if you like. It works by taking a typical scalar user-defined
-> Continue reading The case of scalar UDF inlining, where context is everything

Proposed SQL Server defaults: optimize for ad hoc workloads

A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ad hoc workloads to enabled Set max server memory (MB) to a
-> Continue reading Proposed SQL Server defaults: optimize for ad hoc workloads

Proposed SQL Server defaults: disable priority boost

A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ad hoc workloads to enabled Set max server memory (MB) to a
-> Continue reading Proposed SQL Server defaults: disable priority boost

Proposed SQL Server defaults: disable lightweight pooling

A few months ago I suggested that the following settings should be the default for most SQL Server instances. Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ad hoc workloads to enabled Set max server memory (MB) to a
-> Continue reading Proposed SQL Server defaults: disable lightweight pooling

Proposed SQL Server defaults: cost threshold for parallelism

A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ad hoc workloads to enabled Set max server memory (MB) to a
-> Continue reading Proposed SQL Server defaults: cost threshold for parallelism

When the buffer pool isn’t just in memory

Last time we looked at the four major components of a computer system, and then looked at the SQL Server buffer pool as a way to leverage the best performance from computing hardware. Temperature Before we dive deeper into the buffer pool, I wanted to briefly mention data access terminology. A common metaphor for accessing
-> Continue reading When the buffer pool isn’t just in memory

Flagrantly ignoring the 10% rule

My friend Michael J. Swart has a rule of thumb he calls Swart’s Ten Percent Rule. If you’re using over 10% of what SQL Server restricts you to, you’re doing it wrong. After a recent discussion on Twitter, I wondered what it would look like if I had 32,767 databases on one instance of SQL
-> Continue reading Flagrantly ignoring the 10% rule

SQL Server logo

Fix SQL Server with one click

Tempting headline, isn’t it? It might even seem like clickbait, but that’s not the intention. The SQL Server default configuration is not recommended for production environments, and yet I have worked on many production environments that have been set up by people who don’t know that the default configurations are not recommended. These same people
-> Continue reading Fix SQL Server with one click

messy paint

Why you should not use SELECT *

A shorter post this week, but an important one. Last week, Erik Darling commented on my post saying that we shouldn’t use SELECT *, which was both amusing and accurate. Amusing, because a number of the example T-SQL queries in that post made use of this construct. Why not? Why was Erik’s comment accurate? A
-> Continue reading Why you should not use SELECT *