SQL Server Performance: Field Notes from 2021

I wrote these over about seven weeks in the spring of 2021, one post at a time, mostly because I kept answering the same questions and wanted somewhere to point people. They were written against SQL Server 2016 through 2019.

I’ve merged them into one page and left the substance alone. Some of it is version-specific and I’ve flagged those spots. The rest is the part that doesn’t move: you measure before you tune, you don’t bring back data you don’t need, and the query is rarely the only thing that’s wrong.

---

Measuring, before you touch anything

A production query starts running slow and users aren’t happy. The application takes forever to load because the query behind it isn’t optimal. A finance report was fine until yesterday and now times out. The user only cares about how long their screen takes.

Tuning SQL Server isn’t a set-and-forget configuration. It shifts with new releases, new business requests, and whatever the last person did.

sp_WhoIsActive tells me what’s happening right now. Run it bare and you get active queries, how long they’ve been running, the actual SQL text, login and application info, CPU and logical reads. I usually add two parameters:

sp_WhoIsActive @get_plans = 1, @get_additional_info = 1

That gets you a query plan and a useful extra column. Worth knowing whether you’re looking at an actual or estimated plan: you’re watching something that is running or about to run.

Graphical execution plans. The optimizer picks a plan based on estimated cost. SSMS gives you estimated and actual; I use actual, because it’s generated after execution and shows you where the optimizer’s choices didn’t work out. Read right to left, top to bottom, and pay attention to when arrow thickness changes. Live in the tooltips. Rows returned versus estimated rows, operator cost, execution counts. Cardinality estimates drive everything the optimizer does.

SentryOne’s free Plan Explorer (now SolarWinds Plan Explorer) highlights the expensive operators and lets you sort by whatever measure you care about.

For ad hoc work, SET STATISTICS IO and SET STATISTICS TIME. The first gives you logical and physical reads and scan counts, the second gives you elapsed milliseconds.

Extended Events, not Profiler. I held onto Profiler longer than I should have. XE captures more and is more flexible. My usual events are rpc_completed, sp_statement_completed, and sql_statement_completed.

Hot cache or cold? Comparing the same code across environments is misleading when production’s plan cache is warm and development’s is not. For a warm comparison, run the query twice and measure the second run. For cold, there’s DBCC DROPCLEANBUFFERS, which you should not casually run in production.

Temporary stored procedures are underused. Change management won’t let you create objects, or the developer lacks CREATE permission, or you just want to test before altering the real thing. Copy the body of the problem procedure into a temp proc and troubleshoot there. Local needs a # prefix, global needs ##, and they live in tempdb so you can call them from any database.

One specific use: when someone says it works fine in their management studio but the application is slow, don’t pull the body out and run it ad hoc. That changes the plan you get. Use a temporary stored procedure instead.

---

Query design

The rule underneath all of these is the same. Don’t bring back data you don’t need.

SET NOCOUNT ON to avoid the network overhead of row counts you’re not using.

Use EXISTS rather than COUNT(*) to test for existence. COUNT(*) scans everything; EXISTS stops at the first match.

Watch implicit conversions. Compatible but different data types get converted silently and your performance goes with them.

Avoid non-sargable predicates. LIKE '%something', arithmetic on a column (WHERE Rate * 10 = 20), functions on a column (WHERE SUBSTRING(Name,1,1) = 'A'). All of these stop the optimizer using your indexes and you scan the table.

Table variables versus temp tables: my rule of thumb was under 20 rows, table variable is fine, and you get no statistics so no recompiles. Above that, temp table.

Qualify object owners explicitly. It avoids name resolution delays at compile time.

Minimize cursors. SQL is set-based and row-by-row work fights the engine. Your I/O pays for it.

Inline your UDFs. Scalar UDFs can stop the optimizer going parallel. Pull the logic into the query and accept that code reuse loses this round. SQL Server 2019 claims to inline scalar UDFs automatically, which was new when I wrote this and is worth verifying against whatever version you’re on.

