How does programmers solve SQL Server's CPU usage?

Problems encountered in the article directory use SQLServerProfiler to monitor the database SQL1: Find the latest 30 alarm events SQL2: Get the current total number of alarm records what SQL statements will cause high CPU? When you select the top record for the SQL query plan, try to index the fields in the order clause to view the SQL statements with high CPU usage.

Problems encountered in the article directory use SQLServer Profiler to monitor the database SQL1: Find the latest 30 alarm events SQL2: Obtain the current total number of alarm records which SQL statements will lead to high CPU? When you select the top record for the SQL query plan, try to index the fields in the order clause to view the SQL statements with high CPU usage.

Document directory

• Problems encountered
  

• Use SQLServer Profiler to monitor Databases

  • SQL1: Find the latest 30 alarm events
    
  • SQL2: Get the current total number of alarm records
    
• Which SQL statements cause high CPU usage?
  

• View the SQL query plan

  • When selecting the top record, try to create an index for the field in the order clause
    
  • View SQL statements with high CPU usage
    
  • Create indexes to reduce table scans
    
• Other Optimization Methods
  

• Summary

Problems encountered

Some colleagues reported that the server's CPU usage was too high. At first glance, it was basically 100%, my god. This is a big problem. Let's take a look.

Let colleagues check the system process and find that the CPU usage of SQLServer is high. The first thought was whether the report was generated at a high level. Because of this issue, it was still high to turn off the service program. Is it caused by a client program? However, when there are so many client connections, it is very easy to disconnect the network. After the network is disconnected, the CPU usage immediately drops. So where is the problem? It's time to sacrifice our powerful tool-SQLServer Profiler.

Use SQLServer Profiler to monitor Databases

I asked my colleagues to use SQLProfiler for monitoring for about 20 minutes, and then saved it as the tracking file *. rtc.

Let's take a look at the SQL statement:

SQL1: Find the latest 30 alarm events

select top 30  a.orderno,a.AgentBm,a.AlarmTime,a.RemoveTime,c.Name as AddrName,b.Name as MgrObjName,a.Ch,a.Value,a.Content,a.Level,ag.Name as AgentServerName,a.EventBm,a.MgrObjId,a.Id,a.Cfmoper,a.Cfm,a.Cfmtime,a.State,a.IgnoreStartTime,a.IgnoreEndTime,a.OpUserId,d.Name as MgrObjTypeName,l.UserName as userName,f.Name as AddrName2 from eventlog as a left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm left join addrnode as c on b.AddrId=c.Id left join mgrobjtype as d on b.MgrObjTypeId=d.Id left join eventdir as e on a.EventBm=e.Bm left join agentserver as ag on a.AgentBm=ag.AgentBm left join loginUser as l on a.cfmoper=l.loginGuid left join addrnode as f on ag.AddrId=f.Id where ((MgrObjId in (select Id from MgrObj where AddrId in ('','02100000','02113000','02113001','02113002','02113003','02113004','02113005','02113006','02113007','02113008','02113009','02113010','02113011','02113012','02113013','02113014','02113015','02113016','02113017','02113018','02113019','02113020','02113021','02113022','02113023','02113024','02113025','02113026'))) or (mgrobjid in ('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','11111111-1111-1111-1111-111111111111','11111111-1111-1111-1111-111111111111'))) order by alarmtime DESC

SQL2: Get the current total number of alarm records

select count(*)  from eventlog as a left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm left join addrnode as c on b.AddrId=c.Id left join mgrobjtype as d on b.MgrObjTypeId=d.Id left join eventdir as e on a.EventBm=e.Bm where MgrObjId in (select Id from MgrObj where AddrId in ('','02100000','02100001','02100002','02100003','02100004','02100005','02100006','02100007','02100008','02100009','02100010','02100011','02100012','02100013','02100014','02100015','02100016','02100017','02100018','02100019','02101000','02101001','02101002','02101003','02101004','02101005','02101006','02101007','02101008','02101009','02101010','02101011','02101012','02101013','02101014','02101015','02101016','02101017','02101018','02101019','02101020','02101021','02101022','02101023','02101024','02101025','022000','022001','022101','022102','0755','0755002')) and mgrobjid not in ('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','11111111-1111-1111-1111-111111111111','11111111-1111-1111-1111-111111111111')

This is a typical data acquisition and paging. One gets the total number of the latest paging records and one gets the paging records. It is precisely because the CPU usage is too high because the latest events are obtained. The business here is about every client, which executes a database query every 3 seconds to display the latest alarm events. Okay. What should I do if I find the culprit?

Which SQL statements cause high CPU usage?

I checked the article online and concluded the following:

1. Compile and recompile

Compiling is the process in which SQL Server generates execution plans for commands. SQL Server needs to analyze what instructions are to be done, and analyze the structure of the table to be accessed, that is, the process of generating the execution plan. This process is mainly used for various computations, so the CPU usage is concentrated.

After the execution plan is generated, it is cached in the memory for reuse. But not all of them can be reused. In many cases, due to a change in the data volume or a change in the data structure, re-compilation is required for execution in the same sentence.

2. sort and aggregation)

During query, order by, distinct, avg, sum, max, and min operations are often performed. After the data has been loaded into the memory, we need to use the CPU to finish the calculation. Therefore, the CPU usage of these operations is higher.

3. Join Operation

When the statement requires two tables to be connected, SQLServer usually chooses the Nested Loop or Hash algorithm. To complete the algorithm, you need to run the CPU. Therefore, join may sometimes bring about a concentrated CPU usage.

4. The Count (*) Statement is executed too frequently.

Especially for large tables, Count (), Because Count () If there are no conditions later, or the condition cannot be indexed, it will cause a full table scan and a large number of CPU operations.

