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

Most people do not understand the knowledge points of this article "what are the common performance problems in SQLServer?", so the editor summarizes the following contents, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "what are the common performance problems in SQLServer?"

1. Tempdb PAGELATCH contention

This increasingly common problem is usually due to the heavy use of tempdb by the system for some type of extraction, transformation, and loading (ETL) process. This is especially common if it is a continuous "real-time" style of ETL process.

The symptoms may be different, but some things are always the same: high PAGELATCH wait in tempdb and poor performance of process logging using tempdb. I usually follow the wait for Top SQL in Performance Advisor and look at a large number of queries that use the temporary tables listed in Top SQL. These queries are usually run in milliseconds and should never be counted as the "top-level SQL" of the server. This may make people think that these queries are a big part of the problem, but this is not the case. Queries are the victims of real problems.

Once I suspect this is the case, I usually jump to the disk activity tab in Performance Advisor to see how tempdb is configured. Most of the time I actually see the same thing: a busy tempdb that defines a data file. From here on, I usually recommend reconfiguring tempdb.

two。 Look forward to automatically updating statistics to keep statistics updated

The problem here is that the threshold for triggering automatic statistical updates ends up being the same in most cases, even for very large tables. The threshold is about 20% of the rows in the table. On a very large table, a large number of data changes are required to trigger updates.

The list is listed because the database administrator seems really surprised to find that automatic updates do not handle things as the name implies. Then there are many DBA who think it should be handled by their maintenance work. Then after reviewing the maintenance, they spend most of their time reorganizing the index, which does not update the statistics (although the rebuild will).

The lesson is to pay close attention to statistics and ensure that they are updated regularly, especially on increasingly common tables. Another option is to use the trace flag 2371 to actually change the formula used to trigger the update.

3. CXPACKET wait type

This is one of the most common types of waits I see on large SQL Server systems when I am asked to study their query performance.

Sadly, I still see a lot of people make the initial assumption that the problem should be solved by having the query or the entire SQL Server set the maximum parallelism (MAXDOP) to 1. In general, problems can be handled or statistical maintenance can be done through appropriate indexes. It is also possible that the cached plan for this query is not optimal, and you can use sp_recompile to mark it as recompiled, set it to recompile at the query level, or simply use DBCC FREEPROCCACHE with plan handles to expel the plan. It's best to exhaust these options before you decide to change MAXDOP to 1, because you may throw away a lot of processing power without realizing it.

4. Misunderstanding that the timeout expires before it is completed.

This is huge. In addition to some very extreme behavior, you may handle two basic types of timeouts for SQL Server. These are connection timeouts and operation (or query) timeouts. In both cases, these values are set by the client connected to the SQL Server. On the server side, there is a remote query timeout setting, but this is a very extreme case.

Operation timeout is the most common and probably the most misunderstood situation I have ever encountered. The reason boils down to a simple factor: the client that executes the command sets the maximum time to wait for the command to complete. If this maximum is reached before completion, the command is aborted. An error is raised from the client.

Often, timeout errors trigger panic patterns because the errors look scary. In fact, this is not much different from clicking the stop button in SQL Server Management Studio, because the query takes too long. It will appear exactly the same in the profiler trace with error = 2 (abort).

Timeouts like this tell us that the query takes longer than expected. We should go into "performance tuning" mode instead of "something is broken" mode. The error message from the client is good information about where you can start to relocate your work centrally.

This is true for any system that uses RDBMS for repositories. Your database needs some TLC from time to time. Without it, you may indeed encounter some timeouts from your customers. We spend a lot of time optimizing the performance of queries before they are released, but proper maintenance will ensure that they continue to work as expected.

5. Memory pressure

This is a big problem because I see it often and because it is often mistaken for poor disk performance.

There are many caches in SQL Server, but the most famous is the data cache (aka buffer pool). The easiest way to describe data caching is that it is data stored in memory rather than persisted to disk. It is advisable to keep a large amount of data in memory for a long time, because processing data in memory is usually much faster than having to perform a physical Ihand O.

Usually, memory stress manifests itself in several different symptoms. When viewed alone, some of these symptoms can lead you to wrong and sometimes costly conclusions.

The two misleading symptoms are that you may start to see higher-than-normal latency for the entire disk subsystem, and you may start to see unusually high waits associated with disk activity. If you only look at these two symptoms, you may conclude that you need to work on your disk system.

This is why it is so important to display all relevant indicators on a dashboard. You must look at the larger picture and make memory-related data available along with disk activity and waiting to help you get a clearer picture of what's really happening.

Usually, I will see that the PLE (life expectancy of pages) for this server is quite low. The larger the buffer cache, the higher the "critical" threshold for PLE. The more data flows into and out of the buffer, the worse the situation when "loss" occurs. Another consideration is non-uniform memory access (NUMA). When multiple NUMA nodes are involved, the way PLE counters are calculated can make this value very misleading.

I also usually see persistent high lazy writer activity and SQL Server page errors (SQL Server goes to disk). Sometimes I see what I call a buffer tear. This happens when the data buffer fluctuates up and down, often creating jagged (or torn) edges on Performance Advisor's historical charts. I may also see that the unusually large plan cache reduces the available memory of the data cache.

All these factors together constitute memory stress. There are many ways to deal with them, but the important thing is that this is not a disk problem. I will not call your storage area network contact and order new hardware based on this situation. Once the memory pressure is under control, SQL Server doesn't need to go to disk so much, and some disk-related symptoms may disappear completely!

The above is about the content of this article on "what are the common performance problems in SQLServer?" I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

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.