01. The Situation

RetailCo’s analytics team had built a set of Power BI dashboards over two years. The dashboards tracked sales performance, inventory levels, customer segments, and regional profitability across 14 store locations. They were used daily by the leadership team and weekly by store managers across the business.

The problem had crept in slowly. In the early months after launch, dashboards loaded in 8 to 12 seconds. As data volume grew and new metrics were added, load times rose. By the time the team reached out, opening the main executive dashboard took between 40 and 55 seconds. Some users had stopped opening the dashboards at all and were asking the analytics team to export static screenshots instead.

The dashboards ran in DirectQuery mode, meaning Power BI sent SQL queries to the database every time a user loaded a page or applied a filter. Every second of database query time showed up directly as dashboard load time. The database itself, Microsoft SQL Server 2019, had not had a performance review since the dashboards went live.

02. Diagnosing the Problem

The first step was not to rewrite anything. It was to understand exactly what was happening. Query Execution Plans in SQL Server Management Studio were used to trace the actual cost of each query the dashboards were running. The picture that emerged was consistent across almost every query.

The Four Root Causes Found

After reviewing all 23 queries the dashboards were generating, four patterns kept appearing:

• Full table scans on large tables. The main sales fact table had over 18 million rows. Several queries were scanning the entire table on every load, even for date-filtered views, because there were no indexes on the filter columns.
• Nested subqueries instead of CTEs or joins. Some queries had three and four levels of nesting, forcing SQL Server to execute inner queries millions of times rather than once.
• Repeated aggregation of the same data. Queries for different dashboard tiles were independently calculating the same monthly sales totals from raw transaction data, rather than reading from a pre-aggregated source.
• SELECT * in underlying views. Several base views used SELECT *, pulling all 34 columns from the sales table even when the dashboard only needed 4 of them. This sent unnecessary data across the network on every query.

03. Optimization 1: Strategic Indexing

The biggest single gain came from adding the right indexes. The sales fact table was being scanned from top to bottom on every query because the columns most commonly used in WHERE clauses had no indexes at all.

The following composite index was added to the main sales table, covering the three columns that appeared in the filter conditions of 19 out of 23 dashboard queries:

SQL — Before: No Relevant Index (Full Scan Every Query)

-- Table: dbo.fact_sales (18.3 million rows)
-- Existing indexes: only on primary key (sale_id)
-- Every WHERE clause on date, store_id, or category_id
-- triggered a full table scan. Execution cost: 847 units.

SQL — After: Composite Index Added

CREATE NONCLUSTERED INDEX idx_sales_date_store_cat
ON dbo.fact_sales (sale_date, store_id, category_id)
INCLUDE (revenue, quantity, cost);

The INCLUDE clause means the index stores the revenue, quantity, and cost columns alongside the key columns. When a query filters by date, store, and category and only needs those three values, SQL Server can satisfy the entire query from the index without ever touching the main table. This is called a covering index.

04. Optimization 2: Replacing Nested Subqueries with CTEs

Several queries had been written with nested subqueries. This pattern is common in queries that grow organically over time — someone adds a filter, then a calculated field, then another filter, and the nesting builds up without anyone stepping back to restructure the whole thing.

Here is a simplified example of the pattern found in the Customer Segment Analysis query, which was taking 12 seconds on its own:

SQL — BEFORE: Nested Subqueries (12.3 second execution)

SELECT
  segment_name,
  total_revenue,
  order_count
FROM (
  SELECT
    segment_name,
    SUM(revenue) AS total_revenue,
    COUNT(order_id) AS order_count
  FROM (
    SELECT
      c.segment_name,
      s.revenue,
      s.order_id
    FROM dbo.fact_sales s
    INNER JOIN (
      SELECT customer_id, segment_name
      FROM dbo.dim_customer
      WHERE is_active = 1 -- re-evaluated per outer row
    ) c ON s.customer_id = c.customer_id
    WHERE s.sale_date >= '2025-01-01'
  ) base
  GROUP BY segment_name
) summary
ORDER BY total_revenue DESC;

SQL — AFTER: CTE Rewrite (1.8 second execution)

WITH active_customers AS (
  -- Evaluated ONCE, result reused below
  SELECT customer_id, segment_name
  FROM dbo.dim_customer
  WHERE is_active = 1
),
sales_2025 AS (
  SELECT s.customer_id, s.revenue, s.order_id
  FROM dbo.fact_sales s
  WHERE s.sale_date >= '2025-01-01'
)
SELECT
  c.segment_name,
  SUM(s.revenue)   AS total_revenue,
  COUNT(s.order_id) AS order_count
FROM sales_2025 s
INNER JOIN active_customers c ON s.customer_id = c.customer_id
GROUP BY c.segment_name
ORDER BY total_revenue DESC;

The CTE version is not just faster. It is also much easier to read, test, and maintain. The active_customers CTE is evaluated once and cached in memory. The nested version was re-evaluating the inner subquery for every row in the outer query, causing unnecessary repeated work.

05. Optimization 3: Pre-Aggregated Summary Tables

The single most impactful architectural change was creating a set of pre-aggregated summary tables. Four different dashboard queries were each independently computing the same monthly sales aggregations from the raw 18-million-row fact table. This meant the same heavy calculation was being done four times per dashboard load.

The solution was to create a summary table that was refreshed nightly via a SQL Server Agent job, and redirect all four queries to read from that summary instead of the raw table:

SQL — Creating the Monthly Summary Table

