Eleven SQL Server Checks, Ordered by Return on the Hour Spent

I spent a long stretch of my career as the person who got called when a database got slow. I kept finding the same problems: a foreign key nobody re-checked after a bulk load, a column nobody indexed, a query returning columns nobody reads, and a security default nobody changed.

This page is that list, collected from notes I first wrote between 2007 and 2018 and revised since. The engine has moved a long way in that time and most of these items have not needed to move with it.

Work it top to bottom. The ordering is roughly by return on the hour spent.

1. Find your untrusted and unindexed foreign keys first

This is the highest-yield check on the page and, in my experience, the one fewest people run.

A foreign key describes a relationship, and the query optimizer can use that description. When referential integrity has been lost or bypassed, the key becomes untrusted: still enforced for future writes, but no longer usable by the optimizer as proof when simplifying joins or estimating work. That can produce more expensive plans as the tables grow, although the effect depends on the query and workload.

Separately, a foreign key column with no useful index can force costly scans for common joins and referential checks. Inspect the actual workload and execution plans before adding one.

How keys become untrusted, in rough order of frequency:

• Bulk loading. Disabling foreign keys to speed a large load is normal. Re-enabling them without re-checking the data is also normal, and it is what leaves them untrusted.
• Troubleshooting under duress. Constraints get disabled to get production back up, and nobody goes back.
• Bulk inserts without the CHECK_CONSTRAINTS hint.
• NOT FOR REPLICATION keys, which are not enforced when the replication agent writes, and therefore cannot be trusted.

The fix, per key:

alter table [schema].[table] with check check constraint [keyname] ;

If that errors, you have orphans. Find them with:

dbcc checkconstraints('schema.table') ;

I keep a detection query that reports every foreign key in a database with its trust status, index status, NOT FOR REPLICATION flag, row counts, table sizes, and seek/scan activity, and generates the ready-to-run alter and create index statements for whatever it finds. It is long. The full thing is in Foreign Key Trust and Indexing.

Run it on any database you inherited. I have never run it on an unfamiliar production database and found nothing.

2. Index deliberately, and check what your indexes are doing

Review the indexes on your foreign key columns. That is item 24 on a list I wrote in 2007, refined by the workload evidence now available in SQL Server.

For more on indexing, I like Use The Index, Luke. Much of it applies across database engines.

I also check usage and plan history:

• Look at index usage, not just index existence. sys.dm_db_index_usage_stats tells you seeks, scans, lookups, and updates. An index with heavy writes and no recorded seeks, scans, or lookups is a removal candidate, not proof that it is redundant. Check it after a representative uptime window and confirm that it does not support a constraint or an infrequent critical workload before removing it.
• Use Query Store for history. Query Store keeps query text, plans, and runtime statistics across time and can also retain wait statistics, which makes plan regressions and workload shifts visible after the fact. It is enabled in READ_WRITE mode by default for new databases starting with SQL Server 2022; check upgraded and restored databases rather than assuming they inherited that setting.
• Learn the current plan tooling. Use actual execution plans and Extended Events against a representative workload, not only the query someone complained about. Extended Events replaces the deprecated SQL Trace and SQL Server Profiler features and has a graphical interface in SSMS.

3. Return less

I often find that an application is asking for much more data than it uses. Before tuning the query, check what the caller needs.

• Never select * to populate a simple control. Selecting every column from a table to fill a dropdown is a needless cost that becomes a serious one the moment a large column type is in the table.
• Use output parameters when you need one value, rather than returning an entire row.
• set nocount on in stored procedures that do not need row counts. It removes the row-count messages sent after each statement; the savings add up over repeated calls.
• Do not use select distinct as a substitute for correct joins. distinct on a query returning duplicates is usually a sign the join is wrong, and it hides the real problem behind a sort.
• Do not make several round trips where one would do. Latency per call dominates almost every “the database is slow” report I have ever investigated.
• Cache what does not change. If data changes a handful of times a day, do not fetch it on every page request.

4. Temp tables versus table variables

The comparison, briefly:

Both ordinary table variables and temp tables are backed by tempdb, and either can be processed from the data cache when memory is available. Treating one as high-I/O and the other as almost I/O-free is wrong; actual I/O and performance depend on the data volume, plan, memory pressure, and workload. Temp tables have full index support and optimizer statistics. Table variables can declare primary or unique constraints and, since SQL Server 2014, certain indexes inline, but they cannot add indexes after declaration and do not maintain distribution statistics.

