Sql-server – Problem understanding an execution plan

execution-planoptimizationsql server

I'm new to execution plans in SQL Server and I'm trying to understand why SQL Server is doing some operations. The query is: 

select P.PERSONID
from KSS_V_EMPLEADOS_PER_3_CERRADOS p, COMBHOMEACCT c, LABORACCT L, kss_carga_empl_centros k, CALENDAR ca
where p.PERSONID = c.EMPLOYEEID
and c.LABORACCTID = l.LABORACCTID
and k.empleado = p.PERSONNUM
and convert(date,ca.CALENDARDTM,103) between DBO.KSS_PrimerDiaMes(convert(DATE,@DFECHA, 103)) and DBO.KSS_UltimoDiaMes(convert (DATE,@DFECHA, 103))
and convert(date,ca.CALENDARDTM,103) between p.Inicio and p.Fin
and convert(date,ca.CALENDARDTM,103) between convert(date,c.EFFECTIVEDTM,103) and convert(date,c.EXPIRATIONDTM-1,103)
and convert(date,ca.CALENDARDTM,103) between k.FECHA_INI and k.FECHA_FIN
and l.LABORLEV4NM <> k.CENTRO

Table Information

  • KSS_V_EMPLEADOS_PER_3_CERRADOS is a view that has no indexes and involves some tables and functions. It has 12827 rows

  • COMBHOMEACCT c is a table with 16771 rows

  • LABORACCT is a table with 2460 rows

  • kss_carga_empl_centros is a table with 6594 rows

  • Calendar is a table with 146462 rows (it's just a table with a column that has one row for every day)

I'm able to understand most of the plan, but just before the last Inner join it does a Index spool that returns 830563 rows and I don't know why it is doing this index spool and cannot find where this number come from. I'm able to calculate all the other row counts without problem, but this branch of the tree calculates the days of the current month and passes it to the index spool operator. But what is this operator doing and why it returns 830563 rows?

I've uploaded the plan, just in case someone wants to see it:

https://www.brentozar.com/pastetheplan/?id=BkbEnkDNX

If you need more details please ask me

Thanks in advance

SQL plan of the query

Best Answer

The eager index spool is accessed multiple times, each time fetching about 4 rows.

NUTS

It's called by the Row Count Spool (note that the number of rebinds matches the number of executions):

NUTS

Which is called iteratively by the Nested Loops Join after the Sort operator:

NUTS

I'd be a bad consultant if I didn't also mention that using multi-statement table valued functions, and converting dates (some with date math) on the fly will often have a negative performance impact on queries. I realize that's not what your question is about, but hey.

If you'd like to learn more about spools (and many other operators), Fabiano Amorim has an excellent series of articles:

Operator of the Week – Spools, Eager Spool

Hope this helps.

Related Question