Enterprise Database Performance Optimization for Madad Bakhtar Company

Enterprise Database Performance Optimization for Madad Bakhtar Company

Project Overview

In 2017, I led a comprehensive SQL Server performance optimization and storage restructuring project for Madad Bakhtar, one of the major regional electricity billing organizations serving the provinces of Markazi, Hamedan, and Lorestan in Iran.
The operational database—responsible for billing, metering, and transaction management—had grown to over 500 GB and was suffering from severe performance degradation, inefficient storage utilization, and long execution times on critical business queries.

This case study outlines the technical challenges, optimization strategies, and measurable performance improvements delivered during the project.

1. Initial Challenges

The system exhibited several significant performance and storage issues:

Performance Problems

• Extremely slow execution of high-usage and business-critical queries
• Excessive logical and physical reads
• High CPU consumption during peak billing periods
• Long response times on reporting modules and data extraction processes

Storage & Structural Issues

• Database file fragmentation and inefficient autogrowth settings
• Presence of HEAP tables leading to page disorder
• Incorrect or suboptimal data types
• Numerous unused, overlapping, and fully redundant indexes
• Large tables suffering from internal fragmentation and wasted space
• Unbalanced filegroup distribution causing I/O bottlenecks

2. Scope of Work

To stabilize the platform and prepare it for future scalability, I executed a deep technical analysis followed by a multi-phase optimization plan:

Database File & Storage Optimization

• Rebuilt logical file structures and redesigned file growth patterns
• Reduced fragmentation at both file and object level
• Implemented optimized filegroup allocation based on table usage patterns

Object-Level Assessment

• Identified HEAP tables and recommended clustered index strategies
• Detected data-type misconfigurations impacting performance and space consumption
• Performed a full index health audit, marking unused and duplicate indexes for removal
• Rebuilt critical large tables to restore page order and recover unused space

Query & Workload Optimization

• Analyzed execution plans of high-traffic tables
• Highlighted problematic scans, missing indexes, and cardinality estimation issues
• Proposed structural corrections and indexing strategies for long-term maintainability

3. Key Technical Improvements

Below are selected examples demonstrating the performance impact of the optimization:

3.1. Table: ImportExportFileHistoryTbl

Query:

Performance

Metric	Old Database	Optimized Database
Logical Reads	2,870	698
CPU Time	47 ms	15 ms
Duration	2,742 ms	492 ms

3.2. Table: SRTbl – Full Scan

Query:

SELECT COUNT(*) 

  FROM [dbo].[SRTbl]

Metric	Old DB	Optimized DB
Logical Reads	144,652	88,661
Duration	49,490 ms	30,030 ms

3.3. SRTbl – Date Range Filtering

Query:

SELECT * 

  FROM [dbo].[SRTbl] 

  WHERE SaleDateTime BETWEEN '2010-12-07 00:00:01.000' AND '2010-12-07 23:59:59.000'

Metric	Old DB	Optimized DB
Logical Reads	27,574	67
Duration	29,724 ms	902 ms

3.4. Multi-Table Join

Tables involved:

• SRTbl: ~58 million rows
• SrDetailTbl: ~64 million rows
• SRLinearTbl: ~57 million rows

Query:

SELECT * 

  FROM [dbo].[SRTbl] AS t 

    INNER JOIN [dbo].[SRLinearTbl] AS l ON t.SRCode = l.SRCode 

    INNER JOIN [dbo].[SrDetailTbl] AS d ON t.SRCode = d.SRCode 

  WHERE t.SaleDateTime BETWEEN '2010-12-07 00:00:01.000' AND '2010-12-07 23:59:59.000'

Metric	Old DB	Optimized DB
Total Duration	214,805 ms	51,832 ms
SRTbl Logical Reads	27,254	67
SRLinearTbl Logical Reads	25,494	23,890
SrDetailTbl Logical Reads	15,438	15,084

4. Outcome & Business Impact

The optimization resulted in:

Performance Gains

• Up to 40x faster execution on selected queries
• Significant reduction in CPU utilization
• Dramatically lower I/O overhead
• Substantial reduction in page reads and execution time

Storage Efficiency

• Reclaimed large amounts of unused space
• Improved physical database organization
• Reduced long-term disk usage and improved backup times

Operational Impact

• Improved responsiveness for billing and reporting teams
• More predictable performance during peak billing cycles
• Enhanced maintainability for DBAs and developers
• Prepared the environment for future application enhancements

---

5. Technologies & Tools Used

• Microsoft SQL Server
• Dynamic Management Views (DMVs)
• Index usage statistics
• Execution plan analysis
• Filegroup design and IO balancing
• Advanced fragmentation and storage analysis
• T-SQL performance tuning techniques

---

6. Summary

This project demonstrated how systematic database analysis, indexing strategy, and storage-level optimization can dramatically improve the performance of large-scale enterprise systems.
The improvements achieved were sustainable, required minimal changes at the application layer, and delivered significant operational value to the organization.

---

More info

You can find more info in my GitHub.