Plan reuse. Cached plans reused repeatedly give you consistent performance. Parameterize instead of running ad hoc; sp_executesql is your friend. Plenty still causes recompilation: schema changes, statistics changes, explicit recompile hints. Use extended events to find out why and reduce what you can.

Hints. I don’t recommend them unless you know exactly what you’re doing. Trust the engine by default. When the optimizer keeps producing a bad plan, something like MAXDOP at the query level is defensible, but test it and understand the downstream effect, and know that behavior can change between versions. NOLOCK lets your application read dirty data, so picture users seeing duplicate rows from a transaction that then rolls back.

Transactions. Keep the scope short so queries aren’t blocking each other. Use TRY/CATCH and never leave a transaction open.

---

Beyond the query

Writing good queries is table stakes. There are more knobs.

Design. Unnecessary referential checks slow an OLTP system down, and column data type choices matter more than people expect. Review table structures, indexes, and constraints as access patterns change. Ask whether a logging database really needs all those foreign keys. Minimize triggers, because their cost is hidden. Columnstore indexes are a given in the warehouse, and OLTP systems can benefit from nonclustered columnstore too.

Memory. SQL Server uses memory for the buffer pool and caches, and leaving max server memory at the default puts the OS under stress. It’s a beast that holds onto what it has. Set min and max explicitly, and with stacked instances make sure the total doesn’t exceed what the server actually has.

MAXDOP and Cost Threshold for Parallelism. On a NUMA machine SQL Server picks how many processors to use per parallel plan. MAXDOP can be set at server, database, or query level, and they override in that order going down. No restart required.

Default MAXDOP is 0, meaning any parallel plan uses every processor, and you’ll meet CXPACKET waits. Don’t set it to 1 either, since that suppresses parallel plans entirely. The guidance at the time was cores per NUMA node or 8, whichever is lower. Task Manager’s performance tab tells you your NUMA node count.

Cost Threshold for Parallelism is server-level and defaults to 5, which is far too low. Test somewhere in the 20 to 50 range. The point is to stop small queries going parallel so the processors are free for the expensive ones.

Power plan. Not a SQL setting at all, but switch Windows from Balanced to High Performance. It’s one of the cheapest wins available.

Trace flags. Useful for troubleshooting, dangerous left on, because they change behavior.

DBCC TRACEON (3226, -1)   -- global
DBCC TRACEON (3226)       -- session
OPTION (QUERYTRACEON 4199) -- single query
DBCC TRACESTATUS          -- what's currently on

Audit what’s active and find out why each one is there. You will find flags nobody remembers enabling. From 2016 onward several old flags do nothing, because the behavior moved to database-scoped configuration: query optimizer fixes, parameter sniffing, legacy cardinality estimation.

Statistics. Stale statistics wreck cardinality estimates and therefore plans. Leave auto-create and auto-update on. For large tables use AUTO_UPDATE_STATISTICS_ASYNC so queries aren’t blocked waiting on a stats refresh, and enable incremental statistics on partitioned tables. A scheduled agent job to find and refresh stale stats is worth having.

Index maintenance. Indexes help until they don’t. Drop the ones the engine no longer uses, create the ones it’s asking for, and measure fragmentation before deciding how to fix it, because the level determines whether you reorganize or rebuild.

Never turn on AUTO_CLOSE or AUTO_SHRINK. Both are resource hogs and every story I’ve heard about them ends badly.

Compress your backups. And put data and log files on separate disks, which brings me to a horror story from 2013 involving a certain person who had everything on the C drive.

---

Choosing an index type

Indexes are on-disk structures that reduce I/O. Too many and the database grows and writes slow down. Too few and reads crawl. Picking well needs an understanding of your workload and of what’s available.

Rowstore indexes store data by row, on a B-tree.

