update - recorrer cursor sql server

¿Cuándo un cursor FAST_FORWARD tendrá una tabla de trabajo(y es algo que debe evitarse)?

Question

Fondo

Me di cuenta mientras experimentaba con la ejecución de consultas totales que a veces el plan estimado solo muestra una "consulta de obtención"

y el plan real muestra capturas repetidas del escaneo de índice agrupado

en otras ocasiones (por ejemplo, al agregar un TOP a la consulta), el plan estimado muestra una etapa de "Consulta de población" que rellena una tabla de trabajo

Con el plan real que muestra un escaneo de índice agrupado para poblar la mesa de trabajo, se repiten las búsquedas en contra de esa mesa de trabajo.

Pregunta

1. ¿Qué criterios utiliza SQL Server para elegir un enfoque sobre el otro?
2. ¿Tendría razón al pensar que el primer método (sin el paso de población de la tabla de trabajo adicional) es más eficiente?

(Pregunta de bonificación: si alguien pudiera explicar por qué cada escaneo en la primera consulta cuenta como 2 lecturas lógicas que también pueden ser bastante esclarecedoras)

Información Adicional

He encontrado este artículo aquí que explica que los cursores FAST_FORWARD pueden usar un plan dinámico o un plan estático. La primera consulta en este caso parece estar utilizando un plan dinámico y el segundo un plan estático.

También descubrí que si lo intento

SET @C2 = CURSOR DYNAMIC TYPE_WARNING FOR SELECT TOP ...

El cursor se convierte implícitamente en un cursor de keyset por lo que está claro que el constructo TOP no es compatible con los cursores dinámicos, tal vez por las razones en la respuesta de Ruben. Todavía está buscando una explicación definitiva de esto.

Sin embargo, también he leído que los cursores dinámicos tienden a ser más lentos que sus contrapartes estáticas ( fuente 1 , fuente 2 ) lo cual me parece sorprendente dado que la variedad estática debe leer los datos fuente, copiarlos y leer la copia en lugar de solo lee los datos de origen. El artículo que mencioné anteriormente menciona que los cursores dinámicos usan markers . ¿Alguien puede explicar qué son estos? ¿Es solo un RID o la clave CI, o algo diferente?

Guión

SET STATISTICS IO OFF

CREATE TABLE #T ( ord INT IDENTITY PRIMARY KEY, total INT, Filler char(8000))

INSERT INTO #T (total) VALUES (37),(80),(55),(31),(53)

DECLARE @running_total INT, 
    @ord INT, 
    @total INT

SET @running_total = 0
SET STATISTICS IO ON
DECLARE @C1 AS CURSOR;
SET @C1 = CURSOR FAST_FORWARD FOR SELECT ord, total FROM #T ORDER BY ord;
OPEN @C1;
PRINT ''Initial FETCH C1''
FETCH NEXT FROM @C1 INTO @ord, @total ;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @running_total = @running_total + @total
  PRINT ''FETCH C1''
  FETCH NEXT FROM @C1 INTO @ord, @total ;
END

SET @running_total = 0
SET STATISTICS IO ON
DECLARE @C2 AS CURSOR;
SET @C2 = CURSOR FAST_FORWARD FOR SELECT TOP 5 ord, total FROM #T ORDER BY ord;
OPEN @C2;
PRINT ''Initial FETCH C2''
FETCH NEXT FROM @C2 INTO @ord, @total ;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @running_total = @running_total + @total
  PRINT ''FETCH C2''
  FETCH NEXT FROM @C2 INTO @ord, @total ;
END

PRINT ''End C2''
DROP TABLE #T 

Answer 1

Solo una corazonada, pero normalmente un TOP-ORDER BY requiere SQL Server para almacenar el resultado de algún modo (ya sea el resultado del escaneo del índice o incluso el resultado completo en una estructura temporal, o cualquier cosa intermedia).

