Performance Tuning

1-- Hiển thị Estimated Execution Plan
2SET SHOWPLAN_ALL ON;
3GO
4SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 29825;
5GO
6SET SHOWPLAN_ALL OFF;
7GO
8
9-- Hiển thị Actual Execution Plan (in SSMS: Ctrl + M)
10SET STATISTICS PROFILE ON;
11GO
12SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 29825;
13GO
14SET STATISTICS PROFILE OFF;
15
16-- XML format
17SET STATISTICS XML ON;
18GO
19SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 29825;
20GO
21SET STATISTICS XML OFF;

1-- Thick arrows = nhiều rows đi qua
2-- Yellow bang icon = warnings
3-- High % cost = bottleneck
4
5-- Ví dụ: Query với Table Scan
6SELECT * FROM Sales.SalesOrderHeader
7WHERE YEAR(OrderDate) = 2014;  -- Function on column prevents index use!
8
9-- Fix: Rewrite để sử dụng index
10SELECT * FROM Sales.SalesOrderHeader
11WHERE OrderDate >= '2014-01-01' AND OrderDate < '2015-01-01';

1-- I/O Statistics
2SET STATISTICS IO ON;
3GO
4
5SELECT c.CustomerID, c.PersonID, COUNT(*) AS OrderCount
6FROM Sales.Customer c
7JOIN Sales.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID
8GROUP BY c.CustomerID, c.PersonID;
9
10GO
11SET STATISTICS IO OFF;
12
13/*
14Output:
15Table 'SalesOrderHeader'. Scan count 1, logical reads 689
16Table 'Customer'. Scan count 1, logical reads 123
17
18Logical reads = pages read from cache
19Physical reads = pages read from disk (slow!)
20*/
21
22-- Time Statistics
23SET STATISTICS TIME ON;
24GO
25SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 29825;
26GO
27SET STATISTICS TIME OFF;
28
29/*
30Output:
31CPU time = 0 ms, elapsed time = 1 ms
32*/

1-- Tìm queries đang chạy lâu
2SELECT 
3    r.session_id,
4    r.status,
5    r.command,
6    r.cpu_time,
7    r.total_elapsed_time / 1000.0 AS elapsed_seconds,
8    r.logical_reads,
9    r.writes,
10    t.text AS query_text,
11    qp.query_plan
12FROM sys.dm_exec_requests r
13CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
14CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
15WHERE r.session_id > 50  -- User sessions
16ORDER BY r.total_elapsed_time DESC;
17
18-- Top resource-consuming queries (từ cache)
19SELECT TOP 20
20    total_worker_time / execution_count AS avg_cpu,
21    total_logical_reads / execution_count AS avg_reads,
22    total_elapsed_time / execution_count AS avg_duration,
23    execution_count,
24    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
25        ((CASE qs.statement_end_offset
26            WHEN -1 THEN DATALENGTH(st.text)
27            ELSE qs.statement_end_offset
28        END - qs.statement_start_offset)/2) + 1) AS query_text
29FROM sys.dm_exec_query_stats qs
30CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
31ORDER BY avg_cpu DESC;

1-- ❌ Bad: Function prevents index usage
2SELECT * FROM Customer.Customer
3WHERE UPPER(Email) = 'TEST@EMAIL.COM';
4
5-- ✅ Good: Store normalized or use computed column
6SELECT * FROM Customer.Customer
7WHERE Email = 'test@email.com';  -- Case-insensitive by collation
8
9-- Or add computed column with index
10ALTER TABLE Customer.Customer
11ADD EmailLower AS LOWER(Email) PERSISTED;
12
13CREATE INDEX IX_Customer_EmailLower ON Customer.Customer(EmailLower);
14
15-- ❌ Bad: Function on date
16SELECT * FROM Sales.[Order]
17WHERE YEAR(OrderDate) = 2024 AND MONTH(OrderDate) = 1;
18
19-- ✅ Good: Use range
20SELECT * FROM Sales.[Order]
21WHERE OrderDate >= '2024-01-01' AND OrderDate < '2024-02-01';

1-- ❌ Bad: Returns unnecessary columns
2SELECT * FROM Sales.SalesOrderHeader
3WHERE CustomerID = 29825;
4
5-- ✅ Good: Only needed columns
6SELECT SalesOrderID, OrderDate, Status, TotalDue
7FROM Sales.SalesOrderHeader
8WHERE CustomerID = 29825;
9
10-- Benefits:
11-- 1. Less data to transfer
12-- 2. Can use covering index
13-- 3. Clear documentation of what's needed

1-- ❌ Potentially slow với large subquery
2SELECT * FROM Production.Product
3WHERE ProductID IN (
4    SELECT ProductID FROM Sales.SalesOrderDetail
5);
6
7-- ✅ Better: EXISTS (stops at first match)
8SELECT * FROM Production.Product p
9WHERE EXISTS (
10    SELECT 1 FROM Sales.SalesOrderDetail sod
11    WHERE sod.ProductID = p.ProductID
12);
13
14-- Note: Modern optimizer often handles this, but EXISTS is clearer intent

