SQL Server Performance Bible

Back in February 2010 I kept a running list of SQL Server performance tips. Fifteen-plus years later almost all of them still hold — SQL Server is bigger and smarter, but it still punishes the same mistakes. Below is the original list, corrected where it was wrong and annotated with what changed since. The quirks worth knowing about in 2026 are called out inline.

1. Profile remotely, not on the server itself

This is still rule number one: running Profiler or Performance Monitor on the same box as SQL Server steals CPU and I/O from the very thing you are measuring. Run your monitoring from a separate workstation or a dedicated monitoring server.

One modern note: SQL Server Profiler has been deprecated in favor of Extended Events. XEvent sessions run server-side, are dramatically lighter than a Profiler trace, and you query their output with T-SQL instead of watching it scroll by over RDP.

2. The key counters worth watching

The System Monitor (PerfMon) counters I watched back then, with current commentary:

• Memory: Pages/Sec — should sit near zero on a dedicated SQL Server. Brief spikes during backups and restores are normal.
• PhysicalDisk: Avg. Disk sec/Read and Avg. Disk sec/Write — the latency counters I would reach for first today. Sustained reads or writes above ~20 ms deserve investigation.
• PhysicalDisk: % Disk Time and Current Disk Queue Length — still useful for spotting saturation, though on modern storage (SSD/NVMe) queue lengths mean far less than latency.
• System: Processor Queue Length — sustained values above the CPU core count mean the CPUs are saturated.
• SQLServer: General Statistics — User Connections — remember that one connection does not equal one user. A single user can hold many connections; connection pooling multiplies the confusion.
• SQLServer: Access Methods — Page Splits/sec — still my favorite early-warning sign. Lots of splits means fill factor is too aggressive or your indexes need maintenance.
• SQLServer: Buffer Manager — Page Life Expectancy — the counter I would add to the original list. Repeated PLE crashes mean the working set does not fit in memory.
• SQLServer: Buffer Manager — Buffer cache hit ratio — useful, but it is an average since the last restart, so a sudden memory problem barely moves it.
• SQLServer: Memory Manager — Target vs. Total Server Memory — if Total is well below Target, SQL Server wants more memory and is not getting it.

Two things to know about counter names: named instances show up as MSSQL$InstanceName rather than SQLServer, and the semantic meaning of some counters shifts on modern hardware. Brent Ozar’s classic PerfMon counters tutorial is still the best starting point, and Microsoft’s System Monitor documentation covers the mechanics.

3. Use SQL Server performance condition alerts

SQL Server Agent performance condition alerts still work exactly this way: define a condition (say, user connections over 100), and when it trips, the Agent can email you, page an operator, or kick off a job. In an era where everything has a cloud dashboard, this built-in, zero-cost path is underrated.

4. Indexed views: get the SET options right

The original tip was garbled, so let me fix it: to create (and to query!) an indexed view, these session options must be ON — ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER — and NUMERIC_ROUNDABORT must be OFF. If any client connects with the wrong settings, its queries silently degrade to scanning the base tables instead of using the view’s index. The full requirements are in Microsoft’s create indexed views documentation.

5. Avoid cursors; think in sets

Still true, and it is the habit that separates developers who struggle with SQL Server from those who do not. Cursors process rows one at a time; set-based logic lets the optimizer do what it is built for. The 2010 alternatives are all still valid: WHILE loops, temp tables, derived tables, correlated subqueries, CASE expressions, or multiple queries. In 2026 I would add table-valued parameters, which replaced the old comma-split-and-LOOP-join pattern for passing lists into procedures.

The temp-table-in-a-transaction warning also still holds directionally, though the mechanism aged: syscolumns/sysindexes/syscomments are SQL Server 2000-era compatibility views, long gone. Creating temp tables inside a transaction no longer serializes everyone the way it once did — but a DDL statement inside a long-running transaction still holds metadata locks (Sch-M) that can queue behind it, so create your temp tables before the transaction starts. Cheap insurance, same as it ever was.

6. WHERE clause operators, ranked by speed

From fastest to slowest:

• =
• >, >=, <, <=
• LIKE
• <>

Still worth internalizing, with one caveat the original list missed: a leading wildcard LIKE '%term' cannot seek an index at all, while LIKE 'term%' usually can. The operator matters less than whether your predicate is sargable — wrapping a column in a function (WHERE YEAR(order_date) = 2026) throws away index seeks just as surely as an anti-sargable operator does.

7. Prefer CHECK constraints over triggers

Unchanged advice: CHECK constraints are faster than triggers for the same rule, and they document the intent right on the table. One 2026 note — this is precisely why the deprecation of inline scalar UDFs in CHECK constraints hurts: Microsoft is steering people toward triggers for anything non-trivial. Use the constraint while it works; measure before adding a trigger.

8. Normalize your OLTP schema

The normalization argument survives intact, and each reason is still accurate:

• Less redundant data means less work for the engine.
• Fewer NULLs means cheaper predicates and indexes (especially WHERE clauses).
• Narrower tables fit more rows per page, boosting read performance.
• Less T-SQL around non-normalized data means less code to run and to get wrong.
• More tables means more clustered indexes, the most useful index type SQL Server has.
• Fewer columns means fewer indexes per table, which keeps INSERT/UPDATE/DELETE overhead down.

(The usual disclaimer applies: normalize OLTP, then denormalize deliberately — via indexed views or reporting structures — where read performance demands it.)

9. No screensavers on production servers

Archived as period humor, but the underlying principle aged well: nothing unneeded runs on a production box. Modern version — no browsers, no agents you do not need, no desktop apps doing background indexing on a machine whose job is to serve your database.

10. Be deliberate about temp tables

The 2010 advice was “avoid temp tables,” which oversold it. Temp tables are fine — they are cached and reused across executions all the time — they just are not free: they live in tempdb, and they cost a round of statistics. What I would say today:

• Rewrite the procedure so a standard query or set-based statement does the job without one.
• Consider a table variable for small row counts (with the caveat that it has no statistics, so large table variables mislead the optimizer — the exact trap the old advice warned about in reverse).
• Use a derived table or CTE when the logic fits inline.
• Use a correlated subquery where a scalar value is enough.
• Use a table-valued parameter instead of a temp table plus string-splitting.

The derived table sample from the original post, cleaned up:

SELECT *
FROM (SELECT *
      FROM dbo.customers) AS dt_customers;

And that is where the original list ended, promising “tips go on and on, keep tuned.” Fifteen years later, this is the tuning: the counters changed names, Profiler became Extended Events, screensavers became background services — but profile remotely, think in sets, and keep predicates sargable, and SQL Server will keep rewarding you.