One SQL Server tuning experience

Your current location: Home tutorial Programming Development mssql database one SQL Server tuning experience thanks to 3lian10 delivery time: Source: the health check of the database showed that the idle time of the SQLServer server was reduced and the I/O time was relatively idle. It is estimated that the CPU usage was high.

Your current location: Home> tutorial> programming> mssql database> one SQL Server tuning experience thanks to 3lian10 delivery time: Source: the health check of the database some time ago showed that the SQL Server had less idle time and I/O was relatively idle. It is estimated that the CPU usage was high.

Your current location: Home> tutorial> programming and development> mssql database> one SQL Server tuning experience

One SQL Server tuning experience

Thanks for the delivery time of 3lian10: 2013-09-06 Source: Sanlian tutorial

Some time ago, the database health check showed that the SQL Server had less idle time and I/O was relatively idle. It was estimated that it encountered a statement with high CPU usage.

Background: Our company is responsible for O & M of more than N systems and providing good software and hardware environments. As for the quality of application development, we cannot do anything about it.

To solve this problem, I thought:

Find the statement with the largest CPU usage.

Analyze the query plan.

Optimized.

1. Find the statement

Use the performance report provided by SQL Server (not the report service) to find the statement with the largest CPU usage. 1.

Performance Report

I selected "Performance-top query by total CPU time" to generate the following two reports, as shown in Figure 2:

Performance-top query by total CPU time

In a report, you cannot Copy the statement directly. You have to save it as Excel to Copy the statement. In addition, it often does not indicate which database the statement belongs :(.

It took me the power to find out the database in which the statement was executed, and then immediately back up the database and restore it to another non-production database to create an experimental environment.

I copied the statement and sorted out the format. As follows:

Double-click all code

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

Select *

Fromnetwork_listen

Where

Node_codein

(

Selectdistinctnode_code

Fromview_Log_Network_circsByUnit

Wherestatus = '1'

)

Or

Node_code =

(

Selecttop1nodeCode

FromTransmissionUnit_LocalInfo

)

And

Node_code <>

(

SelectparentNodeCode

FromTransmissionUnit_RouterInfo

WherenodeCode =

(

Selecttop1nodeCode

FromTransmissionUnit_LocalInfo

)

)

2. Analysis Statement

The execution plan is as follows:

The figure is too big. Let's take a look :(.

Query plan full Graph

Query Plan 1

Query Plan 2

Query Plan 3

From the perspective of the entire query plan, the main overhead is spent on the part-Two "clustered index scans ".

Let's take a look at these two numbers for "clustered index scans". What are the planes?

The query statement does not contain the Log_Nwtwork_circs table. Analyze the execution plan carefully. The biggest suspect is the view_Log_Network_circsByUnit view.

Check the definition of this attempt:

Double-click all code

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATEVIEW [dbo]. [view_Log_Network_circsByUnit]

AS

SELECTB .*

FROM (

SELECTnode_code, MAX (end_time) ASend_time

FROMLog_Network_circs

GROUPBYnode_code

)

LEFTOUTERJOIN

Dbo. Log_Network_circsB

ON

A. node_code = B. node_code

AND

A. end_time = B. end_time

Look dizzy, right?

3. Optimization

The SQL statement is not well written. I cannot change the SQL statement anyway, and it can be determined that the most time-consuming part of the entire query is used in this attempt.

Then we can only try to optimize this. This attempt involves a Log_Network_circs table. The following table structure is shown:

Double-click all code

1

2

3

4

5

6

7

8

9

10

11

12

13

CREATETABLE [dbo]. [Log_Network_circs] (

[Log_id] [varchar] (30) NOTNULL,

[Node_code] [varchar] (100) NULL,

[Node_name] [varchar] (100) NULL,

[Server_name] [varchar] (100) NULL,

[Start_time] [datetime] NULL,

[End_time] [datetime] NULL,

[Status] [varchar] (30) NULL,

CONSTRAINT [PK_LOG_NETWORK_CIRCS] PRIMARYKEYCLUSTERED

(

[Log_id] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

The data volume has 489957 records, not too large.

3. For tables that are frequently connected to other tables, indexes should be created on the connection fields;

It seems that a composite index is created on the node_code and end_time fields, which is roughly defined as follows:

Double-click all code

1

2

3

4

5

6

CREATEINDEX [idx _ Log_Network]

ONLog_Network_circs

(

Node_codeASC,

End_timeASC

)