SQL Server performance audit in a BI context
Context
Daily loads take the entire night and spill over into the morning, blocking user reporting. Some weekly full loads last more than 15 hours. Despite an initial performance campaign that brought improvements, far too many load processes remain slow.
Challenge
Audit loading interfaces to determine the main causes of load slowness and propose solutions to improve both loading and reporting
Technologies used
DBMS: MS SQL Server 2008 R2 EE • ETL: SAP BODS, SQL Server jobs and stored procedures • Sources: SAP ECC, Flat files • Reporting: SAP BO On Premise
Methodology and implementation
This comprehensive audit analyzed the entire datawarehouse loading chain:
**Phase 1: Diagnosis** - Instrumentation of SAP BODS ETL jobs - Collection of SQL Server metrics (DMV, Extended Events) - Execution plan analysis - Identification of locks and deadlocks
**Phase 2: Analysis** - Detection of non-partitioned tables with high volume - Identification of missing or underutilized indexes - Analysis of obsolete statistics - Study of loading algorithms
**Phase 3: Recommendations** - Partitioning strategy for large tables - Creation of targeted indexes - Optimization of stored procedures - Review of SQL Server configuration parameters
**Phase 4: Training** 3-day specialized training on SQL Server tuning for the internal team.
Results
After specialized training in SQL tuning, internal teams reduced load times by 50% using audit recommendations, SQL server partitioning techniques and their new skills acquired in training.
Visualizations
SQL Server wait statistics
Problematic query identification
Detailed SQL analysis
MSSQL query analysis
Disk I/O analysis
Index fragmentation analysis
Data load stored procedure optimization