114 ) Data model tuning to improve the tables performance

 

Complete Beginner's Guide: How to Analyze and Tune a Fact Table Data Model in SSMS

Here is the complete, step-by-step beginner's guide. It covers how to capture performance, what specific numbers and metrics to look for, and how to change the data model to fix it.

Step 1: Set Up Your Monitoring Tools in SSMS

Before running any queries, you need to tell SSMS to show you the performance metrics.

1. Open SQL Server Management Studio (SSMS) and open a new Query window.

2. Click the "Include Actual Execution Plan" button on the top toolbar (or press Ctrl + M). This tells SQL Server to generate a visual map of how your query runs.

3. Paste and run these commands at the top of your query window to turn on text performance stats:

   SQL

   SET STATISTICS TIME ON;
   SET STATISTICS IO ON;
   

Step 2: Run the Heavy Query on Your Base Fact Table

Run a query that asks for a high-level summary (like yearly sales by region) from your low-grain transactional table (fact_sales):

SQL

SELECT 
    d.year,
    f.region_id,
    SUM(f.amount) AS total_amount
FROM fact_sales f
INNER JOIN dim_date d ON f.date_key = d.date_key
GROUP BY d.year, f.region_id;

Step 3: Analyze the Performance Results (What to Look For)

Once the query finishes, look at two places in SSMS to evaluate its performance:

1. Check the "Messages" Tab (The Numbers)

Look at the bottom panel in SSMS and click on the Messages tab. You will see output like this:

• CPU time = 1200 ms: This is how hard your computer's processor worked.

• Elapsed time = 4500 ms: This is the total time (4.5 seconds) the user had to wait for the result. Note this number.

• Table 'fact_sales'. Scan count 1, logical reads 450,000: Logical reads tell you how many 8KB data pages SQL Server had to load from memory. A high number (like hundreds of thousands) means the database is reading massive amounts of unnecessary data.

2. Check the "Execution Plan" Tab (The Visual Proof)

Click on the Execution Plan tab next to Results. You will see graphical icons connected by arrows.

• Look for the Icon Name: Hover your mouse over the rightmost icon. If it says Clustered Index Scan or Table Scan, it means SQL Server had to read every single row in the table from top to bottom.

• Check the Cost Percentage: Look at the percentage written on that icon (e.g., 100%). If one step takes 100% of the query cost, that is your performance bottleneck.

• Check the Number of Rows Read: Hover over the arrow leading into the scan. Look for Number of Rows Read (e.g., 10,000,000 rows). If your final report only gives you 5 rows of data, but SQL Server had to read 10 million rows to get them, your data model is inefficient for this type of query.

Step 4: Change the Data Model (The Fix)

Because scanning 10 million rows for a high-level yearly report is a structural data model flaw, you fix it by changing the model architecture ( mentioned after step 5 ) —creating a pre-aggregated summary table.

Run this script to build a tuned summary fact table:

SQL

-- 1. Create a higher-grain summary model table
CREATE TABLE fact_sales_yearly_summary (
    year INT,
    region_id INT,
    total_sales_amount DECIMAL(12,2),
    PRIMARY KEY (year, region_id)
);

-- 2. Populate it once from the heavy transactional table
INSERT INTO fact_sales_yearly_summary (year, region_id, total_sales_amount)
SELECT 
    d.year,
    f.region_id,
    SUM(f.amount)
FROM fact_sales f
INNER JOIN dim_date d ON f.date_key = d.date_key
GROUP BY d.year, f.region_id;

Step 5: Re-Test Performance with the Tuned Model

Now, run your report query against the new, tuned data model:

SQL

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT 
    year,
    region_id,
    total_sales_amount
FROM fact_sales_yearly_summary
WHERE year = 2026;

What to note after tuning:

• In the Messages Tab: Your Elapsed time will drop from seconds to milliseconds (e.g., 10 ms), and logical reads will drop from hundreds of thousands down to just 2 or 3.

• In the Execution Plan Tab: The heavy "Table Scan" icon will be replaced by an efficient Index Seek, and the row count will go straight from reading millions to reading just the few rows you asked for.

==================================================

Steps to Fix the Data Model by Creating a Pre-Aggregated Summary Table

Here are the specific, step-by-step instructions to fix the structural performance issue by changing your data model architecture from a low-grain transactional table to a pre-aggregated summary table in SSMS.

Step 1: Design and Create the New Summary Fact Table

You change the data model architecture by creating a new table designed specifically at the higher grain requested by high-level reports (in this case, grouped by year and region_id instead of individual transactions).

Run this script in SSMS to create the new data model structure:

SQL

-- Create a higher-grain summary model table
CREATE TABLE fact_sales_yearly_summary (
    year INT,
    region_id INT,
    total_sales_amount DECIMAL(12,2),
    PRIMARY KEY (year, region_id)
);

Step 2: Populate the New Data Model Table (ETL / Load)

Next, populate your new summary data model by aggregating data from your heavy base transactional table (fact_sales). This shifts the heavy calculation burden away from live user queries and does it once during data loading.

Run this script in SSMS:

SQL

-- Populate the summary model from the base transactional model
INSERT INTO fact_sales_yearly_summary (year, region_id, total_sales_amount)
SELECT 
    d.year,
    f.region_id,
    SUM(f.amount)
FROM fact_sales f
INNER JOIN dim_date d ON f.date_key = d.date_key
GROUP BY d.year, f.region_id;

Step 3: Update Your Report Query to Use the New Model

To finish the fix, point your reporting query or dashboard to the newly architected summary table instead of scanning the 10-million-row base fact table.

Run this script in SSMS with your performance stats turned on (Ctrl + M for Execution Plan):

SQL

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- Querying the newly architected data model
SELECT 
    year,
    region_id,
    total_sales_amount
FROM fact_sales_yearly_summary
WHERE year = 2026;

Step 4: Verify the Architecture Fix

Check your SSMS results to confirm the data model fix worked:

• Messages Tab: Your logical reads will drop from hundreds of thousands down to just a handful, and Elapsed time will drop to milliseconds.

• Execution Plan Tab: The heavy table scan will disappear, replaced by an instant Clustered Index Seek, proving the structural data model flaw has been resolved.