Performance tuning is a continuous process in any database environments. Many organizations spend more time and resources on tuning the database than on the original development because of data growth and usage on top of a poor database design. 80% of performance issues can be avoided with a proper database design. In addition, developing Transact-SQL code is very different from developing front-end applications code and middle-tier components. Transact-SQL code developed by front-end or middle-tier experts can often be optimized depending on the transparency of the code. Finally, there is a lot of requirement and necessity for optimizing databases when the volume of operations grows and the application that worked great with 5-10 users and 100-200GB no longer lives up to tera bytes of data and 1000s of users' expectations. Yes as all of us aware scalability is the root cause.
A common place where most people like to start tuning databases is tuning the Server configuration. Generally, Most developers that are unhappy with the application performance will demand adding more memory to the server and reconfiguring the dynamic configuration parameters. But, extending the memory or keep on changing configuration parameters will only help optimizing the performance up to a certain point. In other words, if you keep adding memory and do not tune the application or t-sql code in any other way, you will reach the point where additional memory or re-configuration produces marginal or no performance improvement.
It is also important to realize that improvement in one area often means compromising others. If you can optimize 90% of the critical queries by slowing down the performance of other 10% it might be well worth of your time. Sometimes you can improve the performance of online transactions by increasing the response time with the expense of reducing concurrency or throughput. Therefore it is important to determine the application performance requirements. If we improve the performance of selects obviously we compromise DML statements/transactions (may be adding indexes).
It is common to spend more time identifying the cause of the problem then actually troubleshooting and fixing it. If all other areas of application are working properly and you can be sure there is a problem with the database code, then you need to investigate your code modules and decide which one is causing problems. Many times improvement in only one stored procedure or trigger can fix most of the issues.
The Optimizer in the Adaptive Server takes a query and finds the best way to execute it. The optimization is done based on the statistics for a database or table. The optimized plan stays in effect until the statistics are updated or the query changes. You can update the statistics on the entire table or by sampling on a percentage of the data.
Adaptive Server can generate an abstract plan for a query, and save the text and its associated abstract plan in the sysqueryplans system table. Abstract plans provide an alternative to options that must be specified in the batch or query in order to influence optimizer decisions. Using abstract plans, you can influence the optimization of a SQL statement without having to modify the statement syntax.
Adaptive Server locks the tables, data pages, or data rows currently used by active transactions by locking them. Locking is a concurrency control mechanism: it ensures the consistency of data within and across transactions. Locking is needed in a multiuser environment, since several users may be working with the same data at the same time.
Carefully considered indexes, built on top of a good database design, are the foundation of a high-performance Adaptive Server installation. However, adding indexes without proper analysis can reduce the overall performance of your system. Insert, update, and delete operations can take longer when a large number of indexes need to be updated.
What to observe - high level (We have many many :) ... but top 10)
- Server error logs
- OS error logs
- Configuration files
- sp_monitorconfig output
- sp_sysmon reports
- sp_object_stats reports
- sp_helpdb output
- options and traceflags used
- ER diagram or database model
- and all applications running on the database.
No comments:
Post a Comment