Basic rules of optimization with sqlserver
What means optimized? This article presents best practices and the rationale governing the optimization of a computer system, it will prompt you to include process optimization in the overall approach of your project
STEPS OF OPTIMIZATION
What can we optimize, and what should be optimized? We usually think of the configuration. The hardware configuration is the set of elements constituting a server hardware and software configuration, the set of parameters of the operating system and RDBMS itself. Although important, these elements are only steps to ensure optimal operation. Other aspects, often neglected, are crucial, and we will try to present them in detail to help you get the most out of your databases. It is not only thinking performance during the server installation, but from the design phase of the data structure, till the writing of the code. We must reflect on the performance in the early stages of the project and throughout its evolution :modeling, choice of material, equipment, organization of physical and logical storage (good indexing tables is of course crucial), SQL coding, server management ...
Unfortunately , in practice, this thinking is too often overlooked, and the need for optimization emerges at the end of the process, when the system is in place and that the response time, suddenly or gradually, is not satisfactory. Sometimes the increase of the volume of data or the number of concurrent users has slowed the query execution time. So you can see the facts and you must bring an urgent solution.
This situation is not conducive if we want to work effectively and in the right direction, but it is unfortunately the most common situation when a planification taking into account the performance has not been conducted since the beginning the project. We will present throughout a series of articles all the tools that are necessary to identify the source of slowdowns and to bring a clear solution .
However, the urgency should not push yourself too quickly to respond to the pressure, and to choose an obvious solution.
I must say that with SQL Server, the performance gains are not automatically achieved by more powerful hardware, because often, the problem is a bad architecture database, lack of indexes, little optimal queries ... all sources of problems that the increase of the power of the equipment will not overcome totally .
To improve performance, we can not do without the search of causes. This research requires appropriate tools - which are fortunately supplied with SQL Server .
Often , optimization is urgent. A database for a merchant web site, cannot afford a prolonged slowdown . when performance falls , sirens are triggered and teams enter in emergency mode . Often, the first solution that comes to our mind is to restart the machine , which often improves the situation, especially when it comes to a problem of deadlock . While this may solve the problem quickly , it is certain that it does not give any concrete information on the source of the slowdown. Wanting too quickly to solve the problem will negatively affect the performance over the long term , because it prevents to conduct the necessary investigation in order to identify the causes , and therefore the establishment of a permanent suitable solution . Often, unresolved problems at the root will cause a kind of permanent emergency where all the attention of technicians is brought to solve real-time issues , such as emergency medical situations. In optimization , you must learn to report drug administration, in order to ensure that you understand the cause. It is essential to take a step back , and not work in a hurry. It may take a little time, but this painful period will allow you to avoid many more later.
SHOULD WE OPTIMIZE ALL?
We must not focus exclusively on performance compared to other criteria .. It is important to maintain a balance between simplicity, readability and performance. The query optimizer in SQL Server is one of the best on the market, and generally accomplished an excellent job. It is often unnecessary to over-optimize your queries, especially when a syntax to another, the query plan generated by the optimizer is the same. Similarly, the optimization should not become an obsession, as in this case, the time dedicated to it can become exaggerated. Here elsewhere, the Pareto applies: 20% of optimization efforts well chosen allow 80% performance gains. The remaining 80% can cost a lot of effort without generating commensurate results
A BASELINE MAINTENANCE
To properly judge the performance of your system , you need to know it . When an user of your database indicates performance problems , how do you know if the reported delays are exceptional, or "normal" ? You have only one way : To build in advance a baseline , and maintain it over time . A baseline is the collection over a period characteristic of important counters to know the behavior of a system in normal time . By regularly journaling these indicators, you will have a clear idea of the physical constitution of your servers, as you can tell for yourself if you are tired or in a good mood , because you have learned to know yourself over time . It is as well to collect data from the hardware support (CPU , memory, network , disk ) , from the use of operating system (consumers process resources , privileged time and time user ... ) than from SQL Server itself (number of transactions and requests per second , the behavior of caches ... ) . We will return to the practical aspects of this baseline in other articles.