Program Ape is how to solve SQL Server accounted for cpu100%

Article Directory

• Problems encountered
• Monitoring the database using SQL Server Profiler
  • SQL1: Find the latest 30 alarm events
  • SQL2: Gets the current total number of alarm records
• What SQL statements are causing the CPU to be too high?
• View a query plan for SQL
  • Try to index the field of the order clause when you select the top record
  • To view SQL statement CPU High statements
  • Reduce table scans by establishing related indexes
• Other optimization methods
• Summarize

Problems encountered

Have a colleague reaction server CPU is too high, a look at the basic is 100%, my God, this is a big problem, hurriedly first look.

Ask a colleague to view the system process and find that SQL Server has a high CPU footprint. The first thought is not the report generated when the high, because this block before the problem, shut down the service program, or high. is the client program causing it? But with so many client connections, it's hard not to shut down each one, so it's easy to disconnect the network. After the network disconnects, the CPU drops immediately. So where exactly is the problem, it's time to sacrifice our weapon--sqlserver Profiler.

Monitoring the database using SQL Server Profiler

Let colleagues use sqlprofiler monitoring for about 20 minutes, and then save as a trace file *.RTC.

Let's see what the hell is wrong with sql:

SQL1: Find the latest 30 alarm events

Select topA.orderno,a.agentbm,a.alarmtime,a.removetime,C.NameAs Addrname,b.NameAs Mgrobjname,a.ch,a.Value,a.Content,a.Level, AG.NameAs Agentservername,a.eventbm,a.mgrobjid,a.Id,a.cfmoper,a.cfm,a.cfmtime,a.state,a.ignorestarttime,a.ignoreendtime, A.opuserid,D.NameAs Mgrobjtypename,L.usernameAs UserName,F.NameAs AddrName2From eventlogAs aLeftJoin MgrobjAs BOn A.mgrobjid=b.Idand A.AGENTBM=B.AGENTBMLeftJoin AddrnodeAsCOn b.addrid=C.IdLeftJoin MgrobjtypeAsDOn b.mgrobjtypeid=D.IdLeftJoin EventdirAsEOn a.eventbm=E.bmLeftJoin AgentserverAs AGOn A.agentbm=ag. AgentbmLeftJoin LoginuserAsLOn a.cfmoper=L.loginguidLeftJoin AddrnodeAsFOn AG. Addrid=F.IdWhere (MgrobjidIn (SelectIdFrom Mgrobjwhere AddridIn (‘‘,' 02100000 ',' 02113000 ',' 02113001 ',' 02113002 ',' 02113003 ',' 02113004 ',' 02113005 ',' 02113006 ',' 02113007 ',' 02113008 ',' 02113009 ',' 02113010 ',' 02113011 ',' 02113012 ',' 02113013 ',' 02113014 ',' 02113015 ',' 02113016 ',' 02113017 ', ' 02113018 ',  ' 02113019 ',  ' 02113020 ' ,  ' 02113022 ',  ' 02113023 ',  ' 02113025 ', or (Mgrobjid in ( ' 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: Gets the current total number of alarm records

SelectCOUNT (*)From eventlogAs aLeftJoin MgrobjAs BOn A.mgrobjid=b.Idand A.AGENTBM=B.AGENTBMLeftJoin AddrnodeAsCOn b.addrid=C.IdLeftJoin MgrobjtypeAsDOn b.mgrobjtypeid=D.IdLeftJoin EventdirAsEOn a.eventbm=E.bmwhere MgrobjidIn (SelectIdFrom Mgrobjwhere AddridIn (‘‘,' 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 ' ,  ' 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 typical to get the data and pagination of the data, one to get the latest total number of paging records, one to get the paging record, is to get the latest event caused by the CPU is too high. The business here is about every client, performing a database lookup every 3 seconds to show the latest alarm events. All right, the culprit found it, how to solve it?

What SQL statements are causing the CPU to be too high?

The following article was viewed on the internet and concluded that:

1. Compiling and recompiling

Compilation is the process by which SQL Server generates execution plans for instructions. SQL Server analyzes what the directive does, analyzes the table structure it is going to access, that is, the process of generating the execution plan. This process is mostly done in a variety of calculations, so the CPU uses a relatively concentrated place.

After the execution plan is generated, it is cached in memory for reuse. But not all of them can be reused. In many cases, the same sentence executes as the data volume changes, or the data structure changes, and it is re-compiled.

2. Sorting (sort) and aggregation calculations (aggregation)

In the query, often do order by, distinct such operations, will do the AVG, SUM, max, min Such aggregation calculation, after the data has been loaded into memory, it is necessary to use the CPU to finish these calculations. So the statements for these operations will have more CPU usage.

3. Table join (join) operation

SQL Server often chooses the Nested Loop or Hash algorithm when a statement requires two tables to be connected. The completion of the algorithm runs the CPU, so joins can sometimes lead to a relatively centralized CPU usage.

4.Count (*) statements are performed too frequently

In particular, the large table count (), becauseif there is no condition after count (), or if the condition is not indexed, it will cause a full table scan, also cause a large number of CPU operations

The general reason, we all know, but specifically to our above two SQL, seems to have mentioned above these problems, then in the end which is the biggest culprit, how can we optimize?

View a query plan for SQL

SQL Server's query plan clearly tells us exactly which step consumes the most resources. Let's take a look at the record of getting TOP30:

The sort actually accounted for 94% of the resources. It turns out to be it! Colleagues immediately thought, with OrderNo sort will quickly. The above statements are executed in SQL Server first, after clearing the cache, about 2-3 seconds, and then the sorting field to orderno,1 seconds is not, and sure enough useful. But the order of OrderNo and AlarmTime is not exactly the same, OrderNo sort can not replace alarmtime sort, then how to do? I think, because the choice is top, then because OrderNo is a clustered index, then select the first 30 records, you can return immediately, do not need to traverse the entire result, if AlarmTime is an indexed field, can you speed up the sorting?

Try to index the field of the order clause when you select the top record

To build the 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)

When viewing the execution plan:

See no, just query time-consuming sort has disappeared, then how to verify that it can effectively reduce our CPU, it is not to deploy to the scene, of course not.

To view SQL statement CPU High statements

SELECT TOP10TEXTAs' SQL Statement ', last_execution_timeAs' Last Execution time ', (total_logical_reads + total_physical_reads + total_logical_writes)/Execution_countAs [Average IO], (total_worker_time/execution_count)/1000000.0As [Average CPUTime (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      

Let's compare the CPU before and after the index:

has been significantly reduced.

Reduce table scans by establishing related indexes

Let's take a look at how the count (*) is optimized, because the difference between this and count is the order by. The usual, 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 data, unexpectedly to traverse this 20 more w table two times, can not consume CPU. Let's see if we can use the relevant conditions to reduce the table scan. Obviously, we can index the Mgrobjid:

CREATE NONCLUSTERED INDEX IX_eventlog_moid ON dbo.eventlog(MgrObjId)

But no matter how I try, it's not using the index, the IN clause and the not-in clause are not able to make use of the index must cause a table scan. So on-line search information, find the text of the birch, there are answers:

SQL Server has some suggestions for the wording of the filter (search Argument/sarg)

For expressions that do not use the SARG operator, indexes are useless, and SQL Server makes it difficult for them to use a more optimized approach. Non-SARG operators include

Not, <>, not EXISTS, not in, no like, and intrinsic functions, for example: Convert, Upper, etc.

But it just means that in can be indexed. Baffled, after a consultation, got the answer:

Not necessarily the index is good, SQL Server determines whether a table scan or an index scan depends on the percentage of duplicate values of the fields of your query

It makes sense, but I look at the next, duplicate values are not high, how can there be problems.

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

Really a language awakened dream of people, missing is the inclusion index!!! About the importance of including indexes I've already mentioned this article, "How I handled 430 million records per day in SQL Server," and I didn't expect to get a second somersault here. Practice, really is too important!

Let the SQL statement go through the index by creating a include index

All right, set up the relevant index immediately:

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:

See no, there are no tables scanned for EventLog tables. Let's compare the CPU to the front and back:

Obviously, this count optimization, the query top statement remains in effect. So far, these two queries have been used up, there is no high CPU phenomenon.

Other optimization methods

• Through the service side of the push, there is an event alarm or cancellation to query the database.
• Optimization of the above query statements, such as COUNT (*) can be substituted with count (0)--refer to the SQL Development tips (ii)
• Optimize the statement, first query out all the Mgrobjid, and then do the connection
• Add indexes for Management objects, place tables, and more
• After the index is added, the event table is inserted slowly and can be optimized again. Can be partitioned to index, when not busy every day, the new records into the partition to build the index

Of course, these optimization means are the follow-up thing, I have to do is basically finished.

Summarize

• Server CPU is too high, first look at the system process to determine the process that caused the CPU too high
• Easily monitor which SQL statements take too long and consume the most CPU through SQL Server Profiler
• The SQL statement is a way to see how much CPU each SQL statement consumes
• The high CPU is caused by a large number of statements: memory sequencing, table scanning, compiling plans, and so on.
• If you use the top brush to select the previous few statements, you can index the ORDER BY clause as much as possible, which reduces the sorting of all the brush results
• When using count to query the number of records, try to reduce the table scan by indexing the relevant fields of the WHERE clause. If more than one table joins, the related table join fields are built into the containing index
• Reduce the query of SQL statements by server-side notifications
• Reduce the slow impact of table insertions by adding indexes as a result of table partitioning

Reference articles

• sqlservr statement in and exists which high efficiency I test proof
• Common causes and optimizations for SQL Server Cpu 100%
• SQL Server to troubleshoot high CPU usage
• Everyone is DBA (XII) Query information collection script compilation

Finally, thanks to the blog Park DBA, the enthusiastic advice of Birch Tsai.

Program Ape is how to solve SQL Server accounted for cpu100%