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
- poorly designed databases that do not follow proper normalization can have a lot of data redundancy
- 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)
- I/O (Input/Output) is crucial to performance tuning
- 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:
- Permissions have changes
- The process that runs the data through a flow is broken
- The database server is down, and there is no connection
- The queries have somehow broken and are not running
- 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