70) Performance tuning - SQL SERVER

1 . Steps for  SQL SERVER 

 

Sample Slow Running Query (SQL Server Dialect)

SQL

SELECT 
    r.RegionName,
    s.StoreName,
    DATEPART(year, o.OrderDate) AS OrderYear,
    DATEPART(month, o.OrderDate) AS OrderMonth,
    COUNT(DISTINCT o.OrderID) AS TotalOrders,
    SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
    AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
FROM 
    Sales.Orders o
INNER JOIN 
    Sales.OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN 
    Sales.Stores s ON o.StoreID = s.StoreID
INNER JOIN 
    Sales.Regions r ON s.RegionID = r.RegionID
WHERE 
    r.RegionName = 'North America'
    AND o.OrderDate >= '2025-01-01' 
    AND o.OrderDate < '2025-02-01'
GROUP BY 
    r.RegionName,
    s.StoreName,
    DATEPART(year, o.OrderDate),
    DATEPART(month, o.OrderDate);

Steps to Resolve

Step 1: Set statistics time and I/O to capture baseline performance

• Action: Enable client-side statistics tracking in SQL Server Management Studio (SSMS) or use SET STATISTICS TIME and SET STATISTICS IO commands to measure execution duration and disk page reads before making any changes.

• SQL Command:

  SQL

  SET STATISTICS TIME ON;
  SET STATISTICS IO ON;
  
  -- Execute the slow running query above
  SELECT 
      r.RegionName,
      s.StoreName,
      DATEPART(year, o.OrderDate) AS OrderYear,
      DATEPART(month, o.OrderDate) AS OrderMonth,
      COUNT(DISTINCT o.OrderID) AS TotalOrders,
      SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
      AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
  FROM 
      Sales.Orders o
  INNER JOIN 
      Sales.OrderDetails od ON o.OrderID = od.OrderID
  INNER JOIN 
      Sales.Stores s ON o.StoreID = s.StoreID
  INNER JOIN 
      Sales.Regions r ON s.RegionID = r.RegionID
  WHERE 
      r.RegionName = 'North America'
      AND o.OrderDate >= '2025-01-01' 
      AND o.OrderDate < '2025-02-01'
  GROUP BY 
      r.RegionName,
      s.StoreName,
      DATEPART(year, o.OrderDate),
      DATEPART(month, o.OrderDate);
  

• Sample Output (Messages Tab in SSMS):

  Plaintext

  Table 'Orders'. Scan count 1, logical reads 124500, physical reads 4120, page server reads 0...
  Table 'OrderDetails'. Scan count 1, logical reads 310200, physical reads 9500...
  SQL Server Execution Times:
     CPU time = 4210 ms,  elapsed time = 5120 ms.
  

• Explanation of Output: The baseline execution shows an elapsed time of 5.120 seconds alongside massive logical and physical disk reads (124,500 reads on Orders and 310,200 reads on OrderDetails), indicating heavy table scans.

Step 2: Run and analyze the execution plan using Estimated and Actual Execution Plans

• Action: Toggle on Include Actual Execution Plan (Ctrl + M) in SSMS and run the query to inspect the graphical execution tree.

• SQL Command: (Same slow query executed with execution plan tracking enabled)

• Sample Output (Graphical Execution Plan breakdown nodes):

  Plaintext

  [Clustered Index Scan (Table: Sales.Orders)] ---> (Cost: 52%)
        |
  [Clustered Index Scan (Table: Sales.OrderDetails)] ---> (Cost: 38%)
        |
  [Stream Aggregate / Compute Scalar] ---> (Cost: 10%)
  

• Explanation of Output: The graphical layout highlights expensive Clustered Index Scans on the base tables because no secondary non-clustered indexes support the date filter range or join keys.

Step 3: Create targeted non-clustered and covering indexes

• Action: Build structural non-clustered indexes with included columns to eliminate table scans and substitute them with index seek operations.

• SQL Command:

  SQL

  CREATE NONCLUSTERED INDEX IX_Regions_RegionName 
  ON Sales.Regions (RegionName) 
  INCLUDE (RegionID);
  
  CREATE NONCLUSTERED INDEX IX_Stores_RegionID 
  ON Sales.Stores (RegionID) 
  INCLUDE (StoreID, StoreName);
  
  CREATE NONCLUSTERED INDEX IX_Orders_Store_Date 
  ON Sales.Orders (StoreID, OrderDate) 
  INCLUDE (OrderID);
  
  CREATE NONCLUSTERED INDEX IX_OrderDetails_Covering 
  ON Sales.OrderDetails (OrderID) 
  INCLUDE (Quantity, UnitPrice, Discount);
  

