SQL Server’s really clever about a lot of things. It’s not super clever about YEAR() when it comes to indexes, even using SQL Server 2016 – but you can either make your TSQL more clever, or work around it with computed columns.
Patching isn’t fun. It’s time consuming, thankless, and easy to break stuff.
But you can’t skip it. For critical systems, you need to subscribe to patch lists for SQL Server and review issues that are fixed regularly.
SQL Server offers two flavors of optimistic locking for traditional disk-based tables: Read Committed Snapshot Isolation (RCSI), and Snapshot Isolation. They are each great tools to reduce blocking and make applications faster, particularly in databases that have lots of tiny reads and writes that need to be quick, or mixed-use patterns (lots of little reads and writes + larger reporting queries).
Both isolation levels are available in SQL Server Standard Edition and Web Edition as well as Enterprise. That’s right, they’re cheap and easy. Each of them are controlled by database level settings, and default to disabled for new user databases when you install SQL Server and leave the default settings on the model database.
When should you pick one or the other? And when might you enable both?
Never, ever, disable backups to fix a performance problem.
If you’re not having a performance crisis, you might look at that sentence and say, “Wow, that’s super dumb! Who would ever do that?”
Well, a lot of people, actually. It’s pretty common.
Sometimes when SQL Server gets slow, developers and DBAs find that the problem is blocking. After lots of work to identify the query or queries which are the blockers, frequently one idea is to add ROWLOCK hints to the queries to solve the problem or to disable PAGE locks on the table. This often backfires - here’s why.
Before you do all the work to map out a complex sliding window table partitioning scheme for your SQL Server tables, here’s the top five questions I’d think through carefully:
Some folks think they need partitioning for performance– but it really shines as a data management feature. Just because you’ve got tables with millions of rows in them doesn’t necessarily mean that partitioning will make queries faster. Make sure you’ve worked through traditional indexing and query re-writes first. Partitioning is lots of work, so don’t skip this question.
SQL Server 2016’s new Query Store feature has an option that looks for “regressed” query plans.
But does it catch “bad” parameter sniffing?
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.