Choose between them by what the optimizer needs:

• Use a table variable for a small, simple, short-lived set when the plan is stable and avoiding recompilation matters. Its narrow scope and shorter modification transactions can reduce recompilation, locking, and logging overhead.
• Use a temp table when plan quality depends on the intermediate data, especially when the set is reused, joins are complex, row counts vary, distribution matters, or the work needs full index support or parallel modification plans.
• Test the actual workload. There is no useful universal speed ranking, and row count alone is not enough to choose.

Starting with SQL Server 2019 at compatibility level 150, table variable deferred compilation replaces the old one-row guess with the row count observed on the first execution. It does not add distribution statistics or cause more recompilations. The plan is cached using that first row count, so later executions with very different populations can still get a poor plan.

If the requirement is genuinely no tempdb use or disk I/O, that is a different construct: a memory-optimized table variable. It is declared from a memory-optimized user-defined table type, must have an index, and requires a MEMORY_OPTIMIZED_DATA filegroup on SQL Server.

5. Get identity values right

There are three identity functions people commonly reach for, and only one of them is usually what they mean:

• @@IDENTITY gives the last identity generated for any table in the current session, across all scopes. A trigger firing an insert into a logging table will hand you that value instead of yours.
• IDENT_CURRENT('table') gives the last identity for a specific table, in any session and any scope. Another connection’s insert is visible to you.
• SCOPE_IDENTITY() gives the last identity generated in the current session and the current scope. This is the one to use when one scalar value is enough.

For a multi-row insert, use the OUTPUT clause instead. It returns the generated value for every affected row and can send those rows to the caller, a table, or a table variable:

insert into dbo.[widget] ([name])
output inserted.[widget_id]
values ('one'), ('two') ;

Do not infer source-row order from the output; SQL Server does not guarantee it.

If you have @@IDENTITY in a codebase that also has triggers, treat it as an open defect regardless of whether it has misbehaved yet.

6. Sort NULLs where you want them

SQL Server sorts NULLs first ascending, and T-SQL’s order by syntax has no NULLS FIRST or NULLS LAST modifier. When you want NULLs last, position them explicitly.

Coalesce to a correctly typed sentinel at the far end of the type’s range. For datetime2(7) and int columns:

order by
Coalesce([startDate], Convert(datetime2(7), '9999-12-31T23:59:59.9999999'))

order by
Coalesce([rocks], 2147483647)

Or sort on nullness first, which avoids inventing a sentinel value:

order by
case
when [columnName] is null
then 1
else 0
end,
[columnName]

Method two is the one to reach for by default. You do not have to know the type’s maximum, and it does not break when a real row legitimately holds the sentinel value. Method one is still worth knowing, because it composes into expressions where a case is awkward.

7. Dynamic SQL: sp_executesql, not exec

Keep values out of the statement text and pass them separately:

declare @accountId int = 42 ;
declare @stmt nvarchar(max) = N'
select [name]
from dbo.[account]
where [account_id] = @accountId ;' ;

exec sys.sp_executesql
 @stmt = @stmt,
 @params = N'@accountId int',
 @accountId = @accountId ;

sp_executesql supports parameterization, allowing the plan to be reused when parameter values change. Passing those values separately also keeps user input out of the statement text. Concatenating input into an exec string can expose the query to injection.

SQL Server 2025 adds the OPTIMIZED_SP_EXECUTESQL database-scoped configuration. When enabled, it serializes compilation of identical sp_executesql batches so concurrent sessions reuse the first cached plan instead of creating a compilation storm. It is off by default, so enable it only after confirming that concurrent compilation is the problem.

8. The security defaults nobody changes

All of these were on my 2007 list, and they all still turn up. The OWASP Top 10:2025 ranks Security Misconfiguration second and Injection fifth. Both are the subject of the defaults below.

• Do not use the sa account for application access, and do not use one account for everything. Every user, developer, and application gets its own account with access scoped by role. An injection flaw reached through sa can compromise the entire instance. A scoped account limits what the attacker can access.
• No blank passwords. Modern versions refuse this. Older ones did not, and older ones are still running.
• Validate every input that reaches the database. “That will never happen” is not a defense. OWASP notes that even a parameterized stored procedure can reintroduce injection when its T-SQL concatenates queries and data or executes hostile input with exec(). A security pass over one of my own admin panels in 2026 turned up an injection through unvalidated column names, which parameterization does not protect you from because an identifier is not a parameter. Allowlist identifiers; parameterize values.
• Do not store connection strings unencrypted in application configuration.
• Do not grant the application’s service account administrator rights in SQL Server because something did not work and that made it work.