• Sample Output (sys.indexes metadata verification query):

  SQL

  SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('Sales.Orders');
  

  Plaintext

  name                      type_desc
  PK_Orders                 CLUSTERED
  IX_Orders_Store_Date      NONCLUSTERED
  

• Explanation of Output: Confirms that the custom non-clustered index IX_Orders_Store_Date has been successfully created alongside the primary clustered index.

Step 4: Update table statistics using FULLSCAN

• Action: Force SQL Server's query optimizer to recalculate distribution statistics with full accuracy so it builds optimal join plans.

• SQL Command:

  SQL

  UPDATE STATISTICS Sales.Orders WITH FULLSCAN;
  UPDATE STATISTICS Sales.OrderDetails WITH FULLSCAN;
  UPDATE STATISTICS Sales.Stores WITH FULLSCAN;
  UPDATE STATISTICS Sales.Regions WITH FULLSCAN;
  

• Sample Output (Messages Tab):

  Plaintext

  Command completed successfully.
  

• Explanation of Output: Confirms stats collection is updated across all underlying database tables, ensuring the Cost-Based Optimizer accurately estimates row counts.

Step 5: Verify index fragmentation and memory buffer status

• Action: Verify that the buffer cache is healthy and index pages are not heavily fragmented.

• SQL Command:

  SQL

  SELECT 
      db_name(database_id) AS DatabaseName,
      COUNT(row_count) * 8 / 1024 AS BufferSizeMB
  FROM sys.dm_os_buffer_descriptors
  WHERE database_id = DB_ID()
  GROUP BY database_id;
  

• Sample Output:

  Plaintext

  DatabaseName    BufferSizeMB
  SalesDB         4096
  

• Explanation of Output: Confirms that memory pages are cached correctly in the buffer pool (4096 MB), minimizing physical disk read bottlenecks.

Step 6: Rewrite the query using Common Table Expressions (CTEs)

• Action: Restructure the query using a CTE to isolate region parameters and date ranges upfront, allowing the optimizer to process smaller sets of rows before performing distinct counts and aggregations.

• Tuned SQL Query:

  SQL

  WITH FilteredOrders AS (
      SELECT 
          o.OrderID,
          o.StoreID,
          DATEPART(year, o.OrderDate) AS OrderYear,
          DATEPART(month, o.OrderDate) AS OrderMonth
      FROM Sales.Orders o
      INNER JOIN Sales.Stores s ON o.StoreID = s.StoreID
      INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID
      WHERE r.RegionName = 'North America'
        AND o.OrderDate >= '2025-01-01' 
        AND o.OrderDate < '2025-02-01'
  ),
  AggregatedSales AS (
      SELECT 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID,
          COUNT(DISTINCT fo.OrderID) AS TotalOrders,
          SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
          AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
      FROM FilteredOrders fo
      INNER JOIN Sales.OrderDetails od ON fo.OrderID = od.OrderID
      GROUP BY 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID
  )
  SELECT 
      r.RegionName,
      s.StoreName,
      a.OrderYear,
      a.OrderMonth,
      a.TotalOrders,
      a.TotalRevenue,
      a.AverageOrderValue
  FROM AggregatedSales a
  INNER JOIN Sales.Stores s ON a.StoreID = s.StoreID
  INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID;
  

• Sample Output Execution Metrics (Messages Tab):

  SQL

  SET STATISTICS TIME ON;
  SET STATISTICS IO ON;
  -- [Run Fully Tuned Query]
  

  Plaintext

  Table 'Orders'. Scan count 1, logical reads 1240, physical reads 0...
  Table 'OrderDetails'. Scan count 150, logical reads 3400, physical reads 0...
  SQL Server Execution Times:
     CPU time = 140 ms,  elapsed time = 180 ms.
  

• Explanation of Output: Logical reads drop drastically from hundreds of thousands down to a few thousand, and the elapsed execution time drops from 5.120 seconds down to 0.180 seconds, proving a massive 28x performance improvement.