Database Performance Tuning


Performance tuning is the process of increasing the performance of a data system by ensuring queries are performing at optimal levels.

  • process of increasing the performance of a relational database by watching:
    • the performance of queries
    • the servers and infrastructure around the processing requirements
  • effects of poor performance:
    • slow refresh rates
    • stale data
  • several different types of issues that can cause slow performance:
    • poorly designed databases that do not follow proper normalization can have a lot of data redundancy
      • Excess data will cause performance issues
    • Poorly designed queries
    • have a design that requires a lot of processing but does not have a lot of processing power
  • DBAs will be consistently monitoring database transactions
    •  I/O (Input/Output) is crucial to performance tuning
      • focus on successful, failed, and total concurrent transactions (workload)
  • DBAs will create indexes for the right queries, resulting in increased performance

Stale Data

  • When a user reports that data is not refreshing, and you confirm, then you will need to troubleshoot the data
  • reasons why stale data might have occurred:
    1. Permissions have changes
    2. The process that runs the data through a flow is broken
    3. The database server is down, and there is no connection
    4. The queries have somehow broken and are not running
    5. An older version is in use (user is not looking at the correct report)
  • might perform specific validation steps:
    • checking the report’s “last refresh” timestamp and comparing it to source data timestamps
    • check the run times and verify ETL or datapipe logs (like SQL Agent job history or Power BI refresh history) for failures or delays
    • you must pinpoint the exact moment at which data went stale
      • by comparing row counts or key metrics in the report versus the source
  • There are also automated alerts or monitoring dashboards that proactively catch stale data
    • e.g., Azure Monitor and AWS CloudWatch