1-- Let optimizer choose join order (usually best)
2SELECT c.CustomerID, COUNT(*) AS OrderCount
3FROM Sales.Customer c
4JOIN Sales.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID
5GROUP BY c.CustomerID;
6
7-- Force join order (rare, only when optimizer fails)
8SELECT c.CustomerID, COUNT(*) AS OrderCount
9FROM Sales.Customer c
10INNER JOIN Sales.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID
11OPTION (FORCE ORDER);
12
13-- Join hints (use sparingly)
14SELECT *
15FROM Sales.Customer c
16INNER HASH JOIN Sales.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID;
17-- LOOP JOIN, MERGE JOIN, HASH JOIN

1-- ❌ Old method: OFFSET can be slow for large pages
2SELECT *
3FROM Sales.SalesOrderHeader
4ORDER BY SalesOrderID
5OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY;
6-- Must scan 100,000 rows first!
7
8-- ✅ Better: Keyset pagination
9SELECT TOP 20 *
10FROM Sales.SalesOrderHeader
11WHERE SalesOrderID > @LastSalesOrderID  -- From previous page
12ORDER BY SalesOrderID;
13-- Direct seek to starting point

1-- SQL Server's missing index suggestions
2SELECT 
3    ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) AS improvement_measure,
4    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_' + 
5        REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''), ', ', '_'), '[', ''), ']', '') AS create_statement,
6    mid.equality_columns,
7    mid.inequality_columns,
8    mid.included_columns,
9    migs.user_seeks,
10    migs.user_scans,
11    migs.avg_user_impact
12FROM sys.dm_db_missing_index_group_stats migs
13JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
14JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
15WHERE mid.database_id = DB_ID()
16ORDER BY improvement_measure DESC;

1-- Find indexes with low usage
2SELECT 
3    OBJECT_NAME(i.object_id) AS TableName,
4    i.name AS IndexName,
5    i.type_desc,
6    us.user_seeks,
7    us.user_scans,
8    us.user_lookups,
9    us.user_updates,
10    -- Ratio of reads vs writes
11    CASE WHEN (us.user_seeks + us.user_scans + us.user_lookups) = 0 
12         THEN 0 
13         ELSE ROUND(us.user_updates * 1.0 / (us.user_seeks + us.user_scans + us.user_lookups), 2)
14    END AS WriteToReadRatio
15FROM sys.indexes i
16LEFT JOIN sys.dm_db_index_usage_stats us 
17    ON i.object_id = us.object_id AND i.index_id = us.index_id
18WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
19    AND i.index_id > 1  -- Non-clustered only
20ORDER BY (us.user_seeks + us.user_scans + us.user_lookups) ASC;

1-- Reorganize or Rebuild based on fragmentation
2DECLARE @TableName NVARCHAR(256), @IndexName NVARCHAR(256), @Fragmentation FLOAT;
3
4DECLARE index_cursor CURSOR FOR
5SELECT 
6    QUOTENAME(OBJECT_SCHEMA_NAME(ps.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ps.object_id)),
7    i.name,
8    ps.avg_fragmentation_in_percent
9FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
10JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
11WHERE ps.avg_fragmentation_in_percent > 10
12    AND ps.page_count > 1000
13    AND i.name IS NOT NULL;
14
15OPEN index_cursor;
16FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation;
17
18WHILE @@FETCH_STATUS = 0
19BEGIN
20    IF @Fragmentation < 30
21        EXEC('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REORGANIZE');
22    ELSE
23        EXEC('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD');
24    
25    FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation;
26END
27
28CLOSE index_cursor;
29DEALLOCATE index_cursor;

1-- Statistics giúp Query Optimizer estimate row counts
2-- Outdated statistics = bad execution plans
3
4-- Xem statistics của table
5SELECT 
6    s.name AS StatisticsName,
7    STATS_DATE(s.object_id, s.stats_id) AS LastUpdated,
8    s.auto_created,
9    s.user_created
10FROM sys.stats s
11WHERE s.object_id = OBJECT_ID('Sales.SalesOrderHeader');
12
13-- Xem chi tiết statistics
14DBCC SHOW_STATISTICS('Sales.SalesOrderHeader', 'IX_SalesOrderHeader_CustomerID');

1-- Update statistics cho 1 table
2UPDATE STATISTICS Sales.SalesOrderHeader;
3
4-- Update với FULLSCAN (more accurate, slower)
5UPDATE STATISTICS Sales.SalesOrderHeader WITH FULLSCAN;
6
7-- Update all statistics in database
8EXEC sp_updatestats;
9
10-- Auto update settings
11ALTER DATABASE AdventureWorks2019 
12SET AUTO_UPDATE_STATISTICS ON;
13
14ALTER DATABASE AdventureWorks2019 
15SET AUTO_CREATE_STATISTICS ON;

