a coffee filtering process

Don’t optimize for ad hoc workloads as a best practice

(This post was co-authored by Erik Darling.) The more things stay the same, the more they change… No, that’s not a mistake. In fact, it’s a reference to long-held belief systems that don’t take new information into account, and how confirmation bias is not a good motivator for recommending best practices. Let’s talk about the
-> Continue reading Don’t optimize for ad hoc workloads as a best practice

DataGrillen logo

Join me in Germany in June

I have been selected to speak at the DataGrillen conference later this year. I will be presenting my session How Does SQL Server Store That Data Type?, which I debuted during last year’s EightKB online conference. This is the abstract for the talk, which will be presented on Thursday June 2nd, 2022, from 1:15 pm – 2:15 pm in
-> Continue reading Join me in Germany in June

EightKB logo

Join me at the EightKB virtual conference on 28 July 2021

Next week on Wednesday 28 July 2021, I will be presenting a brand-new session titled “How SQL Server stores that data type” for the free EightKB virtual conference. I’ve made it a 500-level talk, as reading hex and binary for fun requires a bit of mental arithmetic. Bob Ward, famous for his half-day brain-melting sessions
-> Continue reading Join me at the EightKB virtual conference on 28 July 2021

analogue clock with a blur effect on the minutes

How SQL Server stores data types: DATETIMEOFFSET

If you’d like to check out the previous instalment in this series on storing dates and times, click here. I avoided mentioning this data type because I didn’t think a lot of people used it, and then my co-author William Assaf (blog | Twitter) told me on Twitter that he uses it, so here we
-> Continue reading How SQL Server stores data types: DATETIMEOFFSET

pink sofa chair near a body of water

Ambling through undocumented DBCC commands may result in boredom

There comes a time when we heed a certain call. The call is to avoid dangerous undocumented DBCC commands in SQL Server, especially those that bypass built-in protections. I’m looking directly at you DBCC WRITEPAGE. Besides, Paul Randal (blog | Twitter) has written strongly and authoritatively on the topic, striking fear in the hearts of everyone
-> Continue reading Ambling through undocumented DBCC commands may result in boredom

Green rope meshwork

Using indexed views? What is an imprecise or non-deterministic convert?

After last week’s post about using WITH (NOEXPAND) to query indexed views even on SQL Server Enterprise Edition, this week is a short but interesting side-road into deterministic values, and why it is important to get your data types correct. Longtime readers will know I care deeply about data types. Here is a T-SQL query
-> Continue reading Using indexed views? What is an imprecise or non-deterministic convert?

A filing cabinet drawer containing index cards

Using indexed views? Don’t forget this important tip.

Today after fighting with designing a view that let me create a clustered index on it (indexed views — also known as materialized views — are awesome in the right context!), I kept running into the same problem whenever I clicked Display Estimated Execution Plan in SQL Server Management Studio. The problem was that the plan was doing
-> Continue reading Using indexed views? Don’t forget this important tip.

Better SQL Server memory defaults in 2019

In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias. SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has
-> Continue reading Better SQL Server memory defaults in 2019

Better SQL Server CPU defaults in 2019

SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version. On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server
-> Continue reading Better SQL Server CPU defaults in 2019

Proposed SQL Server defaults: max server memory

(This post was updated on 5 February 2022.) 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
-> Continue reading Proposed SQL Server defaults: max server memory