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

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

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

Canadian SQL Saturday speaking events

I am pleased to announce that I will be speaking at two separate SQL Saturdays in March and April of this year. The first event is in Victoria (the capital city of British Columbia, located on Vancouver Island), and the second is in Edmonton, the capital city of Alberta. I will be presenting the same
-> Continue reading Canadian SQL Saturday speaking events