Clustered rowstore. Rows stored and sorted by the index key. Usually the primary key, though it doesn’t have to be. One per table. A table without one is a heap, stored unordered.

Nonclustered rowstore. Stores the key values plus a row locator. On a clustered table that locator points at the clustered key; on a heap it points at the row.

Covering. A nonclustered index carrying everything a query needs, so included columns let you skip the key lookup.

Filtered. A nonclustered index with a WHERE clause, which shrinks the B-tree. Good for sparse columns or when you only ever query a small subset.

Rowstore wins when queries are selective and looking for specific values, which is most of OLTP.

Columnstore doesn’t use a B-tree. Data is stored by column in rowgroups, which compresses well and is much faster for analytical scans.

Clustered columnstore is the physical storage for the whole table and is the standard for large warehouse fact tables.

Nonclustered columnstore is a secondary index on a rowstore table, which is how you support analytics running against an OLTP structure.

You can mix them. An updateable nonclustered columnstore index on a rowstore table, or a nonclustered rowstore index on a columnstore table.

---

Files and filegroups

Three file types, one optional.

• Primary (.mdf) holds startup information and pointers to the others.
• Transaction log (.ldf) holds what recovery depends on.
• Secondary (.ndf) is optional, for user data.

Filegroups are either Primary or user-defined. Primary is the default and holds all system objects, and unless you say otherwise your user objects land there too.

Spreading data files across filegroups and across disks distributes I/O and buys you parallel access. Two things worth doing: put large, I/O-heavy tables in their own filegroup on their own disk, and keep data and log files on separate dedicated I/O paths.

All reads and writes happen at the page level. The page is the basic unit of storage.

---

Partitioning

SQL Server partitions a table or index into chunks that can live on different filegroups, and it stays a single logical structure as far as queries and users are concerned. From 2016 SP1 this stopped being Enterprise-only.

When you need it. You have a very large table holding data going back further than anyone looks, users want a rolling twelve months, and some analyst occasionally pulls three years. Also, yes, you should have had a retention policy, and it’s still not too late to write one.

What you get. Loading and removing large volumes via partition switch, which requires your indexes to be partition-aligned. Faster loads into warehouse fact tables. Partition elimination, so a query filtering on the partitioning key only touches relevant partitions, and when joining partitioned tables you want the join columns to be the partitioning columns. Maintenance you can target one partition at a time.

Building one. Create filegroups and files, create a partition function mapping values to partitions, create a partition scheme mapping partitions to filegroups, then create or alter the table on that scheme. RIGHT range includes the lower boundary, LEFT includes the upper.

ALTER DATABASE PartitionDB ADD FILEGROUP Partition_FG1;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP Partition_FG2;
GO

ALTER DATABASE PartitionDB
ADD FILE (
  NAME = Partition_F1,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Partition_F1.ndf',
  SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB
) TO FILEGROUP Partition_FG1;
GO

CREATE PARTITION FUNCTION PF_PartitionbyMonth (datetime)
AS RANGE RIGHT FOR VALUES (N'2021-01-01T00:00:00.000', N'2021-02-01T00:00:00.000');

CREATE PARTITION SCHEME PS_PartitionbyMonth
AS PARTITION PF_PartitionbyMonth TO (Partition_FG1, Partition_FG2, [Primary]);

CREATE TABLE PartitionTable (
  PartitioningColumn datetime PRIMARY KEY,
  ColumnDescription varchar(250)
) ON PS_PartitionbyMonth (PartitioningColumn);

Adding [Primary] at the end of the scheme catches rows that don’t fall into any defined range, which is what saves you the day you forget to extend the function.

Automate the extension. You can estimate an initial range, but new ranges and filegroups need adding over time and that shouldn’t be manual. A scheduled agent job that finds partition functions running out of range and extends them is the right answer.

To see what you’ve actually got:

