Query Optimization and Execution - Pro SQL Server Internals - page 468

Neither of these functions accesses the data, and therefore cannot introduce the Halloween effect. However,
SQL Server does not know that in the case of non-schema bound functions, and it adds a Spool operator to execution
plan, as shown in Figure 25-13 .
Figure 25-13. Halloween Protection and user-defined functions: Execution plans
Spool temporary tables are usually referenced as worktables in the I/O statistics for the queries. You should
analyze table spool-related reads during query performance tuning. While spools can improve the performance of
queries, there is overhead introduced by the unnecessary spools. You can often remove them by creating appropriate
indexes on the tables.
Parallelism
SQL Server can execute queries using multiple CPUs simultaneously. Even though parallel query execution can
reduce the response time of queries, it comes at a cost. Parallelism always introduces the overhead of managing
multiple threads.
Let's look at an example and create two tables, as shown in Listing 25-17. The script inserts 65,536 rows into
table dbo.T1 and 1,048,576 rows into table dbo.T2 .
Listing 25-17. Parallelism: Table creation
create table dbo.T1
(
T1ID int not null,
PlaceHolder char(100),
constraint PK_T1
primary key clustered(T1ID)
);
create table dbo.T2
(
T1ID int not null,
T2ID int not null,
PlaceHolder char(100)
);