SQL Server Optimization

Some time ago, the database health check showed that the SQL Server 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.

　　
Figure 1 Performance Report

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

　　
Figure 2 Performance-top query by total CPU time

In a report, you cannot copy the statement directly. You must 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:
Select *

From network_listen

Where

Node_code in

(

Select distinct node_code

From view_log_network_circsbyunit

Where status = '1'

)

Or

Node_code =

(

Select top 1 nodecode

From transmissionunit_localinfo

)

And

Node_code <>

(

Select parentnodecode

From transmissionunit_routerinfo

Where nodecode =

(

Select top 1 nodecode from transmissionunit_localinfo

)

)

 

 

2. Analysis Statement

The execution plan is as follows:

Figure 3 query plan full chart

　
Figure 4 query plan 1

　
Figure 5 query plan 2

　　
Figure 6 query plan 3

From the perspective of the entire query plan, the main overhead is spent on the part in Figure 5-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:
Create view [DBO]. [view_log_network_circsbyunit]

As

Select B .*

From (

Select node_code, max (end_time) as end_time

From log_network_circs

Group by node_code

)

Left Outer Join

DBO. log_network_circs B

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:

 

Create Table [DBO]. [log_network_circs] (
[Log_id] [varchar] (30) not null,
[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] primary key clustered
(
[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:

 

Create index [idx _ log_network]
On log_network_circs
(
Node_code ASC,
End_time ASC
)

 

For the sake of insurance, I copy the statement to be optimized to a file, then open "database engine optimization consultant", set up the database, and the following optimization results are obtained:
　

 

Create statistics [_ dta_stat_559341057_6_2] on [DBO]. [log_network_circs] ([end_time], [node_code])

Create nonclustered index [_ dta_index_log_network_circs_24_559341057 _ k2_k6] on [DBO]. [log_network_circs]
(
[Node_code] ASC,
[End_time] ASC
) With (sort_in_tempdb = OFF, ignore_dup_key = OFF, drop_existing = OFF, online = OFF) on [primary]

 

Well, the results are similar. Let's talk about the specific parameters.

Follow the suggestions provided by the database engine optimization consultant to create statistics and index.

Let's look at the optimized execution plan.

Obviously, the execution plan of view_log_network_circsbyunit is different.

No advertisement, no curative effect, and no statistical function. Run the following statement:

 

Set statistics Io on;
Set statistics time on;

 

(2 rows affected)

Table 'Log _ network_circs '. Scan count 2, logical reads 13558 times, physical reads 0 times, pre-reads 0 times, lob logic reads 0 times, lob physical reads 0 times, and LOB pre-reads 0 times.

Table 'transmissionunit _ routerinfo '. Scan count 0, logical read 2, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.

Table 'transmissionunit _ localinfo '. Scan count 3, logical read 6, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.

Table 'network _ Listen '. 1 scan count, 2 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

SQL Server execution time:

CPU time = 719 milliseconds, occupied time = 719 milliseconds.

(2 rows affected)

Table 'Log _ network_circs '. Scan count 2, logical reads 9 times, physical reads 0 times, pre-reads 0 times, lob logic reads 0 times, lob physical reads 0 times, lob pre-reads 0 times.

Table 'transmissionunit _ routerinfo '. Scan count 0, logical read 2, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.

Table 'transmissionunit _ localinfo '. Scan count 3, logical read 6, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.

Table 'network _ Listen '. 1 scan count, 2 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

SQL Server execution time:

CPU time = 0 ms, occupied time = 2 ms.

The number of logical reads, the total execution time is greatly reduced, and the optimization is quite successful.