SELECT DISTINCT o.name AS table_name, rv.value AS partition_range,
       fg.name AS file_groupName, p.partition_number, p.rows AS number_of_rows
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id
       AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values rv ON f.function_id = rv.function_id
       AND p.partition_number = rv.boundary_id
WHERE o.object_id = OBJECT_ID('PartitionTable');

---

Deleting a lot of rows

Two things decide the approach: how many rows, and what percentage of the table that is. Take a table with 100 million rows.

Ten thousand rows. Just delete them. A single statement won’t trouble the transaction log much, keeping in mind DELETE is fully logged.

DELETE FROM dbo.Test WHERE Id <= 10000;

Ten million rows. Still a small percentage, but a single statement will grow your log and invite lock escalation. Batch it.

SET NOCOUNT ON;
DECLARE @Counter INT = 1;
DECLARE @BatchSize INT = 10000;
WHILE @Counter > 0
BEGIN
    BEGIN TRANSACTION;
        DELETE TOP (@BatchSize)
        FROM dbo.Test
        WHERE Id <= 10000000;
        SET @Counter = @@ROWCOUNT;
    COMMIT TRANSACTION;
END

Wrapping each batch in its own transaction means that if you have to stop, the completed batches are already committed and you restart with less to do.

Seventy-five million rows, three quarters of the table. Stop deleting and start keeping.

SELECT Id, Description, Date
INTO dbo.TestStaging
FROM dbo.Test
WHERE ID >= 75000000;

DROP TABLE dbo.Test;

EXEC sp_rename 'dbo.TestStaging', 'Test';
-- then recreate constraints, indexes, permissions, and your triggers

All of it. TRUNCATE needs stronger permissions, at least ALTER, and logs far less because it only records the deallocated extents. It also resets IDENTITY, which DELETE doesn’t. To clear the table but keep the current identity value:

BEGIN TRANSACTION
    DECLARE @Identity AS INT = IDENT_CURRENT('dbo.Test') + 1;
    TRUNCATE TABLE dbo.Test;
    DBCC CHECKIDENT('dbo.Test', RESEED, @Identity);
COMMIT TRANSACTION

You can’t truncate a table with foreign keys pointing at it, or one underneath an indexed view.

---

SSIS data flow performance

SSIS maps source column types to its own, then allocates memory buffers to hold incoming rows for transformation. How well your transformations use and reuse those buffers is most of your performance story.

Transformations block in three ways:

• Non-blocking. No waiting. Derived Column, Lookup, Multicast.
• Partially blocking. Waits until enough rows accumulate. Merge, Union All.
• Blocking. Waits for every row. Aggregate, Sort, Fuzzy Lookup.

Synchronous components are generally faster because they reuse buffers.

Two properties matter for buffer sizing. DefaultMaxBufferSize defaults to 10MB and, in the versions I was running, maxed at 100MB; later releases raised that ceiling considerably. DefaultMaxBufferRows defaults to 10,000. Keep them roughly in proportion so your row count actually fits the size you set, and enable logging on the data flow task to watch the BufferSizeTuning event. Or set AutoAdjustBufferSize and let it size buffers to match your configured row count.

Beyond that: avoid asynchronous transformations, and let the database engine do work it’s better at, sorting especially. Never pull more than you need, columns as well as rows. Don’t pick a source table from the dropdown, write a query with the filter and sort you want. Keep column data types as small as they can be, so more rows fit per buffer. And use parallel execution in control flow if you have the processors for it.

---

SSRS report tuning

I’m not going to defend still using SSRS. It delivers crisp paginated reports that users can download in whatever format they want, which is a real job, and I use other tools when the job is slicing across many dimensions.

The complaint is always the same. The report takes forever and renders a blank screen.

Start with the ExecutionLog3 view in the ReportServer database. Use that one, not ExecutionLog or ExecutionLog2, which exist for backward compatibility.

