SQL Server

Sniffed Nulls and Magic Numbers

I Sniff Your Milkshake Building off of A Simple Stored Procedure Pattern To Avoid, I wanted to talk about a similar one that I see quite often that is not nearly as clever as one would imagine. I goes something like this: If this variable is passed in as NULL, substitute it with something else.…
Read More
Brent Ozar reading

What’s New in SQL Server 2019 System Tables

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…
Read More

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

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…
Read More

Announcing SQL Server 2019

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!…
Read More

Wait Stats When VSS Snaps Are Slow

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…
Read More