OVERVIEW
Performance tuning becomes essential when the system indicates sluggish or becomes absolutely unresponsive. Usually, this happens due to increased load with some degree of decreasing performance.
Thus, Corporations can save a lot of money using Performance tuning just by modifying a system to handle higher loads and thereby enhance the server performance without spending on new infrastructure or applications.
What we do:
- Application Tuning: Analysis of the code and suggesting better coding practices, which will resolve 80% issues.
- Database Parameter Tuning: Optimal sizing of DB buffer, shared a pool, buffer cache, etc, changing the SGA / PGA parameters.
- Database contention: Elimination/minimization of deadlocks, blocking session, wait for events, data partitioning.
- Operating system: Monitor and tune CPU, I/O, memory utilization.
- Caching: Improvement of performance by retaining the frequently accessed data in the cache, ensuring proper utilization of resources and avoiding repeated computation.
How do we do:
- Identification of the pain areas: Understanding the problem areas and analysis the best possible solution/approach to resolving the same.
- Performance goals: We strive for achieving the best result and also overperform than the expected goals, within the expected cost, duration and effort.
Benefits:
- Understanding the pain areas and detailed analysis of the same from the overall infrastructure perspective.
- Iterations till the best output is achieved.
- Enhanced availability of the application and improvement in TPS.
- Elimination of cost by investing in new hardware.
- Improved end-user performance.
- Improved ROI.
SQL Server Performance Tuning Goals
- Instance Level Tuning: Improper SQL Server configuration parameter.Excessive load on storage/CPU/Memory.Transaction/query slowness.
- SQL Tuning: Query Full table scans.Wrong Index Scan.Query hard parsing.Locking issue
Excessive load on storage/CPU/Memory.Transaction/query slowness.
- Automate Proactive alerts and reports: Automate Proactive alerts and reports setting to identify potential problems in the future.
- Database Level Tuning: Improper database file size/structure.I/O distribution Analysis
SQL Server Resource Wait Stats Analysis.Excessive space usage.Transaction/query slowness.
- Server system Tuning: Analyze systems CPU/Memory/Network and storage load after above goals Resource sizing if required.
- Post-Performance benchmarking and issue troubleshooting: Performance analysis and benchmarking after changes.