Record the paging Optimization of SQLServer and talk about using Row

Recently, project responses showed that when the server CPU usage was high, Our Event Query page was very slow. It took four or more minutes to query several records, in addition, it takes so much time to flip the second page, which is certainly unacceptable. It also allows the SQL Server Profiler to capture the statement on site. Use ROW_NUMBER () to paging me

Recently, project responses showed that when the server CPU usage was high, Our Event Query page was very slow. It took four or more minutes to query several records, in addition, it takes so much time to flip the second page, which is certainly unacceptable. It also allows the SQL Server Profiler to capture the statement on site. Use ROW_NUMBER () to paging me

Recently, project responses showed that when the server CPU usage was high, Our Event Query page was very slow. It took four or more minutes to query several records, in addition, it takes so much time to flip the second page. This is certainly unacceptable and can be used on site.SQLServerProfilerThe statement is crawled.

Use ROW_NUMBER () for paging

Let's look at the paging statements captured at the site:

select top 20 a.*,ag.Name as AgentServerName,,d.Name as MgrObjTypeName,l.UserName as userName from eventlog as a left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm left join addrnode as c on b.AddrId=c.Id left join mgrobjtype as d on b.MgrObjTypeId=d.Id left join eventdir as e on a.EventBm=e.Bm left join agentserver as ag on a.AgentBm=ag.AgentBm left join loginUser as l on a.cfmoper=l.loginGuid where a.OrderNo not in  (select top 0 OrderNo  from eventlog  as a left join mgrobj as b on a.MgrObjId=b.Id left join addrnode as c on b.AddrId=c.Id  where 1=1 and a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' and b.AddrId in ('02109000',……,'02109002') order by  AlarmTime desc )  and 1=1 and a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' and b.AddrId in ('02109000',……,'02109002') order by  AlarmTime DESC

This is a typical paging method using two top pages. The principle is to first find outpageSize*(pageIndex-1)(T1), and thenTopOutputPageSizeRecords that are not in T1 are the records on the current page. This query is inefficient mainly becausenot in. Refer to my previous article "How programmers solve SQL Server's CPU usage:"Indexes are useless for expressions that do not use the SARG operator".

UseROW_NUMBERPaging:

WITH cte AS(select a.*,ag.Name as AgentServerName,d.Name as MgrObjTypeName,l.UserName as userName,b.AddrId,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNofrom eventlog as a WITH(FORCESEEK) left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm left join addrnode as c on b.AddrId=c.Id left join mgrobjtype as d on b.MgrObjTypeId=d.Id left join eventdir as e on a.EventBm=e.Bm left join agentserver As ag on a.AgentBm=ag.AgentBm left join loginUser as l on a.cfmoper=l.loginGuid where a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' AND b.AddrId in ('02109000',……,'02109002'))SELECT * FROM cte WHERE RowNo BETWEEN 1 AND 20;

The execution time is increased from 14 seconds to 5 seconds, which indicates that Row_Number paging is more efficient.top topPagination is much more elegant.

The "spoofing" query engine allows queries to be queried as expected

But why does it take 5 seconds to query 20 records, especially when a time index is added to this table, refer to the index mentioned in "How programmers solve SQL Server's CPU usage.

I try to remove this sentenceAND b.AddrId in ('02109000',……,'02109002')In less than one second, 538 records are queried. With the location restriction clause added, the result is 204 rows. Why is the time difference between the small result set and the time spent? View the execution plan and find another index instead of the time index.

Put this question on the SQLServer group, and soon Gao sang replied: To achieve the effect of removing the location restriction, useAdddrId+'' in.

What does it mean? I didn't understand it at the moment. Why didn't I understand my statement? Soon someone added that the query engine would be spoofed. "Spoofing"? I still don't understand, but I did it. I copied the above cte statement intact and then copied it.AND b.AddrId in ('02109000',……,'02109002')ChangedAND b.AddrId+'' in ('02109000',……,'02109002')A little bit of execution, God !!! Execution completed in less than 1 second. The execution plan is a pair of time indexes:

Afterwards, I remembered the previously seen Optimization Principle of the query engine. If your conditions contain operators or functions, the query engine will discard optimization and execute table scanning. My head suddenly turned around.b.AddrId+''If the previous query engine tries to add the mgrObj table for optimization, the join query between the two tables will greatly increase the estimated number of records.b.AddrId+''The query engine first selects the record based on the time index to achieve the effect, that is, the cte is executed first.inCondition, not in cteinConditional click. So it turns out!Sometimes, excessive optimization of the query engine will lead to the opposite effect. If you know the optimization principle, then you can use some tips to optimize the query engine as expected..

ROW_NUMBER () the problem of large page size

It's not over yet. My colleagues later responded to me and found the number of pages next to the page! What? I re-run the preceding statement and set the time range to. The number of records is limited to 19981 to 20000. It takes about 30 seconds to query and check the execution plan. Why?

Gao sang suspected that there were too many keys lookup. We recommend that you retrieve the rid by PAGE and then make the key lookup. I don't understand what this sentence means. Print out the execution plan and IO:

Look at IO. Obviously, the more pages it reads from several other associated tables, the more pages it reads. I guess,When Row_Number is paged, if a table connection exists, the records are sorted to the number of returned records. The preceding records are all associated with the table connection.As a result, the more pages that follow the scan, the slower the scan because more associated tables are to be scanned.

Is there no way? Song Sang stood up bravely at this time: "You addforceseek". This is just like trying it out right away.

Use the forceseek prompt to force the table to take the index

I checked the following information:

Tips introduced in SQL Server2008ForceSeekWhich can be used to replace index Scanning

Then, add this sentence to the eventlog table to see what will happen?

As a result, the query plan has changed and a prompt is displayed, indicating that the index is missing. The query time is changed to 18 seconds after the query is performed in this way! But check IO, as shown above, is not reduced. However, I finally learned a new skill, and Song Sang was eager to help me later at night.

Place other tables not involved in the where clause outside the cte.

According to the above IO, some people mentioned thatleft joinThe table is placed outside the cte. This is a way, So divideeventlog,mgrobj,addrnodeThe statement is as follows:

WITH cte AS(select a*,b.AddrId,b.Name as MgrObjName,b.MgrObjTypeId          ,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNofrom eventlog as aleft join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm left join addrnode as c on b.AddrId=c.Id where a.AlarmTime>='2011-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' AND b.AddrId+'' in ('02109000',……,'02109002'))SELECT a.* ,ag.Name as AgentServerName,d.Name as MgrObjTypeName,l.UserName as userNameFROM cte a left join eventdir as e on a.EventBm=e.Bm left join mgrobjtype as d on a.MgrObjTypeId=d.Id left join agentserver As ag on a.AgentBm=ag.AgentBm left join loginUser as l on a.cfmoper=l.loginGuid WHERE RowNo BETWEEN 19980 AND 20000;

It works, IO is greatly reduced, and the speed is also increased to 16 seconds.

Table 'loginuser '. 1 scan count, 63 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table 'agentserver '. Scan count 1, logical reads 1617, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. Table 'mgrobjtype '. Scan count 1, logical reads 126, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. Table 'eventdir '. 1 scan count, 42 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table 'addrnode '. Scan count 1, logical reads 119997, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. Table 'worktable '. Scan count 0, logical read 0, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table 'eventlog '. 1 scan count, 5027 logical reads, 3 physical reads, 5024 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads. Table 'mgrobj '. 1 scan count, 24 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

We can see that the addrNode table still has a large scanning count. Can it be upgraded? At this time, I thoughtaddrNode,mgrobj,mgrobjtypeThree tables are joined for query, put in a temporary table, and theneventlogDoinner joinAnd then the query results are compared with other tables.left joinIn this way, IO can be reduced.

Use temporary table Storage paging records to reduce I/O in Table connections

IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObjSELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName INTO tmpMgrObj  FROM dbo.mgrobj mINNER JOIN dbo.addrnode a ON a.Id=m.AddrIdWHERE AddrId IN('02109000',……,'02109002');WITH cte AS(select a.*,b.AddrId,b.MgrObjTypeId          ,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNo,ag.Name as AgentServerName,d.Name as MgrObjTypeName,l.UserName as userNamefrom eventlog as aINNER join tmpMgrObj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBmleft join mgrobjtype as d on b.MgrObjTypeId=d.Id left join agentserver As ag on a.AgentBm=ag.AgentBm left join loginUser as l on a.cfmoper=l.loginGuid WHERE AlarmTime>'2011-12-01 00:00:00' AND AlarmTime<='2014-12-26 23:59:59') SELECT * FROM cte WHERE RowNo BETWEEN 19980 AND 20000IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObj

This query takes only 10 seconds. Let's take a look at IO:

Table 'worktable '. Scan count 0, logical read 0, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table 'mgrobj '. 1 scan count, 24 logical reads, 2 Physical reads, 23 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads. Table 'addrnode '. 1 scan count, 6 logical reads, 3 physical reads, 0 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads. ---------- Table 'loginuser '. Scan count 0, logical read 24, physical read 1, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table 'worktable '. Scan count 0, logical read 0, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table 'eventlog '. The scan count is 93, the logic reads 32773, the physical reads 515, the pre-read 1536, the lob logic reads 0, the lob physical reads 0, and the lob pre-read 0. Table 'tmpmgrobj '. 1 scan count, 3 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table 'mgrobjtype '. 1 scan count, 6 logical reads, 1 physical read, 0 preread, 0 lob logical reads, 0 lob physical reads, and 0 lob preread. Table 'agentserver '. 1 scan count, 77 logical reads, 2 Physical reads, 0 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads.

Except eventlog, IO of other tables is greatly reduced?

Forced Use of hash join

A netizen suggested that it can be forcibly used when the page size is large.hash joinTo reduce IO, and try to avoid using temporary tables by creating two subqueries. After adjustment, the final optimized SQL statement is as follows:

SELECT  *,ag.Name AS AgentServerName, l.UserName AS userNameFROM    ( SELECT    a.*,ROW_NUMBER() OVER (ORDER BY AlarmTime DESC) AS RowNo, b.AddrName , b.Name AS MgrObjNameFROM(SELECT    * FROM      eventlogWHERE     AlarmTime>= '2011-12-01 00:00:00' AND AlarmTime< '2014-12-26 23:59:59') AS aINNER HASH JOIN (SELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName,t.Name AS MgrObjTypeNameFROM dbo.mgrobj mINNER JOIN dbo.addrnode a ON a.Id=m.AddrIdINNER JOIN dbo.mgrobjtype t ON m.MgrObjTypeId=t.IdWHERE AddrId IN('02109000',……,'02109002')) AS b ON a.MgrObjId=b.Id AND a.AgentBM=b.AgentBm) tmp LEFT JOIN agentserver AS ag ON tmp.AgentBm = ag.AgentBmLEFT JOIN eventdir AS e ON tmp.EventBm = e.BmLEFT JOIN loginUser AS l ON tmp.cfmoper = l.loginGuidWHERE tmp.RowNo BETWEEN 190001 AND 190020

In the case of large paging, hash queries do not need to scan the previous page number, which can greatly reduce IO. Howeverhash joinIt is mandatory, so pay attention to it when using it. I should be a special case here.

Query analyzer prompt:"Warning: due to the use of the local join prompt, the join order is enforced ."

Let's take a look at the corresponding IO:

Table 'eventlog '. 5 scans, 5609 logical reads, 34 physical reads, 5636 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads. Table 'worktable '. Scan count 3, logical reads 375, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. Table 'worktable '. Scan count 0, logical read 0, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table 'mgrobj '. 5 scans, 24 logical reads, 8 Physical reads, 40 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads. Table 'mgrobjtype '. 1 scan count, 6 logical reads, 1 physical read, 0 preread, 0 lob logical reads, 0 lob physical reads, and 0 lob preread. Table 'addrnode '. Scan count 3, logical read 18 times, physical read 6 times, pre-read 0 times, lob logical read 0 times, lob physical read 0 times, lob pre-read 0 times. Table 'loginuser '. 1 scan count, 60 logical reads, 2 Physical reads, 0 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads. Table 'eventdir '. 1 scan count, 40 logical reads, 0 physical reads, 0 pre-reads, 30 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table 'agentserver '. 1 scan count, 1540 logical reads, 1 physical read, 0 preread, 0 lob logical reads, 0 lob physical reads, and 0 lob preread.

I/O performance is very good this time, and there is no large I/O caused by the increase in the number of pages after the query,Query time never usedhash joinUp to 12 secondsThe query time overhead should behashFound.

Let's look at the corresponding query plan. This is mainly because the sorting overhead is large.

Let's take a look at the difference between his estimation and execution. Why does sorting take up so much overhead?

Obviously, you only need to sort the selected result in the estimation, but the actual execution is to sort all the previous pages, and the final sorting occupies most of the overhead. Can this problem be solved? Please leave your reply!

Other optimization references

In another group discussion, we found thatROW_NUMBERThe slow query of the following pages by page is indeed plagued by many people.

Some people have suggested who will be so boring and turn the number of pages to thousands of pages? At the beginning, I thought so too. But after talking with others, I found that there was such a scenario, and our software providedLast pageThe result ...... Of course, one way is to remove the last page feature when designing the software. Another way of thinking is to reverse query after half the page number is queried, the last page is the first page.

Some people suggest placing the queried content in a temporary table, and adding the auto-incremental Id index to the temporary table. In this way, you can identify the Id for quick record selection. This is also a method. I plan to try it later. However, this method also has a problem, that is, it cannot be universal. You must construct a temporary table based on each table. In addition, when querying ultra-large data, too many records are inserted, because the existence of indexes is also slow, and every time this is done, it is estimated that the CPU is also quite tight. However, this is an idea.

Do you have any good suggestions? You may wish to discuss your ideas in comments.

Summary

Now, let's summarize what we learned in this optimization process:

• In SQLServer,ROW_NUMBERThe paging should be the most efficient and compatible with databases after SQLServer2005
  
• You can control the optimization process of the query engine through the tips of "spoofing" query engine.
  

• ROW_NUMBERPaging has performance problems in the case of large pages. You can use some tips to avoid this problem.

  • Use the index through cte whenever possible
    
  • DisablewhereThe conditional table is placed outside the cte of the page.
    
  • If you participatewhereIf there are too many tables with conditions, you can consider creating a temporary table for tables not involved in paging to reduce IO
    
  • Use it forcibly when the page size is largehash joinIo can be reduced to achieve good performance.
    

• Usewith(forceseek)You can force query to perform index query.

Finally, I would like to thank Gao sang, Song Sang, Xiao sang and other friends of the SQLServer group for their great help. This is a great group to prevent water flooding, I learned a lot about the database!

Note: As prompted by a user, update the following at on:

• If the number of records exceeds 10000hash joinForce hash connections to reduce IO (thanks to riccc on the 27 th floor)
  
• Removeleft joinInsteadinner joinConnection --left joinThe result is useless.addrId in ()Conditions (thanks to Xia Hao on the 32th floor)

References

• Qu Yan miscellaneous-The ROW_NUMBER function of egg pain
  
• Why is the paging technology of ultra-long list data complicated?