Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to solve the problem of CPU 100% in SQL Server. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Monitoring the database using SQLServer Profiler

Have colleagues monitor it for about 20 minutes using SQLProfiler, and then save it as a trace file * .rtc.

Let's see which sentence SQL has a problem:

SQL1: find 30 alarm events of *

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, 02113015, 02113016, 02113016, 02113018, 02113019, 02113020, 02113024, 02113024, 02113024, 02113024, 02113024, 02113016, 02113016, 02113016, 02113016, 02113016, 02113016, 02113018, 02113019, 02113023, 02113024, 02113024, 02113025. ) or (mgrobjid in ('00000000-0000-0000-0000-00000000000000)) order by alarmtime DESC

SQL2: gets 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 (', '021000001), 02100001, 02100002, 02100003, 02100004, 02100005, 02100006, 02100007, 02100008, 02100009. 02100010, 02100011, 02100012, 02100013, 02100014, 02100015, 02100016, 02100017, 02100018, 02100019, 02101001, 02101002, 02101003, 02101005, 02101005, 02101007, 02101006, 02101007, 02101007, 02101008, 02101009, 02101012, 02101013, 0210101414, 0210151, 021016101017101017101402101010130210101402101002, 02101004, 02101005, 02101007, 02101008, 02101009, 02101010, 02101012, 0210101313, 02101014, 0210101414, 0210151, 0210161010171010171010140210101014021010101402101010140210100210101051010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101017101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010 02101021, 02101022, 02101023, 02101024, 02101025, 022000, 022001, 022101, 0755, 0755002,) and mgrobjid not in ('00000000-0000-0000-0000-0000000000000000, 0000000-0000-0000-0000-000000000000000000-0000000-0000-0000-000000000000000011 '11111111-1111-1111-1111-11111111111111)

This is a typical data acquisition and paging data. One gets the total number of * * paging records, and the other gets paging records. The CPU caused by the acquisition of * * events is too high. The business here is that each client performs a database lookup every 3 seconds to display alarm events. All right, the culprit has been found, how to solve it?

What SQL statements can cause the CPU to be too high?

I checked the next article on the Internet and came to the following conclusions:

1. Compile and recompile

Compilation is the process by which Sql Server generates an execution plan for an instruction. Sql Server analyzes what the instruction does and the table structure it accesses, that is, the process of generating the execution plan. This process is mainly to do a variety of calculations, so the use of CPU is more concentrated.

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

two。 Sorting (sort) and aggregate computing (aggregation)

When querying, we often do operations such as order by and distinct, and aggregate calculations such as avg, sum, max and min. After the data has been loaded into memory, we have to use CPU to complete these calculations. So the statement CPU for these operations will be used a little more.

3. Table join (Join) operation

When a statement requires two tables to be joined, SQLServer often chooses the Nested Loop or Hash algorithm. The completion of the algorithm needs to run CPU, so join sometimes brings places where CPU usage is more concentrated.

4.Count (*) statements are executed too frequently

Especially for large table Count (), because if there is no condition after Count (), or if the condition does not use an index, it will cause a full table scan and a large number of operations in CPU.

We all know the general reason, but specific to the above two SQL, both seem to have these problems mentioned above, so which one is the culprit, and how can we optimize it?

View SQL's query plan

SQLServer's query plan clearly tells us at which point the resources are being consumed. Let's first take a look at the records obtained from top30:

Ranking actually accounts for 94% of the resources. So that's it! Colleagues immediately wondered if sorting with orderno would be faster. First, execute the above statement in SQLServer, after clearing the cache, it will take about 2-3 seconds, and then change the sort field to orderno,1 less than seconds, which is really useful. But the order of orderno is not exactly the same as that of alarmTime, and the sort of orderno cannot replace the sort of alarmTime, so what should we do?

I think, because top is selected, and because orderno is a clustered index, the first 30 records can be returned immediately without having to traverse the entire result at all, so if alarmTime is an index field, can sorting be accelerated?

When you select a top record, try to index the fields of the order clause

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)

Before viewing the execution plan:

See, just now the query time-consuming Sort has disappeared, so how to verify that it can effectively reduce our CPU? is it necessary to deploy to the scene, of course not.

View SQL statements with high CPU

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 qs CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp ORDER BY total_elapsed_time / execution_count DESC

Let's compare the CPU before and after indexing:

It has been significantly reduced.

Reduce table scanning by establishing relevant indexes

Let's take a look at how to optimize the sentence count (*), because the difference between the above sentence and the count sentence is the ordering of order by. The old rule, use the query plan to see.

With the statement select count (0) from eventlog, the table already has more than 20 w records, each query 30 pieces of data, unexpectedly have to traverse this more than 20 w table twice, can not consume CPU. Let's see if we can take advantage of relevant conditions to reduce table scans. Obviously, we can index MgrObjId:

CREATE NONCLUSTERED INDEX IX_eventlog_moid ON dbo.eventlog (MgrObjId)

But no matter how hard I try, I didn't make use of the index. Is it possible that the IN clause and the NOT IN clause cannot make use of the index? it must cause a table scan. So I looked up the information on the Internet and found Hua Tsai's article, and there was an answer:

SQLSERVER has some suggestions on the writing of filter criteria (search argument/SARG).

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

NOT, NOT EXISTS, NOT IN, NOT LIKE and internal functions, such as Convert, Upper, etc.

But this just shows that IN can build indexes. I was puzzled. After some consultation, I got the answer:

It is not necessarily good to use an index. Sqlserver decides whether it is a table scan or an index scan based on the proportion of the repeated values of the fields in your query.

That makes sense, but I checked, the repetition value is not high, how can there be a problem?

The key is, your select field, this place uses the index so the performance is worse, your select field id,addrid,agentbm,mgrobjtypeid,name is not in the index.

What a word to wake up the dreamer, what is missing is to include the index! I already mentioned the importance of including indexes in this article, "how do I deal with 430 million records a day in SQLServer", but I didn't expect to stumble again here. Practice is really too important!

Let the SQL statement walk out of the index by building the include index

All right, set up the index right away:

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 inquiry plan:

See, there is no table scan for the eventlog table. Let's compare the CPU before and after:

Obviously, this count optimization is still valid for the query top statement. So far, after these two queries have been used, the CPU is no longer too high.

Other optimization methods

Through the push of the server, the database will be queried only if there is an event alarm or release.

Optimize the above query statement. For example, count (*) can be replaced by count (0).

Optimize the statement, first query out all the MgrObjId, and then do the connection

Add an index to the management object, location table, etc.

After the index is added, the insertion of the event table will be slow, how can it be optimized? You can index by partition, and move new records to the indexed partition when you are not busy every day.

So much for sharing about how to solve CPU 100% in SQL Server. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.