1-- Problem: First execution caches plan for specific parameter
2-- Subsequent executions may use suboptimal plan
3
4CREATE PROCEDURE Sales.usp_GetOrdersByCustomer
5    @CustomerID INT
6AS
7BEGIN
8    SELECT * FROM Sales.SalesOrderHeader
9    WHERE CustomerID = @CustomerID;
10END;
11
12-- Customer 1 has 1 order, Customer 29825 has 10,000 orders
13-- Plan cached for first customer may be wrong for second
14
15-- Solutions:
16
17-- 1. RECOMPILE (for every execution)
18CREATE PROCEDURE Sales.usp_GetOrdersByCustomer
19    @CustomerID INT
20AS
21BEGIN
22    SELECT * FROM Sales.SalesOrderHeader
23    WHERE CustomerID = @CustomerID
24    OPTION (RECOMPILE);
25END;
26
27-- 2. OPTIMIZE FOR
28CREATE PROCEDURE Sales.usp_GetOrdersByCustomer
29    @CustomerID INT
30AS
31BEGIN
32    SELECT * FROM Sales.SalesOrderHeader
33    WHERE CustomerID = @CustomerID
34    OPTION (OPTIMIZE FOR (@CustomerID = 29825));  -- Typical value
35END;
36
37-- 3. Local variable (loses parameter sniffing)
38CREATE PROCEDURE Sales.usp_GetOrdersByCustomer
39    @CustomerID INT
40AS
41BEGIN
42    DECLARE @LocalCustID INT = @CustomerID;
43    
44    SELECT * FROM Sales.SalesOrderHeader
45    WHERE CustomerID = @LocalCustID;
46END;

1-- ❌ Problem: Data type mismatch causes conversion
2-- CustomerID is INT, but passed as VARCHAR
3
4DECLARE @CustID VARCHAR(10) = '29825';
5SELECT * FROM Sales.Customer WHERE CustomerID = @CustID;
6-- Implicit conversion! Index may not be used efficiently
7
8-- ✅ Solution: Use correct data type
9DECLARE @CustID INT = 29825;
10SELECT * FROM Sales.Customer WHERE CustomerID = @CustID;
11
12-- Check for implicit conversions in execution plan
13-- Look for CONVERT_IMPLICIT warning

1-- Table Variables: No statistics, always estimated 1 row
2DECLARE @Orders TABLE (OrderID INT);
3INSERT INTO @Orders SELECT SalesOrderID FROM Sales.SalesOrderHeader;
4
5SELECT * FROM @Orders o
6JOIN Sales.SalesOrderDetail sod ON o.OrderID = sod.SalesOrderID;
7-- May use wrong join strategy!
8
9-- Temp Tables: Have statistics, better for large data
10CREATE TABLE #Orders (OrderID INT);
11INSERT INTO #Orders SELECT SalesOrderID FROM Sales.SalesOrderHeader;
12
13-- Update statistics
14UPDATE STATISTICS #Orders;
15
16SELECT * FROM #Orders o
17JOIN Sales.SalesOrderDetail sod ON o.OrderID = sod.SalesOrderID;
18-- Better execution plan
19
20DROP TABLE #Orders;
21
22-- Rule: < 100 rows → Table Variable, > 100 rows → Temp Table

1-- Enable Query Store
2ALTER DATABASE AdventureWorks2019
3SET QUERY_STORE = ON;
4
5-- Configure Query Store
6ALTER DATABASE AdventureWorks2019
7SET QUERY_STORE (
8    OPERATION_MODE = READ_WRITE,
9    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
10    DATA_FLUSH_INTERVAL_SECONDS = 900,
11    INTERVAL_LENGTH_MINUTES = 60,
12    MAX_STORAGE_SIZE_MB = 1000,
13    QUERY_CAPTURE_MODE = AUTO
14);
15
16-- View Query Store data
17SELECT 
18    q.query_id,
19    qt.query_sql_text,
20    rs.count_executions,
21    rs.avg_duration / 1000.0 AS avg_duration_ms,
22    rs.avg_logical_io_reads,
23    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms
24FROM sys.query_store_query q
25JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
26JOIN sys.query_store_plan p ON q.query_id = p.query_id
27JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
28ORDER BY rs.avg_duration DESC;
29
30-- Force a specific plan
31EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1;

1-- Blocking sessions
2SELECT 
3    blocking_session_id,
4    session_id,
5    wait_type,
6    wait_time,
7    wait_resource
8FROM sys.dm_exec_requests
9WHERE blocking_session_id <> 0;
10
11-- TempDB usage
12SELECT 
13    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
14    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
15    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb
16FROM sys.dm_db_file_space_usage;
17
18-- Wait statistics
19SELECT TOP 10
20    wait_type,
21    waiting_tasks_count,
22    wait_time_ms,
23    signal_wait_time_ms
24FROM sys.dm_os_wait_stats
25WHERE wait_type NOT LIKE '%SLEEP%'
26ORDER BY wait_time_ms DESC;