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 easy way to handle UTF-8 in a .NET application when dealing with SQL Server 2019

A short post this week. On a mailing list recently, someone noticed that a .NET application writing to SQL Server did not have the expected behaviour with UTF-8 collation and data types. To refresh our memories, UTF-8 is newly supported in SQL Server 2019, and provides potential savings of up to 50% when storing strings,
-> Continue reading The easy way to handle UTF-8 in a .NET application when dealing with SQL Server 2019

A short Azure SQL Database Edge explainer

On Thursday May 2nd, 2019, Microsoft announced a new edition of SQL Server targeting Internet of Things (IoT) edge devices. That means SQL Server can now run almost anywhere. From the Venturebeat article: It supports ARM and x64-based edge gateways and machines, and offers low-latency analytics that combine data streaming and time-series data, with in-database
-> Continue reading A short Azure SQL Database Edge explainer

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

Using a home-grown Azure Blob Storage solution for SQL Server backups

I’m here for the small organizations, the shops that can’t afford expensive solutions to maintain their environments. I’m here for them because that’s me: the one-person consultancy. I’ve built stuff that’s useful to me, and then made it available for free on GitHub. My first SQL Saturday session, way back in 2015, was the public
-> Continue reading Using a home-grown Azure Blob Storage solution for SQL Server backups

The free SQL to Excel Export tool

Earlier this month I released a new, free tool to export SQL Server query data to Excel files without needing Excel. It also installs stored procedures and runs them, if that’s what you desire. Go ahead and play with it. I’ll wait. There was some positive interest on Twitter, for which I am very grateful, and
-> Continue reading The free SQL to Excel Export tool

dbForge Studio logo

Database modelling in a post-SSMS world: dbForge Studio

A few months ago, Microsoft announced that SQL Server Management Studio (SSMS) will no longer include the visual Database Diagrams feature from v18.0 onward. When releasing a new version of a product, Microsoft has the luxury of referencing usage statistics, and obviously this is an underused feature of SSMS. I was a little disappointed at
-> Continue reading Database modelling in a post-SSMS world: dbForge Studio

Some databases may show a NULL collation in the sys.databases DMV

While working on my Swart’s Ten Percent Rule post last week, I needed to test the Windows version of the script on my SQL Server 2016 instance. Just before removing all the databases, I noticed something interesting when querying the sys.databases Dynamic Management View (DMV). Because the process I had come up with involved setting
-> Continue reading Some databases may show a NULL collation in the sys.databases DMV

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