Optimizing SQL Server Performance: 5 Essential Best Practices Every DBA Should Know

In the fast-paced world of enterprise applications, database performance can make or break a business. When queries slow down, user experience plummets, resources spike, and frustration builds.

Whether you are managing a core banking system, an e-commerce platform, or an internal enterprise application, ensuring optimal performance from your Microsoft SQL Server (MSSQL) is critical.

In this guide, we’ll explore 5 essential best practices you can implement today to speed up your queries, optimize resource usage, and keep your SQL Server running at peak efficiency.

1. Indexing Strategy: Beyond the Basics

Indexes are the foundation of database performance. However, having too many indexes can be just as harmful as having none at all, as every INSERT, UPDATE, and DELETE requires SQL Server to maintain them.

• Focus on High-Cost Queries: Use the Database Engine Tuning Advisor or query execution plans to identify missing indexes on high-frequency, high-cost queries.
• Include Non-Key Columns: Use INCLUDE columns in non-clustered indexes to cover queries completely without creating wide composite keys.
• Monitor Index Fragmentation: Regularly check for index fragmentation. Reorganize indexes with fragmentation between 5% to 30%, and rebuild them if fragmentation exceeds 30%.

SQL
-- Example: Checking fragmentation of indexes in a database
SELECT 
    dbsps.name AS TableName, 
    idxt.name AS IndexName, 
    frag.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') frag
INNER JOIN sys.tables dbsps ON frag.object_id = dbsps.object_id
INNER JOIN sys.indexes idxt ON frag.object_id = idxt.object_id AND frag.index_id = idxt.index_id
WHERE frag.avg_fragmentation_in_percent > 10;

2. Eliminate Parameter Sniffing Issues

Parameter sniffing happens when SQL Server caches an execution plan based on the first parameter value passed during compilation. If subsequent calls use drastically different parameters, that cached plan might lead to terrible performance.

• Use OPTIMIZE FOR or RECOMPILE: If a specific stored procedure consistently suffers from bad execution plans due to varying data distribution, consider using query hints:

SQL
CREATE PROCEDURE GetTransactionDetails 
    @AccountID INT
AS
BEGIN
    SELECT * FROM Transactions 
    WHERE AccountID = @AccountID
    OPTION (RECOMPILE);
END;

Keep Statistics Up to Date: Ensure your statistics are updated regularly (AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS should generally remain enabled).

3. Write SARGable Queries (Search Argument Able)

Writing non-SARGable queries forces SQL Server to perform a full table scan rather than an index seek, destroying performance on large datasets.

• Avoid Functions on Indexed Columns: Wrapping columns in functions prevents SQL Server from utilizing indexes.
  • Bad: WHERE YEAR(TransactionDate) = 2026
  • Good: WHERE TransactionDate >= '2026-01-01' AND TransactionDate < '2027-01-01'
• Avoid Leading Wildcards in LIKE:
  • Bad: WHERE AccountName LIKE '%Maharjan' (Triggers full scan)
  • Good: WHERE AccountName LIKE 'Maharjan%' (Can utilize an index)

4. Master TempDB Configuration and Maintenance

TempDB is a global resource used for temporary tables, table variables, row versioning, and sorting operations. A misconfigured TempDB is one of the most common bottlenecks in SQL Server.

• Multiple Data Files: Align the number of TempDB data files with your CPU core count (up to 8 files, then add more if contention persists). Ensure all files are equal in size to allow proportional fill.
• Pre-allocate File Sizes: Avoid default auto-growth settings that cause latency spikes. Size your TempDB files appropriately during off-peak hours based on historical workload requirements.

5. Leverage Extended Events for Monitoring

Stop relying heavily on heavy legacy tools like SQL Server Profiler, which can add significant overhead to your server.

• Lightweight Tracking: Use Extended Events (XEvents) to capture deadlock graphs, slow-running queries, and execution bottlenecks with minimal performance impact.
• Proactive Alerts: Set up alerts for long-running transactions and high CPU/Memory utilization so you can catch issues before users report them.

Conclusion

Performance tuning is not a one-time event; it’s a continuous journey of monitoring, analyzing, and refining. By implementing these five best practices—optimizing your indexing, addressing parameter sniffing, writing SARGable queries, tuning TempDB, and using Extended Events—you will drastically improve your SQL Server’s responsiveness and stability.