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 *

Post image

Performance as a feature, software developer edition

Relational database management systems (RDBMS) like SQL Server and Azure SQL Database are very good at managing normalized data. Efficient storage and retrieval of data is the name of the game, so performance is a feature. That’s why SQL Server (and other RDBMS products in the market) keep as much of the data in memory
-> Continue reading Performance as a feature, software developer edition

Virtual Log Files: 200 or 1000?

Last week I had the privilege of reviewing possibly the best SQL Server production environment I’ve seen in Canada. During the follow-up meeting, the senior DBA and I had a discussion about Virtual Log Files (VLFs), disagreeing on the maximum number of Virtual Log Files a transaction log should have. I said 200, he said
-> Continue reading Virtual Log Files: 200 or 1000?

the world is on fire

Secure or fast? Secure, obviously …

By now you have probably seen the news about a major flaw in the design of CPUs from all major vendors (Intel, AMD, and ARM) resulting in a series of vulnerabilities in operating systems and … web browsers? One of my favourite things to do is to make queries run faster. What the Meltdown and
-> Continue reading Secure or fast? Secure, obviously …

No moving parts, the story of persistent memory

In November 2017, during the PASS Summit keynote, Microsoft’s Bob Ward (Principal Architect) demonstrated a “diskless database” running on Hewlett-Packard Enterprise (HPE) hardware. The storage layer is known as “persistent memory”. At the end of November, Bob and his colleague Jamie Reding (Senior Program Manager) wrote a blog post about this new thing, which you
-> Continue reading No moving parts, the story of persistent memory