E-Risk Services (Database Performance)

Client & Industry

E-Risk Services, operating within the insurance technology sector (often associated with Wachovia during this period), relied heavily on SQL Server databases for both transactional processing and data warehousing. Consistent performance and reliability were critical for their operations.

The Challenge

E-Risk faced severe database performance issues that hampered productivity and operational efficiency. Key challenges included:

• Extremely long query execution times, often taking minutes instead of seconds.
• Lack of established database administration best practices.
• Suboptimal hardware configurations (e.g., slow storage, insufficient memory).
• Inefficient indexing strategies, including unused indexes bloating the database and missing indexes causing full table scans.
• Inadequate or broken database maintenance routines (backups, integrity checks, statistics, defragmentation).
• Inefficiencies in the SSIS-based ETL pipeline and emerging Hadoop/Spark ELT jobs.
• Difficulties in generating timely reports and analytics due to underlying performance bottlenecks.

Solution by Nimbletec

Executed a comprehensive database optimization strategy, delivering expert administration and development to resolve critical performance and reliability deficiencies:

• Implement Database Best Practices: Assessed the existing SQL Server environment and systematically implemented industry best practices for administration, configuration, and security.
• Diagnose & Tune Performance Bottlenecks: Utilized advanced diagnostic tools like SQL Server Profiler and detailed execution plan analysis (within Microsoft Visual Studio and Eclipse IDEs) to pinpoint and resolve performance bottlenecks, specifically targeting inefficient T-SQL stored procedures and underlying causes of slow query execution.
• Optimize Core Infrastructure: Executed critical infrastructure upgrades, including migrating storage to high-performance Solid State Drives (SSD), optimizing physical placement of data, log, and backup files, and increasing server memory allocation.
• Overhaul Indexing Strategy: Conducted thorough index usage analysis, eliminating redundant or unused indexes, creating critical missing indexes to support query patterns, and implementing automated index defragmentation routines.
• Introduce Robust Automated Maintenance: Deployed and configured the industry-standard Ola Hallengren maintenance solution to automate and stabilize essential Database (DB) tasks like backups, integrity checks, and statistics updates, replacing previously unreliable or broken manual processes. Version control managed via Team Foundation Server (TFS).
• Improve Data Integration Pipelines: Enhanced and maintained existing SQL Server Integration Services (SSIS) Extract, Transform, Load (ETL) pipelines; developed and optimized emerging Hadoop/Spark Extract, Load, Transform (ELT) jobs using spark-shell and Python scripts leveraging the PETL library for efficient data warehouse and data mart loading, often hosted on Amazon Web Services (AWS).
• Enable Advanced Reporting & Analytics: Engineered optimized data warehouse load processes and developed sophisticated reporting and analytics solutions using tools like D3.js for custom visualizations, QlikView for business intelligence dashboards, and SQL Server Reporting Services (SSRS) hosted within a custom C# ASP.NET portal, delivering actionable insights from the optimized data platform. Unit testing performed with NUnit.

Key Results & Impact

Nimbletec's comprehensive database optimization delivered transformative results, fundamentally improving E-Risk's data platform performance and reliability:

• Massive Performance Gains: Critical SQL Server query times were drastically reduced from minutes to fractions of a second through targeted tuning and infrastructure upgrades (SSD), significantly boosting application responsiveness and user productivity.
• Enhanced Reliability & Data Safety: Implementation of robust backup, integrity check, and automated maintenance procedures (Ola Hallengren solution) ensured critical data safety and vastly improved overall system stability.
• Improved Data Pipeline Efficiency: Strategic enhancements to SQL Server Integration Services (SSIS), Spark, and Python ETL/ELT processes streamlined data loading and transformation, ensuring timely data availability for analysis.
• Actionable Business Intelligence: Improved reporting and analytics capabilities (SSRS, QlikView, D3.js) provided faster, more reliable access to business insights, enabling better decision-making.
• Reduced Manual Administrative Effort: Automation of routine database maintenance tasks freed up valuable administrative time for more strategic initiatives.