CREATE TABLE dbo.summary_sales_monthly (
  year_month      CHAR(7)        NOT NULL, -- e.g. '2025-03'
  store_id        INT            NOT NULL,
  category_id     INT            NOT NULL,
  total_revenue   DECIMAL(18,2)  NOT NULL,
  total_orders    INT            NOT NULL,
  total_units     INT            NOT NULL,
  avg_order_value DECIMAL(18,2)  NOT NULL,
  created_at      DATETIME DEFAULT GETDATE(),
  CONSTRAINT PK_summary_monthly
    PRIMARY KEY (year_month, store_id, category_id)
);

SQL — Nightly Refresh Procedure (Runs via SQL Server Agent at 2 AM)

CREATE PROCEDURE dbo.usp_refresh_monthly_summary AS
BEGIN
  TRUNCATE TABLE dbo.summary_sales_monthly;
  -- Rebuild from scratch nightly for full accuracy
  INSERT INTO dbo.summary_sales_monthly
  SELECT
    FORMAT(sale_date, 'yyyy-MM')               AS year_month,
    store_id,
    category_id,
    SUM(revenue)                               AS total_revenue,
    COUNT(DISTINCT order_id)                    AS total_orders,
    SUM(quantity)                              AS total_units,
    SUM(revenue) / NULLIF(COUNT(DISTINCT order_id), 0) AS avg_order_value
  FROM dbo.fact_sales
  WHERE sale_date >= DATEADD(YEAR, -2, GETDATE())
  GROUP BY FORMAT(sale_date, 'yyyy-MM'), store_id, category_id;
END;

The summary table has roughly 8,400 rows — one per month-store-category combination for 2 years. Queries that used to scan 18 million rows now scan 8,400. For the four queries redirected to this table, execution time dropped from an average of 11 seconds each to under 0.5 seconds each.

06. Optimization 4: Eliminating SELECT * from Base Views

This was the quickest fix in the engagement and one of the easiest to overlook. Several underlying database views — which the dashboard queries read from — had been written with SELECT * at some point and never updated as the table schema evolved. The fact_sales table had grown from 18 columns to 34 columns over two years. Every query reading through those views was pulling all 34 columns across the network even when only 4 or 5 were needed.

SQL — BEFORE: View using SELECT *

CREATE VIEW dbo.vw_sales_base AS
SELECT * -- Pulls all 34 columns, always
FROM dbo.fact_sales
WHERE is_deleted = 0;

SQL — AFTER: View selecting only needed columns

CREATE OR ALTER VIEW dbo.vw_sales_base AS
SELECT
  sale_id,
  sale_date,
  customer_id,
  store_id,
  category_id,
  order_id,
  revenue,
  quantity,
  cost
FROM dbo.fact_sales
WHERE is_deleted = 0;

Replacing SELECT * with explicit column lists reduced the data volume transferred from the database server to the Power BI service on every query load. The network transfer size per query dropped by approximately 74 percent. Alone, this shaved 3 to 4 seconds off load times. Combined with the other optimizations, it contributed to the compound improvement across all dashboard tiles.

07. Before and After: Full Comparison

08. What Each Optimization Contributed

09. What Was Not Changed

It is worth being explicit about the scope of this work, because the results might suggest a much larger intervention than what actually happened.

• The underlying data model was not changed. No tables were restructured, merged, or renamed.
• The Power BI dashboard design was not changed. No visuals, filters, or report pages were modified.
• The database server hardware was not upgraded. The same server, same memory, same CPU.
• No data was removed or archived to reduce table size.
• The DirectQuery connection mode was kept. The team wanted real-time data and the optimizations made that viable.

Everything achieved here came from working smarter with the existing data infrastructure. The lesson is that most SQL performance problems are not hardware problems or architecture problems. They are query design problems — and they can be fixed without spending money on new servers or rebuilding data models from scratch.

10. How the Work Was Done: The Process

For readers who want to apply a similar approach to their own environment, here is the step-by-step process used in this engagement.

• Step 1: Capture all queries. Used SQL Server Profiler to record every query the dashboards were generating during a live load session. 23 distinct queries were identified.
• Step 2: Rank by cost. Ran each query with SET STATISTICS IO ON and SET STATISTICS TIME ON to measure real execution time and logical reads. Ranked all 23 queries by total execution cost.
• Step 3: Read the execution plans. For the top 8 most expensive queries, opened the actual execution plan in SSMS. Looked specifically for table scan operators, key lookup operators, and sort operators — these are the most common signs of missing indexes or poor query structure.
• Step 4: Fix highest impact first. Applied indexing changes before rewriting any query logic, because indexes can fix multiple queries at once and the risk of introducing bugs is zero.
• Step 5: Rewrite queries one at a time. Rewrote each expensive query using CTEs and explicit column lists, tested each rewrite against the original for identical output, then compared execution plans and timings.
• Step 6: Build the summary table and redirect queries. Created the monthly summary table, validated that its data matched the raw calculation, and updated the four aggregation queries to use it.
• Step 7: End-to-end load test. Cleared all SQL Server caches, loaded each dashboard page, timed each tile, and confirmed that all results matched the pre-optimization output.

11. Conclusion

An 80 percent reduction in dashboard load time sounds like a large number. In this case, it was achieved through a small set of focused, targeted changes applied to the right problems in the right order.

The diagnosis phase is the most important part of any performance optimization work. Without reading the execution plans and measuring the actual cost of each query, it would be easy to spend time optimizing queries that were not the bottleneck, or applying generic best practices that did not match the specific patterns causing the slowdown.

The four techniques used in this engagement — composite indexing, CTE query rewrites, pre-aggregated summary tables, and eliminating SELECT * — are not advanced or exotic. They are fundamental, well-documented practices that are easy to overlook when dashboards are being built under time pressure and performance feels good enough in the early stages. The best time to apply these practices is during the initial build. The second best time is when the symptoms become impossible to ignore. In both cases, the process is the same: diagnose first, fix the highest-impact problems first, and validate every change against the original output before moving on.