SQL Server Performance Tuning notes (ii)----The failure effect of the Fool machine continued

The failure effect is described above. Just want to explain that in the performance tunning aspect can only according to the situation to seek the reason and solves. This is an interesting process. Great principles are experiences that help us make fewer mistakes. Therefore, poor design inevitably leads to performance problems. Inexperienced programmers are bound to write bad code. But a good design can make up for a programmer's lack of experience. This to stop, this topic involved in quality management, it is too big.

One more example, the manifestation of failure effect.

Or above, we say the execution plan of 2 kinds of SQL writing. I choose a dual-core PC, which is equivalent to 2 single core CPUs.

There is a large table TB_CWB. Records about 30.4 million. (In the production environment is a small amount of data, in my test can be seen as a large table). The fn_clt_datetime has been indexed on the table.

1.SQL Wen Yi

　　select CWB_No,fn_Clt_Datetime,acctId_guid,fn_OrigZone_Id,DestSZMCode,DestZone_Id,CWBType,fn_Clt_Dat etime,fn_cwbtype,
　　 PayType,Payweight,StdPriceweight,StdFreight,IsCalculated,AFterDsctFreight,
　　 SchgFreight,InvcFreight,Salesamount,SchgDetail,SchgFreight_Remarks
　　from OCS_TB_CWB
　　WHERE
　　fn_Clt_Datetime between '2008-9-1' and '2008 -9-16'

Run the results and found the odd slow clinking. It takes 12 seconds to produce results.

Check the execution plan and find that the clustered index Scan (primary key) is the equivalent of a plan to select a full table scan. Because the CBO thinks this SQL is faster than using the index. See figure below