SQL Server performance Optimization (3) Querying performance bottlenecks with SQL Server Profiler

With regard to the use of SQL Server Profiler, there are already many tutorials on the web, such as this article: SQL Server Profiler: How to use and metrics. Microsoft Official Documentation: https://msdn.microsoft.com/zh-cn/library/ms179428 (v=sql.105). aspx is described in more detail.

Monitored using Profiler to get monitoring results.

================================== The following is irrelevant to the title of the article, an example of an optimization statement =========================================

A statement that takes a very long time to query

SelectT. Table address, T. Current heat, T. Instantaneous flow, T. cumulative flow, T. water temperature, T. Backwater temperature, T. temperature difference, T. Cumulative working time, T. Time of acquisition, T. Community number, T. Building number, T. Room number frommeasure_heat TwhereAcquisition Time=(Select Max(Acquisition time) fromMeasure_heatwhereTable Address=T. Table Address andAcquisition Time>= '2013-11-11'   andAcquisition Time<= '2014-4-11')

This is a statement that queries a large range of data with the Max function. After the query plan found that the acquisition time column is not indexed, resulting in the query will have a scan table operation. Later, the collection Time column is indexed, and then different query statements are used to analyze. Here are the results of the analysis

--If you query a wide range of databases, you find that using the Max function is the most efficient, and the other sort functions are generally efficient. --because the acquisition time is orderly.------------------------Use the max function--------------------------SET STATISTICSIo onDBCCDropcleanbuffers--Turn off cachingDBCCFreeproccache--Turn off cachingSELECTT. Table address, T. Current heat, T. Instantaneous flow, T. cumulative flow, T. water temperature, T. Backwater temperature, T. temperature difference, T. Cumulative working time, T. Time of acquisition, T. Community number, T. Building number, T. Room number frommeasure_heat TWHEREAcquisition Time=     (    ---A lot of time spent in this place, that is, how to get a table of the most recent acquisition time.     Select Max(Acquisition time) frommeasure_heatwhereTable Address=T. Table Address andAcquisition Time>= '2013-11-11'       andAcquisition Time<= '2014-1-11'    )    ---------------------Use the Row_number function-------------------------Create nonclustered index testmeasure_heat on measure_heat (Acquisition time, table address)SET STATISTICSIo onDBCCDropcleanbuffers--Turn off cachingDBCCFreeproccache--Turn off caching--if present, delete cache table--IF exists (SELECT * from #TableID)    --deposit into Database memory tableSELECT *  into#TableID from (    SELECTRow_number () Over(PARTITION byRoom numberORDER  byAcquisition TimeDESC) asRowID, id frommeasure_heatWHEREAcquisition Time>='2013-11-1 0:00:00'      andAcquisition Time<='2014-1-11 0:00:00'    --and Room number = 119) TWHERET.rowid= 1SELECT *  from#TableIDSELECTT. Table address, T. Current heat, T. Instantaneous flow, T. cumulative flow, T. water temperature, T. Backwater temperature, T. temperature difference, T. Cumulative working time, T. Time of acquisition, T. Community number, T. Building number, T. Room number fromMeasure_heat T Right JOIN#TableID b onT.id=b.idORDER  byt.idDROP Table#TableID------------------------Use the rank function----------------------DBCCDropcleanbuffers--Turn off cachingDBCCFreeproccache--Turn off cachingSelect *  into#table2 from (    SELECTTable address, RANK () Over(PARTITION byTable AddressORDER  byAcquisition time) asranktest frommeasure_heatWHEREAcquisition Time>='2013-11-1 0:00:00'  andAcquisition Time<='2014-1-11 0:00:00') TwhereRanktest= 1--SELECT * from #table2SELECTT. Table address, T. Current heat, T. Instantaneous flow, T. cumulative flow, T. water temperature, T. Backwater temperature, T. temperature difference, T. Cumulative working time, T. Time of acquisition, T. Community number, T. Building number, T. Room number fromMeasure_heat T Right JOIN#table2 A onA. Table address=T. Table Address andT. Acquisition Timebetween '2013-11-1 0:00:00'  and '2014-1-11 0:00:00'Drop Table#table2

Another statement that uses the Min function is also very poor performance

SelectT.*  frommeasure_heat TwhereCommunity number=' -'  andAcquisition Time=(Select min(Acquisition time) fromMeasure_heatwhereRoom number=T. Room number andAcquisition Time>='2013-11-6 0:00:00'  andAcquisition Time<'2013-11-7 0:00:00')

Workaround, create a memory table in SQL, first identify the part, and then use the first part of the results to query the final result. It can be checked in seconds.

SET STATISTICSIo onDBCCDropcleanbuffers--Turn off cachingDBCCFreeproccache--Turn off cachingSELECTT.* frommeasure_heat TWHERECommunity number=' -'  andAcquisition Time=(    SELECT min(Acquisition time) frommeasure_heatwhereAcquisition Time>='2013-11-6 0:00:00'      andAcquisition Time<'2013-11-7 0:00:00'      andRoom number=T. Room number)ORDER  byt.id------------Use the memory table------------SET STATISTICSIo onDBCCDropcleanbuffers--Turn off cachingDBCCFreeproccache--Turn off caching--if present, delete cache table--IF exists (SELECT * from #TableID)DROP Table#TableID--deposit into Database memory tableSELECT *  into#TableID from (    SELECTRow_number () Over(PARTITION byRoom numberORDER  byAcquisition Timedesc) asRowID, id frommeasure_heatWHEREAcquisition Time>='2013-11-6 0:00:00'      andAcquisition Time<'2013-11-7 0:00:00'     andCommunity number=' -') TWHERET.rowid= 1--SELECT * from #TableIDSELECTA.* fromMeasure_heat A Right JOIN#TableID b ona.ID=b.idORDER  bya.ID

SQL Server performance Optimization (3) Querying performance bottlenecks with SQL Server Profiler