Performance Tuning & Optimization
A low performance database is greedy for extra resources and causes long delays, no response and errors such as deadlock and timeout. Since performance downgrade usually is in a consequence of low scalability, performance issues will often get worse as more concurrent users join.
Improving database performance to satisfactory level is a step by step process. A cost-effective tuning plan that maximizes the performance gains begins with application and database optimization and continues on to tuning the hardware, SQL Server and the operating system.
We have designed our performance tuning service to detect and resolve system issues and bottlenecks and substantially improve your database/data warehouse performance and scalability. It saves you time and money by helping you avoid new hardware procurement, maintenance complexity, administration difficulty and future incidents causing business interruption.
Performance Tuning and Optimization Areas
- Application Middle Tiers
- Databases (OLTP)
- Data warehouses (OLAP)
- SSAS (Cubes)
- SSIS Packages
- SQL Jobs
- Maintenance Plans
- SQL-based Modules and Components
We have been involved in many tuning projects with various goals. The achieved results have been always astonishing and often beyond expectation. To read some of our success stories, please read our Case Studies.
- Data Growth
- High Loading
- Data Statistical Distribution Changes
- Maintenance Plans Issues
- Resource Shortage
- Infrastructure Issues
- Operation Conflicts
- Human Errors
- Wrong Configuration
- Concurrent Users
- Side Effects of Changes
- Execution Duration
- Response Time*
- Number of Concurrent Users (Throughput)
- Resources Consumption Amount
- Errors and Warnings
(*) Response time is the time required to get the first result, while execution duration is the time required to get the entire result out of database. Those two concepts may act against each other; as an example, decreasing response time doesn’t necessarily make duration shorter.
- No task waiting for CPU*
- No pending disk IO request
- Low memory reloading (Page Life Expectancy <= 130 ms)
- Low usage of network IO, tempdb Database and Virtual Memory
(*) 100% CPU utilization doesn't necessarily mean that CPU is under pressure.
- Number of Hard Read/Write
- Logical Read/Write
- CPU Usage
- Execution Duration
- Response Time
Performance Tuning & Optimization Workflow
- Determine required tuning areas
- Log performance indicators before any modification
- Identify suitable results based on user expectation, industry standards and thresholds, baselines, and business limitations
-
Specify & prioritize tuning tasks based on
-
Effectiveness
- Remove redundancies
- Tune existing components
- Add new software pieces
- Add new hardware resources
- Costs
- Duration
-
Effectiveness
-
Tuning Cycle
- Execute tuning tasks based on defined priority
- Log performance indicators after modification
- Evaluation
-
Closing Procedure
- Archive project logs and reports
- Conduct post mortem meeting (Lesson Learned)
