The key to managing the performance of relational databases is proper SQL Server optimization through SQL Tuning. In order to achieve this higher level of performance, measurement of database metrics and SQL tuning statements must be performed.
Overall system performance and reliability are the results of SQL Server optimization where system workloads are reduced, balanced and parallelized.
The SQL Tuning Process
SQL tuning processes will depend on the system configuration and application requirements. Regardless of system requirements, however, there are four parts to the SQL tuning process. Here they are:
1. The identification of high load or resource intensive SQL statements that are responsible for the largest workload and usage of system resources. Historic performance data can be used to identify high load SQL statements. Using wait-time data is the best data to use. Typically, the longer it takes a query to execute, the greater the resource usage.
2. Execution plans must be verified. Compare execution plans of when the system was performing well to those times were performance lagged. Baseline metrics for future SQL tuning efforts will then be apparent.
3. Corrective steps should be implemented to improve targeted query, database and application performance. SQL statements which perform poorly, will show which steps need to be taken to improve performance.
4. Steps 1 through 4 should be repeated and measured until the system is at peak performance levels. Typically one attempt at SQL Server Optimization will not result in desired performance levels. Repeating the SQL tuning steps and measuring performance gains each time is the only way to achieve the best results over time.
SQL Server Optimization and Tuning Tips
Top relational database performance tips include:
- Off peak hours are the best time to schedule batch processes and non-critical reports since system resources are available at that time
- Parallelize queries that access large chunks of data to reduce response time
- Consider rewriting SQL statements rather than modifying them
- SQL statements for individual tasks should be written/rewritten separately
Related posts:
Powered by FireStats