Patch your SQL Server instance today
On 14 February 2023, Microsoft released updates for all supported versions of SQL Server in the form of a General Distribution Release (GDR). A GDR is an out-of-band update that usually includes bug fixes and/or…
Zeroing out a transaction log file does not use zeroes
I’ve been doing SQLskills training recently, and Paul Randal (blog | Twitter) reminded our class that zeroing out a transaction log file does not use zeroes (0x00). Well, not since SQL Server 2014 at any…
Deprecated and discontinued features in SQL Server
My co-authors and I recently wrapped up the book SQL Server 2019 Administration Inside Out, which should be hitting the shelves in the next week or two. At the end of Chapter 1 I have…
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…
Max Server Memory and SQL Server 2016 Service Pack 1
(This post was last updated on 1 August 2022.) Everything changed for SQL Server Standard Edition on 16 November 2016, and how memory limits work. On that day, a slew of Enterprise Edition features made…
Temporal Tables and Hidden Period Columns
In my November 2015 post, An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean, I wrote: The HIDDEN property is optional and will hide these columns from a standard SELECT statement for…
A look back, and a look forward
Time flies. My father used to wear a t-shirt that claimed, “When you’re over the hill, you pick up speed.” I’m turning 40 in a few days. I still feel like a teenager in many…
Wait For Service Pack 1
Conventional wisdom tells us that when Microsoft releases a new version of any server product, we should wait until Service Pack 1 before deploying it to production. This hasn’t been true for a while now, since Microsoft…
How to make your T-SQL thirty percent faster
Last week, I mentioned a feature in SQL Server, and now in Azure SQL Database, called Memory-Optimized Table-Valued Parameters, which makes use of In-Memory OLTP structures to work around a dependency on tempdb, and thereby…
The Data Migration Assistant
I’ve written quite a lot about Azure SQL Database recently, but that doesn’t mean I’ve forgotten about the on-premises version of SQL Server. What could be better than Microsoft announcing a new tool for upgrading…
Should I move my on-premises database to Azure SQL Database?
This week is a shorter post than last week, but it answers a very important question: Should I move my on-premises database to Azure SQL Database? The short answer is: It depends. Firstly, why do…
Basic Availability Groups in SQL Server 2016
One of the new features in SQL Server 2016 Standard Edition, which may appeal to users of the deprecated Database Mirroring feature, is Basic Availability Groups, a variation of Availability Groups found in the Enterprise…
Temporal Tables – When To Use Them
Last edited on 18 December 2019. This is the final part of my Temporal Table series. You can read parts one, two and three here. Last week I demonstrated how to modify temporal tables in…
Modifying Temporal Tables – A Primer
This is part three of the Temporal Tables series. You can read parts one and two here. Last week I demonstrated how temporal tables in SQL Server 2016 work. If you have implemented a history…
Temporal Tables Under The Covers
(This is a more technical post than last week. If you are not familiar with SQL Server internals, I will not be upset if you skip it. Otherwise, let’s dive in.) Last week I introduced…