Shulou(Shulou.com)06/02 Report--

SQL SERVER how to exchange time and space and what is a good SQL, I believe that many inexperienced people do not know what to do. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

First of all, the recent SQL SERVER memory consumption is very high, before this is to establish a pile of indexes to improve the efficiency of the query, memory use is also quietly rising.

Generally speaking, if there is a normal and reasonable demand for the database, it should be given that the first reaction of most people is whether there is a slow sentence, whether something that consumes memory is running, whether it should be optimized or killed.

Of course, statements should be optimized, indexes should be added, but excessive optimization and a large number of indexes go to the other extreme. SQL SERVER itself and other ORACLE, POSTGRESQL, MYSQL three traditional databases in memory management is more "sheep" management, you install the maximum memory and minimum memory are not set, the system will automatically deal with.

In fact, there are two contradictory points, which also exist in other databases.

1 prevent the buffer from becoming so large that the system is out of memory

2 to minimize the frequent exchange of database files in Imax O and memory by maximizing the buffer pool.

This is a point that should be recognized on most databases. On an overloaded system, large queries cannot get the minimum amount of memory required, which brings the wait for processing the SQL. If your database also supports parallelism, POSTGRESQL ORACLE SQL SERVER, etc., parallelism will not be any better for large queries, but will make things worse. Because of my own lack of memory, and you want to parallel processing, memory is even more insufficient, speed up the pressure of Igamo.

So to find out whether it is a lack of memory or a problem in the design of the software, these two things are mentioned on the table. Individuals tend to add hardware as much as possible when the price of hardware is low, but it is difficult to change some of the architecture and processing of the software, which is related to a lot of things, so we won't start here.

If you don't get enough memory in SQL SERVER, the query may take a different execution plan and take up the way the TEMP library (physical Ibank O) is processed, while performance goes to the other extreme.

Now back to the above question, a SQL if he executes quickly, it shows that it is very good, whether this idea is correct or not, personally, to young to simple.

I will consider whether a SQL OK OK or not from the following dimensions

1 execution time, of course, this is considered, otherwise your customers will complain about you

2 memory consumed by each SQL (I will look at some complex SQL to see how much memory will be consumed, what do you think later)

The complexity of 3 SQL. If a SQL itself is very complex, it should be dismantled.

On the third question may be asked immediately, I like to write complex SQL I do not use MYSQL, ORACLE SQL SERVER is not to ask people to write complex SQL, through a SQL to solve complex logic, is not a good thing.

In fact, from both sides of this point of view, it is true that a SQL can solve a problem that can be solved by multiple SQL. In terms of efficiency, a SQL may really have an advantage. (in fact, in many cases, it is faster to take it apart and run it than to write a hundred lines of SQL.), but people who generally think like this do not have a concurrent thought or idea. If you run a SQL, will it run repeatedly in unit time? Then the resources they want to occupy are likely to overlap. What will happen to the overlap? lock, deadlock, lock waiting, all kinds of latch locks. So the complexity and concurrency of SQL is a contradiction in itself, and it is the same with other databases.

Another SQL execution speed, it is not fixed, and your time, place and people and (in fact, resources, concurrency, unit time) is bound, and machine resources are dynamic, so always emphasize the statement how many seconds to execute the practice, your premise is that resources can not be short of others, and whether the concurrency of the system is high, how often is the execution, and so on. And blindly require a SQL to come out in how many seconds, the previous things do not want, the latter requirements are very difficult to achieve.

The following is the memory occupation of a database. You can see that the memory occupied by the primary keys of some tables has reached the G level. In the case of proving that the system is very busy, it can also analyze some other problems.

Another thing is that SQL SERVER itself will not allocate memory indiscriminately. If it reaches a higher standard of memory consumption, it will naturally apply for more memory, so it is futile to limit the use of memory. Finally, the result of using disk to simulate memory is quite good. You can check the memory occupied by the SQL of a thread in a database. The memory occupied by the following statement is captured, so before looking at the CPU time occupied by a statement, the number of logical scans, and the number of physical scans, you should still be concerned about memory usage.

In fact, when considering whether an SQL is faster, the saving of time may bring about the loss of space (not only referring to memory here), so it is still the same sentence, space for time, time for space, in each database is a "golden sentence" that can be found. In the case of insufficient hardware performance, how to optimize the statement is also futile, at the same time in the tough hardware, also can not stand some SQL.

After reading the above, have you mastered how SQL SERVER exchanges time and space and what is a good SQL method? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.