Se podría argumentar que para los cursores esto también es necesario incluso al ordenar por la clave primaria (como en su ejemplo), ya que no puede permitir que un cursor TOP 5 regrese inesperadamente menos de 5 filas cuando el SELECT correspondiente devuelve exactamente 5 filas (o peor: el cursor devuelve más de 5 filas).

Esta situación extraña podría ocurrir teóricamente cuando hay eliminaciones o inserciones en la tabla después de que el rango del análisis de índice ya se haya determinado para el cursor, y las inserciones / eliminaciones caigan dentro del rango del análisis de índice, pero aún no ha terminado de recuperar. Para evitar que esto suceda, podrían equivocarse en el lado seguro aquí. (Y simplemente no optimizaron para las tablas #temp).

Sin embargo, una pregunta: ¿SQL Server permite un FETCH FROM SELECT TOP n sin una cláusula ORDER BY ? (No tengo una instancia de SQL Server ejecutándose aquí.) Puede ser interesante saber qué plan causa.

Answer 2

¿Qué criterios utiliza SQL Server para elegir un enfoque sobre el otro?

Es principalmente una decisión basada en los costos. Citando el artículo al que vinculó, "en situaciones donde el plan dinámico parece prometedor, la comparación de costos puede omitirse heurísticamente. Esto ocurre principalmente para consultas extremadamente baratas, aunque los detalles son esotéricos".

¿Tendría razón al pensar que el primer método (sin el paso de población de la tabla de trabajo adicional) es más eficiente?

Depende. Los planes de cursor dinámico y estático tienen diferentes puntos fuertes y débiles. Si finalmente se tocan todas las filas, es probable que el plan estático funcione mejor. Más sobre esto en un momento.

Está claro que la construcción TOP no es compatible con cursores dinámicos

Esto es verdad. Todos los iteradores en un plan de cursor dinámico deben poder guardar y restaurar el estado, explorar hacia delante y hacia atrás, procesar una fila de entrada para cada fila de salida y no bloquear. Top, en general, no cumple todos estos requisitos; la clase CQScanTopNew no implementa los métodos necesarios Set/Get/Goto/Marker() y ReverseDirection() (entre otros).

También he leído que los cursores dinámicos tienden a ser más lentos que sus contrapartes estáticas.

Esto suele ser cierto para los cursores de Transact-SQL, donde se toca la mayoría o la totalidad del conjunto de cursores. Hay un costo asociado con guardar y restaurar el estado de un plan de consulta dinámico. Cuando se procesa una sola fila en cada llamada, y finalmente se tocan todas las filas, esta sobrecarga de almacenamiento / restauración se maximiza.

Los cursores estáticos tienen la ventaja de hacer una copia del conjunto (que puede ser el factor dominante para un conjunto grande), pero el costo de recuperación por fila es bastante pequeño. Los conjuntos de claves tienen una sobrecarga de recuperación por fila más alta que la estática porque deben volverse a unir a las tablas de origen para recuperar columnas que no sean clave.

Los cursores dinámicos son óptimos cuando se accede a una fracción relativamente pequeña del conjunto, y / o la recuperación no se realiza a la vez. Este es un patrón de acceso típico en muchos escenarios comunes de cursor, simplemente no son los que las publicaciones de blog tienden a probar :)

Si alguien pudiera explicar por qué cada escaneo en la primera consulta cuenta como 2 lecturas lógicas que también pueden ser muy esclarecedoras

Esto se debe a la forma en que se guarda el estado para el escaneo, y se cuentan las lecturas de manera.

El artículo que mencioné anteriormente menciona que los cursores dinámicos usan marcadores. ¿Alguien puede explicar qué son estos? ¿Es solo un RID o la clave CI, o algo diferente?

Existen marcadores para cada iterador en un plan de cursor dinámico, no solo métodos de acceso. El ''marcador'' es toda la información de estado necesaria para reiniciar el iterador del plan en el punto que dejó. Para un método de acceso, una RID o clave de índice (con uniquifier si es necesario) es una gran parte de esto, pero no la historia completa de ninguna manera.