USE ReportServer;
SELECT *
FROM ExecutionLog3
WHERE ItemPath = '/reportpath/reportname'
ORDER BY TimeStart DESC;

Three columns tell you where the time went.

TimeDataRetrieval. High means the source is slow, so go open the report query and start asking the usual questions. Also worth avoiding having the warehouse and reporting services on the same box, memory contention being worse than the network hop.

TimeProcessing. High means the report is doing too much after the data arrives. Expressions, paging, and layout drive this. Every expression is evaluated whether or not it’s visible, and [&TotalPages] is the classic offender because a thousand-page report has to paginate fully before page one renders. Use page breaks, or the whole report processes before the user sees anything. Skip complex aggregation in data regions, and don’t group every column in a tablix you’re only using to show detail. Push aggregation and sorting into the query. Watch KeepTogether on tablix members, and TextAlign, CanGrow, and CanShrink when you have a lot of text boxes. Be careful with subreports inside a tablix; drill-through is usually the better answer.

TimeRendering. Format drives this more than anything. CSV, XML, and HTML are cheap. PDF and Excel want real CPU and memory.

Also open the XML in the AdditionalInfo column. There’s a lot in there, including which processing engine ran and what memory it used.

---

SSMS, configured properly

Most of these live under Tools → Options and take a minute each.

AutoRecover (Environment → AutoRecover). Set how often it saves and how long it keeps. You’ll be grateful exactly once, and that’s enough.

Startup (Environment → Startup). “Open Object Explorer and query window,” because that’s what you came to do.

Line numbers and IntelliSense (Text Editor → Transact-SQL).

Statistics on by default (Query Execution → Advanced). Turn on SET STATISTICS TIME and SET STATISTICS IO so you stop typing them.

Scripting defaults (SQL Server Object Explorer → Scripting). Object existence checks, permissions, whatever you want consistently.

Custom colors per environment. Under Options → Connection Properties when connecting. The cheapest insurance there is against running something in production that you meant for dev.

Registered Servers, grouped sensibly, if you connect to many instances or deploy the same objects across replicas.

Object filtering, for databases with hundreds of procedures where Object Explorer gives up.

Drag and drop from Object Explorer, because typing object names is beneath us.

Built-in reports. Right-click a database or the server, Reports → Standard Reports. useful for health checks and troubleshooting.

Object Explorer Details (F7) is the underused one. Select a node and you get create date, row count, schema, space used, without writing a query. You can search for objects within a database or across every database on the instance, and script or delete many objects at once.

---

The scripts worth having

None of these are mine. All of them were on my machine.

• sp_WhoIsActive, Adam Machanic. Real-time diagnostics. sp_who2 is for amateurs.
• First Responder Kit, Brent Ozar. Deadlocks, missing indexes, and the general question of why everything is slow.
• SQL Server Maintenance Solution, Ola Hallengren. Backups, index rebuilds, integrity checks. Sleep is better than babysitting CHECKDB.
• Troubleshooting scripts, Erik Darling.
• dbatools, Chrissy LeMaire. Close to a command-line SSMS. Migrations, best practice checks, automation.
• Opserver, Stack Exchange. Free monitoring for SQL Server and more.

Test in a non-production environment. Your dev environment is not a sacrificial lamb, but it’s closer to one than prod is.

---

Who I learned this from

There is a generation of SQL Server people who published relentlessly and for free, and most of what’s above traces back to one of them.

Brent Ozar on more or less everything. SQLPerformance for the deep performance work. Aaron Bertrand, whose batched delete post is the one I linked when I wrote about large deletes. Pinal Dave at SQLAuthority for quick answers. Erik Darling. Paul Randal and the rest of SQLSkills for internals. Allan Hirt on high availability. MSSQLTips and Pragmatic Works for the broad middle.

On the BI side: Power BI Tips, SQLBI, Radacad, Stacia Varga, Melissa Coates.

Most of them are still going, which says something about the difference between publishing and posting.