SQL Server  

🚀 Top 30 Proven Ways to Optimize SQL Server Database Performance (with Examples)

A slow SQL Server database can cripple an application. Performance issues are often due to inefficient queries, bad indexing, or unoptimized schema design. In this guide, I’ll share 30 proven techniques with real SQL examples to help you speed up queries and optimize slow tables.

1. Choose the Right Data Types

Avoid oversized types.

-- Bad
CREATE TABLE Users (
    UserId BIGINT,         -- Too large if only 1M users
    Name NVARCHAR(MAX)     -- Heavy if names are short
);

-- Good
CREATE TABLE Users (
    UserId INT,            -- INT is enough for < 2B users
    Name NVARCHAR(100)     -- Limit size
);

2. Avoid SELECT *

-- Bad
SELECT * FROM Orders;

-- Good
SELECT OrderId, OrderDate, CustomerId FROM Orders;

3. Use Proper Indexing

-- Add index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId 
ON Orders (CustomerId);

4. Covering Indexes

-- Covers CustomerId and includes OrderDate to avoid key lookup
CREATE NONCLUSTERED INDEX IX_Orders_Cover
ON Orders (CustomerId)
INCLUDE (OrderDate);

5. Index Maintenance

-- Rebuild fragmented indexes
ALTER INDEX ALL ON Orders REBUILD;

6. Update Statistics

-- Keeps optimizer up-to-date
UPDATE STATISTICS Orders;

7. Normalize Data Wisely

-- Instead of repeating customer info in Orders
-- Create separate Customer table
CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    Name NVARCHAR(100)
);

8. Partition Large Tables

-- Partition Orders table by year
CREATE PARTITION FUNCTION OrderPartition (INT)
AS RANGE LEFT FOR VALUES (2019, 2020, 2021);

9. Use Proper Joins

-- Bad (Cartesian join)
SELECT * FROM Customers, Orders;

-- Good
SELECT c.Name, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON c.CustomerId = o.CustomerId;

10. Eliminate Unused Indexes

-- Check unused indexes
SELECT * FROM sys.dm_db_index_usage_stats;

11. Optimize WHERE Clauses

-- Bad (prevents index use)
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;

-- Good
SELECT * FROM Orders 
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';

12. Parameterize Queries

-- Instead of building dynamic queries
DECLARE @CustomerId INT = 101;
SELECT * FROM Orders WHERE CustomerId = @CustomerId;

13. Avoid Cursors

-- Bad: Cursor
DECLARE cur CURSOR FOR SELECT OrderId FROM Orders;

-- Good: Set-based
UPDATE Orders SET Status = 'Processed' WHERE Status = 'Pending';

14. Use Stored Procedures

CREATE PROCEDURE GetOrdersByCustomer @CustomerId INT
AS
BEGIN
    SELECT * FROM Orders WHERE CustomerId = @CustomerId;
END;

15. Optimize TempDB

-- Best practice: multiple TempDB files with equal size
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, SIZE = 256MB);

16. Batch Processing

-- Delete in batches to avoid locking
WHILE 1=1
BEGIN
   DELETE TOP (5000) FROM Logs WHERE LogDate < '2024-01-01';
   IF @@ROWCOUNT = 0 BREAK;
END

17. Avoid Functions in SELECT

-- Bad
SELECT YEAR(OrderDate) FROM Orders;

-- Good
SELECT OrderDate FROM Orders WHERE OrderDate >= '2025-01-01';

18. Proper Primary Keys

-- Bad
PRIMARY KEY (Email, Phone)

-- Good
PRIMARY KEY (UserId) -- surrogate key

19. Foreign Key Indexing

-- Add index for join performance
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

20. Optimize ORDER BY

-- Add index to speed up sort
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate DESC);

21. Use EXISTS Instead of IN

-- Bad
SELECT * FROM Orders WHERE CustomerId IN (SELECT CustomerId FROM Customers);

-- Good
SELECT * FROM Orders o WHERE EXISTS (SELECT 1 FROM Customers c WHERE c.CustomerId = o.CustomerId);

22. Remove Unnecessary DISTINCT

-- Bad
SELECT DISTINCT CustomerId FROM Orders;

-- Good
SELECT CustomerId FROM Orders GROUP BY CustomerId;

23. Archive Old Data

-- Move old orders to archive
INSERT INTO OrdersArchive SELECT * FROM Orders WHERE OrderDate < '2020-01-01';
DELETE FROM Orders WHERE OrderDate < '2020-01-01';

24. Use Compression

-- Enable row compression
ALTER TABLE Orders REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);

25. Check Execution Plans

-- In SSMS: Ctrl + M before executing query
-- Identify table scans and missing indexes

26. Apply Query Hints Carefully

SELECT * FROM Orders WITH (NOLOCK);

27. Optimize Network I/O

-- Bad
SELECT * FROM Orders;

-- Good
SELECT TOP 10 OrderId, OrderDate FROM Orders ORDER BY OrderDate DESC;

28. Use Memory-Optimized Tables

CREATE TABLE Orders_InMemory
(
    OrderId INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    CustomerId INT
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

29. Implement Caching

-- Instead of hitting DB every time, use app caching like Redis
-- Example: Cache top products for 5 min

30. Monitor and Profile Performance

-- Use DMVs
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count AS avg_time,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text)
      ELSE qs.statement_end_offset END
      - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_time DESC;

🔑 Final Thoughts

These 30 techniques with examples cover everything from schema design to indexing, query optimization, and maintenance. Think of SQL optimization as preventive care for your database — the earlier you tune, the better your performance will scale.

Membership not found