9. Stored procedure and schema hygiene

• Do not prefix your procedures with sp_. SQL Server checks master first for that prefix, which costs a lookup on every call, and it puts your names in collision range of current and future system procedures.
• Always implement error handling. A procedure that fails silently will cost somebody an afternoon eventually.
• Keep business logic out of procedures, and keep DML out of application code. I use procedures for data access and application code for behavior.
• Do not over-normalize, and do not create lookup tables for domains with two or three fixed values.
• Use correct data types. Use char(1) rather than varchar(1) for a fixed one-character value, bit rather than varbinary for a boolean, and date or datetime2 rather than strings for dates. Each mismatch is small on its own and each makes validation, comparison, storage, or optimization harder than it needs to be.
• Set up the relationships that clearly exist. Referential integrity you declare is integrity the engine enforces and the optimizer can use. See item 1 for what happens when you have the declaration without the trust.
• Avoid a select inside an if where an exists or a set-based rewrite would do.

10. Watch what long transactions block

Keep transactions short, but do not attach that rule to an obsolete claim that select into blocks the whole system catalog. What a statement blocks depends on the operation, isolation level, hints, and whether optimized locking is enabled.

Without optimized locking, row and page locks needed for writes are normally held until the transaction ends. SQL Server 2025 can reduce that footprint by retaining a transaction ID lock instead, but it does not remove schema and object locks or make long transactions free. Move expensive preparation outside the transaction where correctness permits, inspect the actual blockers, and commit or roll back promptly.

11. Make your maintenance legible

An operational habit rather than a performance one, but it has saved me more hours than most of this page.

Categorize your scheduled jobs, and prefix the ones that exist for the database administrator’s benefit with something consistent. I used DBA: . It groups them visually for whoever inherits the server, and it makes them queryable:

select
 [Server] = Convert(varchar, ServerProperty('ServerName')),
 [Category] = c.[name],
 [Job Name] = j.[name],
 [Enabled] = j.[enabled],
 [Step #] = s.[step_id],
 [Step Name] = s.[step_name],
 [Subsystem] = s.[subsystem],
 [Command] = s.[command]
from
 msdb.dbo.sysjobs [j]
 inner join msdb.dbo.syscategories [c] on j.[category_id] = c.[category_id]
 inner join msdb.dbo.sysjobsteps [s] on j.[job_id] = s.[job_id]
where
 j.[name] like 'DBA: %'
order by
 c.[name] asc,
 j.[name] asc,
 s.[step_id] asc ;

Run this on an instance you have just taken over to see the maintenance jobs and their steps.

Revisiting the old notes

Rereading my 2007 notes to assemble this page was a strange experience. The security items have aged oddly: much better tooling, same mistakes. The temp-table guidance changed the most: ordinary table variables use tempdb too, temp tables are the safer choice when plan quality depends on the intermediate data, and deferred compilation has narrowed the gap since SQL Server 2019. The identity-function guidance still holds for scalar results, with OUTPUT covering multi-row work. The indexing guidance holds and is now much better documented than it was.

Some of the indexing concepts carry over to other engines; PostgreSQL’s CREATE INDEX documentation covers its implementation. The details still need checking for the engine you’re using.

I’m also seeing more SQL generated by ORMs and agents. Even when I can’t review every statement, I can check the schema, constraint trust, and privileges.

Changelog

• August 15, 2026: Qualified the foreign-key and index-usage guidance to require workload evidence rather than treating scans or unused indexes as automatic conclusions.
• August 5, 2026: Rechecked the guidance against SQL Server 2022 and 2025 documentation and corrected the sections on foreign-key trust, table variables, identity output, NULL sorting, dynamic SQL, security, and locking.
• July 16, 2026: First published.

Where this came from

• Don’t Do This in SQL - the original twenty-six-item list from 2007.
• Handling Sorts with Nulls - both ordering techniques.
• Generating SQL Agent Job Summaries - the job inventory query.
• Foreign Key Trust and Indexing - the full detection query with fix generation.
• Findings Become Features - the modern version, where a security lens found injection through unvalidated column names.