We all know the general reasons, but the two SQL statements mentioned above seem to all have these problems. Which is the biggest culprit? How can we optimize it?

View the SQL query plan

The SQL Server query plan clearly shows which step consumes the largest resources. Let's take a look at getting the top 30 records:

Sorting actually accounts for 94% of resources. It turned out to be it! The colleague immediately thought that it would be faster to sort by orderno. Execute the preceding statement in SQLServer. After clearing the cache, it is about 2 ~ 3 seconds, and then the sorting field is changed to orderno, less than 1 second, it is useful. However, the order of orderno is not exactly the same as that of alarmTime. The order of orderno cannot replace alarmTime. What should I do? I think, because top is selected, and orderno is a clustered index, the first 30 records can be selected and returned immediately without traversing the entire result.If alarmTime is an index field, can the sorting be accelerated?

When selecting the top record, try to create an index for the field in the order clause

Create an index first:

IF NOT EXISTS(SELECT * FROM sysindexes WHERE id=OBJECT_ID('eventlog') AND name='IX_eventlog_alarmTime')CREATE NONCLUSTERED INDEX IX_eventlog_alarmTime ON dbo.eventlog(AlarmTime)

View the execution plan:

No. The time-consuming Sort in the query just disappeared. How can we verify that it can effectively reduce our CPU? Is it necessary to deploy it on site, of course not.

View SQL statements with high CPU usage

SELECT TOP 10 TEXT AS 'SQL Statement'    ,last_execution_time AS 'Last Execution Time'    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]    ,execution_count AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes    ,qp.query_plan AS "Query Plan"FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY total_elapsed_time / execution_count DESC

We will compare the CPU before and after index creation:

It has been significantly reduced.

Create indexes to reduce table scans

Let's take a look at how to optimize the count (*) statement, because the difference between the above sentence and the count statement lies in the order by statement. Old Rules: Use the query plan.

Statementselect count(0) from eventlogAt first glance, the table has more than records. If you query 30 data records each time, you have to traverse the 20-plus-w table twice. Can this eliminate CPU consumption. Let's see if we can use the relevant conditions to reduce table scanning. Obviously, we can create an index for MgrObjId:

CREATE NONCLUSTERED INDEX IX_eventlog_moid ON dbo.eventlog(MgrObjId)

However, no matter how I try it, the index is NOT used. Is it possible that the IN clause and the NOT IN clause cannot use the index, which will definitely cause table scanning. So I checked the information on the Internet and found my article. Here are some answers:

SQLSERVER has some suggestions on writing search argument/SARG statements.

Indexes are useless for expressions that do not use the SARG operator, and it is difficult for SQLSERVER to use a more optimized approach for them. Non-SARG operators include

NOT, <>, not exists, not in, not like, and internal functions, such as Convert and Upper.

But this just shows that IN can create an index. I was puzzled. After some consultation, I got the answer:

It may not be good to use indexes. sqlserver determines whether it is a table scan or an index Scan Based on the proportion of duplicate values in the fields you query.

It makes sense, but I checked that the duplicate value is not high. How can this problem be solved.

The key is that if you select a field, the index performance is worse. Your select field id, addrid, agentbm, mgrobjtypeid, and name are not in the index.

What is missing is an index !!! I have mentioned the importance of index inclusion in this article, "How do I process 0.4 billion million records every day in SQLServer". I didn't expect to repeat it here. Practice is really too important!

Index the SQL statement by creating an index.

Well, immediately create related indexes:

IF NOT EXISTS(SELECT * FROM sysindexes WHERE id=OBJECT_ID('eventlog') AND name='IX_eventlog_moid')CREATE NONCLUSTERED INDEX IX_eventlog_moid ON dbo.eventlog(MgrObjId) INCLUDE(EventBm,AgentBM)

Let's take a look at the query plan:

No. No eventlog table is scanned. Let's compare the CPU before and after:

Obviously, the optimization of this count still takes effect for the top query statement. So far, after these two queries are used up, there is no high CPU usage.

Other Optimization Methods

• The database can be queried only when an event alert is triggered or removed from the server.
  
• Optimize the preceding query statements. For example, count (*) can be replaced by count (0). For more information, see SQL development skills (2).
  
• Optimize the statement, first query all mgrobjids, and then connect
  
• Add indexes for management objects and location tables
  
• After an index is added, the insertion of the event table will be slow. How can we optimize it? You can create an index by partitioning. When you are not busy every day, you can move new records to the created index partition.

Of course, these optimization methods are the follow-up work, and I have basically finished what I want to do.

Summary

• The CPU usage on the server is too high. First, check the system process to determine the process that causes the CPU usage too high.
  
• SQL Server Profiler can easily monitor which SQL statements are executed for a long time and consume the most CPU resources.
  
• You can use SQL statements to view the CPU usage of each SQL statement.
  
• Statements that cause high CPU usage include memory sorting, table scanning, and compilation plans.
  
• If you use Top click to select the first few statements, try to create an index for the Order By clause. This will reduce the sorting of all the selected results.
  
• When you use Count to query the number of records, try to create an index for the relevant fields of the where clause to reduce table scanning. If multiple tables are joined, the related table join fields are created in the include index.
  
• Reduces SQL statement queries by means of server-side notifications
  
• Use table partitioning to minimize the impact of slow table insertion due to index addition.

References

• Which of the following SQLSERVR statements in and exists is highly efficient? Test by myself
  
• SQL Server Cpu 100% common causes and Optimization
  
• Check for high CPU usage in SQLSERVER
  
• Everyone is a DBA (XII) Query Information Collection script assembly

???? Finally, I would like to thank the DBA Hua Zi for his enthusiastic guidance.