SQL Server

Sometimes good intentions lead to big problems. Imagine a system where page splits were thought to be causing a big enough performance problem that it made sense to change the fill factor setting of a lot of indexes. Now imagine you thought the fill factor setting was for how much free space you wanted when…

Have Availability Groups Gotten Easier Lately?

Last Updated December 3, 2018

Always On Availability Groups, High Availability, SQL Server

Easy Lover I don’t blog a lot about AGs. If we’re being honest (and I do try to be honest with you, dear reader), I just like performance tuning topics way more. When new features get announced for AGs, some of you may ooh and aah, but not me. I Make A Face I don’t…

Tales From Overindexing: Too Many One Column Indexes

Last Updated November 23, 2018

Execution Plans, Indexing, SQL Server

Master Plan Sometimes you see a query, and it’s hitting one table, and then the query plan looks like a vase full of spaghetti. Usually, there’s a view involved. Usually. Sometimes, there’s just really weird indexing. Here’s A Thing, Look At It Promises This is a query against one table. No views, no functions, no…

Psst – it’s holiday card time. Update your address.

Last Updated November 19, 2018

Last year’s Christmas card We’re getting ready to send out our annual holiday cards to folks who shopped with us this year. If you’d like to get one, make sure you set up your billing address in your account to reflect your current address. Happy holidays!

What’s New in SQL Server 2019: Faster Functions

Last Updated June 25, 2020

SQL Server 2019, T-SQL

A while back, we talked you through a public whitepaper about how Microsoft was working on making user-defined functions go faster. Now that the preview of SQL Server 2019 is out, you can start getting your hands on Froid, the performance-boosting feature. Here’s the documentation on it – let’s see how it works. Using the…

Index Tuning Week: Fixing Nonaligned Indexes On Partitioned Tables

Last Updated September 28, 2018

Unquam Oblite This post will not change your life, but it will help me remember something. When you decide to partition a table to take advantage of data management features, because IT IS NOT A PERFORMANCE FEATURE, or you have an existing partitioned table that takes advantage of data management features, because IT IS NOT…

What’s New in SQL Server 2019 System Tables

Last Updated September 24, 2018

The free SQL Server 2019 preview download is out, and here are quite a few things to check out that aren’t documented yet: New System Objects Starting with new stored procedures: sys.sp_add_feature_restriction sys.sp_autoindex_cancel_dta sys.sp_autoindex_invoke_dta sys.sp_cloud_update_blob_tier sys.sp_configure_automatic_tuning sys.sp_diagnostic_showplan_log_dbid sys.sp_drop_feature_restriction sys.sp_execute_remote sys.sp_force_slog_truncation sys.sp_internal_alter_nt_job_limits sys.sp_rbpex_exec_cmd sys.sp_set_distributed_query_context sys.sp_set_session_resource_group sys.sp_showinitialmemo_xml sys.sp_xa_commit sys.sp_xa_end sys.sp_xa_forget sys.sp_xa_forget_ex sys.sp_xa_init sys.sp_xa_init_ex sys.sp_xa_prepare sys.sp_xa_prepare_ex sys.sp_xa_recover sys.sp_xa_rollback…

What’s New in SQL Server 2019’s sys.messages: More Unannounced Features

Last Updated September 24, 2018

The system table sys.messages holds error messages that SQL Server needs to return to end users. When a new build comes out, I love checking this table to see what features Microsoft has coded, but isn’t quite ready to announce yet. When SQL Server 2017 came out, that’s how I unmasked snapshot materialized views, a…

What’s New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)

Last Updated May 26, 2020

Parameter Sniffing, SQL Server 2019, T-SQL

For over a decade, SQL Server’s handling of table variables has been legendarily bad. I’ve long used this Stack Overflow query from Sam Saffron to illustrate terrible cardinality estimation: Transact-SQL declare @VoteStats table (PostId int, up int, down int) insert @VoteStats select PostId, up = sum(case when VoteTypeId = 2 then 1 else 0 end), down…

What’s New in SQL Server 2019: Adaptive Memory Grants

Last Updated May 26, 2020

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2019

When you run a query, SQL Server guesses how much memory you’re going to need for things like sorts and joins. As your query starts, it gets an allocation of workspace memory, then starts work. Sometimes SQL Server underestimates the work you’re about to do, and doesn’t grant you enough memory. Say you’re working with…

Leaked: SQL Server 2019 Big Data Clusters Introduction Video

Last Updated February 2, 2025

Psst – you’re probably not supposed to see this yet, but look what @WalkingCat found: https://x.com/h0x0d/status/1042979511074086913 What the video says Growing volumes of data create deep pools of opportunity for those who can navigate it. SQL Server 2019 helps you stay ahead of the changing time by making data integration, management, and intelligence easier and more…

A Simple Stored Procedure Pattern To Avoid

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

Get Yourself Together This is one of the most common patterns that I see in stored procedures. I’m going to simplify things a bit, but hopefully you’ll get enough to identify it when you’re looking at your own code. Here’s the stored procedure: Transact-SQL CREATE OR ALTER PROCEDURE dbo.you_are_not_clever ( @cd DATETIME NULL) AS BEGIN…

Quirks When Working With Extended Events To Track Locks

Last Updated September 13, 2018

Worse Than Mobile Browsing I have a love/hate relationship with Extended Events. Yes, they’re powerful. Yes, you can track interesting things. But they’re just not intuitive, like, 10 years on. Part of the problem is that the narrative from Microsoft has been that it’s a full replacement for Profiler and Traces. How many monitoring tool…

Can Forced Parameterization Go Wrong?

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

App Like That If you’ve got the kind of application that sends bare-assed strings to SQL Server, you may end up with a weird choice. Brent will be sitting on one shoulder telling you to use Forced Parameterization. I’ll be on the other shoulder asking if you’ve really thought this whole thing through while you…

Announcing SQL Server 2019

Last Updated September 10, 2018

TEASE Who Let The Docs Out? Ignite must be coming up. If you head over to Microsoft’s GitHub repo, you can peruse around for stuff updated recently. Maybe you’ll create an account. Maybe you’ll start contributing to open source projects. Maybe you’ll quietly slip into a world of solitude for days on end. Happy Saturday!…

Locks Taken During Indexed View Modifications

Last Updated September 2, 2018

Locking, Blocking, and Isolation Levels, SQL Server

Frankenblog This post has been nagging at me for a while, because I had seen it hinted about in several other places, but never written about beyond passing comments. A long while back, Conor Cunningham wrote: This same condition applies to indexed view maintenance, but I’ll save that for another day :). AFAIK he hasn’t…

Foundational Material: Microsoft SQL Server Book and Blogs From The Past

Last Updated August 13, 2018

What Did Dinosaurs Watch On TV? These are some of my favorite books and blogs from Microsoft from the way-back machine. I can’t say every bit of information is still 100% true and should be followed to the letter, but hey, that’s what happens. This is stuff I consider foundational material, though. I’ve learned a…

Wait Stats When VSS Snaps Are Slow

Last Updated August 8, 2018

Deus Redux A while back I wrote about the Perils of VSS Snaps. After working with several more clients having similar issues, I decided it was time to look at things again. This time, I wanted blood. I wanted to simulate a slow VSS Snap and see what kind of waits stats I’d have to…

How Trace Flag 2335 Affects Memory Grants

Last Updated January 8, 2019