150 ) Data model - Optimization

 

 Data model - Optimization 

 Diagnosing, Reporting, and Resolving a Data Model Problem

Here is the complete, end-to-end process showing how a performance bottleneck is spotted in a database tool, reported to the data model team, and successfully resolved.

Step 1: Diagnose and Profile the Bottleneck in the Database Tool

An application engineer or database administrator (DBA) notices a slow screen or a sluggish API call and digs into the database monitoring tool to find the root cause.

• In Microsoft SQL Server (SSMS): The DBA opens SQL Server Management Studio, runs Activity Monitor or checks the Query Store, and identifies a stored procedure taking 8 seconds to run. By viewing the Estimated/Actual Execution Plan, they spot a massive Clustered Index Scan (full table scan) on a wide table because the query is constantly pulling heavy text columns that aren't needed.

• In Oracle: The DBA checks Enterprise Manager (EM) or runs an AWR (Automatic Workload Repository) report, pointing to high db file sequential read events caused by inefficient table design and poor column layouts.

• In Snowflake: A data engineer checks Query History, finding that a core operational query is scanning massive partitions because of unoptimized column selection and missing structural separation.

Step 2: Report the Issue to the Data Model Team via Jira

Once diagnosed, the engineer creates a formal ticket to alert the enterprise data architecture and modeling team.

• Jira Ticket Creation:

• Title: Data Model Optimization: Vertical partitioning required for bloated users table causing memory pressure.

• Description: "API endpoint /api/v1/users/status is timing out. SSMS execution plan shows full table scans on the users table due to oversized rows containing VARCHAR(MAX) biography fields. Proposing vertical split of the table to isolate core attributes from heavy profile text."

• Attachments: Screenshots of the SSMS execution plan, query text, and performance metrics (showing high logical reads).

• Priority: High / Medium.

Step 3: Data Model Team Acceptance and Technical Analysis

The Data Model Team reviews the Jira ticket during their backlog grooming or architecture review session.

• Ticket Triage & Acceptance: The Lead Data Modeler reviews the Jira attachment, verifies that the table design violates best practices for row-size efficiency, and accepts the ticket, moving it to "In Progress."

• Impact Analysis by the Data Model Team:

  • The modeler checks the enterprise data dictionary / ER diagram tool (e.g., Erwin Data Modeler or ER/Studio).

  • They identify all downstream applications, views, and stored procedures that depend on the users table to ensure the upcoming structural change won't break legacy queries.

  • They design the new schema change (splitting into users_core and users_profile_details).

 

Step 4: Iterative Troubleshooting, Trial, and Resolution

In real-world enterprise environments, performance tuning is rarely a one-shot fix. When the data model team picks up the Jira ticket, they typically go through a phase of experimentation (trial and error) before landing on the final working architecture.

Here is how the team troubleshot the bloated users table issue:

1. Attempt 1: Adding Indexing (Failed)

• What they tried: The team first attempted a quick fix by adding a non-clustered index covering the username and account_status columns, hoping to avoid full table scans without altering the table structure.

• Why it failed: Because the table contained massive VARCHAR(MAX) columns and the row size was excessively wide, the database engine's query optimizer still estimated high page reads and memory grants. It bypassed the index, choosing a full table scan because fetching the rows required traversing bloated data pages anyway.

2. Attempt 2: Table Compression (Partial Success, High CPU)

• What they tried: Next, they enabled Page Compression on the users table to shrink the physical footprint on disk and fit more rows into memory cache.

• Why it fell short: While it reduced storage size by 40%, it introduced high CPU overhead every time rows were updated or read because the database engine constantly had to compress and decompress the heavy biography text fields during queries.

3. Attempt 3: Vertical Partitioning (What Finally Worked)

• What succeeded: Recognizing that lightweight queries shouldn't share the same physical storage pages as heavy text fields, the team decided on Vertical Partitioning.

• The Result: Separating the core operational columns from the profile text fields completely eliminated unnecessary I/O. Lightweight queries now hit lean data pages, drastically reducing execution time from 8 seconds down to milliseconds.

Step 5: Before and After Data Model Comparison

Old Data Model (Monolithic & Bloated Table)	New Data Model (Optimized & Vertically Partitioned Tables)
Table Name: users	Table 1: users_core
Columns:	Columns:
• user_id (INT, PK)	• user_id (INT, PK)
• username (VARCHAR(MAX))	• username (VARCHAR(50), NOT NULL)
• email (VARCHAR(MAX))	• email (VARCHAR(100), NOT NULL)
• account_status (VARCHAR(20))	• account_status (TINYINT, NOT NULL)
• biography (VARCHAR(MAX))	• created_at (DATETIME)
• profile_notes (VARCHAR(MAX))
• created_at (DATETIME)	Table 2: users_profile_details
	Columns:
	• user_id (INT, PK, FK referencing users_core.user_id)
	• biography (VARCHAR(MAX))
	• profile_notes (VARCHAR(MAX))

Explaination :

problem : The monolithic users table was bloated with heavy VARCHAR(MAX)  fields and oversized data types, causing full table scans, causing excessive memory page reads, and slow API query performance.

solution with new data model : Vertically partitioned the table into users_core (containing lightweight operational columns and right-sized data types) and users_profile_details (containing heavy text fields in a 1:1 relationship), allowing frequent queries to scan lean, highly cacheable pages.

what happens if data model not changed : Continued database performance degradation, high CPU and disk I/O overhead from decompressing or scanning unnecessary text data, and recurring API timeouts during peak traffic.