Apsara infrastructure management framework for SQL Server

In SQL Server, I found that the online interpretations of the false offline are scattered. What exactly is the false offline interpretation? I have studied it at home over the past few days and collected a lot of online information. The Chinese translation is used as a false offline function, the English name is spool. In the "SQLSERVER enterprise platform management practices" written by instructor Xu, I mentioned that

In SQL Server, I found that the online interpretations of the false offline are scattered. What exactly is the false offline interpretation? I have studied it at home over the past few days and collected a lot of online information. The Chinese translation is used as a false offline function, the English name is spool. In the "SQLSERVER enterprise platform management practices" written by instructor Xu, I mentioned that

Apsara infrastructure management framework for SQL Server

I found that the online interpretation of the counterfeit offline is very fragmented. What is the counterfeit offline?

I studied it at home these days and collected a lot of online information.

Offline is a Chinese translation, and the English name is spool.

In "SQLSERVER enterprise-level platform management practices for SQL Server" written by Mr Xu, I mentioned "false offline ".

In the section on SQL Server I/O Problems

In the performance monitor, there is a counter "worktables/sec ":

The number of worksheets created per second. For example, a worksheet can be used to store query spool, LOB, XML, table, and cursor temporary results.

In the SQL Server profiling execution plan, we also mentioned the concept of false offline.

(13) Sometimes the query optimizer needs to create a temporary worksheet in the tempdb database. If so

This means that there are icons marked as Index Spool, Row Count Spool or Table Spool in the graph execution plan.

Using a worksheet at any time may affect performance, because additional I/O overhead is required to maintain the worksheet.

I have also written an article about index counterfeiting.

At the time of writing this article, I was still confused about the counterfeit offline.

Several offline operators are mentioned in logical operators and physical operators in the execution plan of MSDN. (For details, refer to the final part of this article)

Eager Spool

Lazy Spool

Index Spool (sometimes called Nonclustered Index Spool)

Row Count Spool

Spool

Table Spool

Window Spool

Spool, Table Spool, Index Spool, Window Spool, and Row Count Spool are physical operators.

The Eager Spool and Lazy Spool are logical operators.

These operators describe how off-line operations work. here you need to be very clear about the differences between logical operators and physical operators.

Description in MSDN:

Logical operators: logical operators describe the relational algebra operations used to process statements. In other words, logical operators describe the operations to be performed.

Physical operators: Physical operators perform operations described by logical operators. Each physical operator is an object or routine that executes an operation.

For example, some physical operators can access columns or rows in tables, indexes, or views. Other physical operators perform other operations, such as calculation, aggregation, data integrity check, or join.

Physical operators have the associated overhead.

Note: There is no Eager Spool or Lazy Spool for window off-line, because it is both a logical and physical Operator !!

To put it simply, SQLSERVER performs an operation by the physical operator, and the specific operation is determined by the logical operator.

For example, if James wants to go to Guangzhou in Foshan, James can choose to drive a car to Guangzhou, step on a bicycle to Guangzhou, and ride a motorcycle to Guangzhou (equivalent to a certain operation)

James can follow the road conditions at that time:

(1) stepping on a bicycle: if the road is congested, you don't have to worry about stepping on a bicycle. The most cars can also pass. He can choose to step on the Eager Spool or slowly step on the Lazy Spool)

(2) driving a car: if the road is smooth, he can choose to drive a little faster (Eager Spool) or a little slower (Lazy Spool)

(3) motorcycle ride: if the road is congested, he can select to copy the path, and then start a little faster (Eager Spool) or a little slower (Lazy Spool)

I don't know what this metaphor means, but I cannot find a better one ~

In the graphic execution plan, you will find that Table Spool sometimes has an Eager Spool, and sometimes has a Lazy Spool

Because Table Spool is a physical operator, Eager Spool and Eager Spool are logical operators.

Table Spool (Table offline)

The SQL script is as follows:

Table off-line Eager Spool

Sales (EmpId INT, Yr INT, Sales MONEY) 5 INSERT Sales VALUES (1, 2005,120 00) 6 INSERT Sales VALUES (1, 2006,180 00) 7 INSERT Sales VALUES (1, 2007,250 00) 8 INSERT Sales VALUES (2, 2005,150 00) 9 INSERT Sales VALUES (2, 2006,600 0) 10 INSERT Sales VALUES (3, 2006,200 00) 11 INSERT Sales VALUES (3, 2007,240 00 ). empId, Yr, SUM (Sales) AS Sales17 FROM SalesEmpId, Yr WITH CUBE