SQL Server Performance Tuning notes (i)----failure effect of a fool's machine

Recently, the following project encountered an urgent problem, my old horse and young people to fight together. The problem is that many queries are incredibly slow when the pressure data is poured into the database.

Said there must be some basic guidelines for performance issues. The order of performance problems tunning

1 Architecture design (software architecture and database design, poor design is almost fatal)

2 Code defects (90% of performance problems)

3 Add index (this is to be determined according to the actual situation)

4 Resource tuning (cpu-> Memory->disk IO)

The network here is not a factor.

Take a look at the program's SQL, and some look at a bunch of subqueries composed of join, basically a glance can be judged, need to rewrite. We use the system to configure the SQL is dynamic, this design for the current tuning has brought convenience.

A very interesting phenomenon came up suddenly. One of the queries was slow (a minute later) and checked for SQL. This is the SQL text

SELECT ISNULL(a.CWB_NO,b.CWB_NO) AS CWB_NO,a.IMPORT_AWB_NO,
a.IMPORT_BWB_NO,ISNULL(a.PCS,0) AS RS2PCS,ISNULL(b.PCS,0) AS DECPCS,a.CCC_STATUS
FROM　
(SELECT * FROM　 TB_CWB WHERE　IMPORT_AWB_NO = @IMPORT_AWB_NO) a
FULL JOIN　
(SELECT * FROM　 TP_DECSUMMARY WHERE　AWB_NO = @IMPORT_AWB_NO) b
ON　 a.CWB_NO = b.CWB_NO AND　 b.AVAILABLE = 'Y'
WHERE　 a.AVAILABLE = 'Y'

Full join is not the core of the problem (because business rules are like this) or select *, but there may be differences between the SELECT * and the specified field, but there is no big difference.

I ran in the background for a little less than 0 seconds. But another programmer says it takes 59 seconds to run the same. Strange!!!

Take it and compare it to find the difference.

Because, what our system takes is to use. NET in the designation of the parameters of CMD, converted to the background of SQL, equal to the method of running sp_executesql. To put it simply, it is a substitution variable.

That is, the equivalent of the SQL text should be

SELECT ISNULL(a.CWB_NO,b.CWB_NO) AS CWB_NO,a.IMPORT_AWB_NO,
a.IMPORT_BWB_NO,ISNULL(a.PCS,0) AS RS2PCS,ISNULL(b.PCS,0) AS DECPCS,a.CCC_STATUS
FROM　
(SELECT * FROM　 TB_CWB WHERE 　IMPORT_AWB_NO = '25200000011') a
FULL JOIN　
(SELECT * FROM　 TP_DECSUMMARY WHERE　AWB_NO ='25200000011') b
ON　 a.CWB_NO = b.CWB_NO AND　 b.AVAILABLE = 'Y'
WHERE　a.AVAILABLE = 'Y'