REVISIÓN: Reordenar las combinaciones externas con criterios de filtrado antes de combinaciones no selectivo y combinaciones externas

Nº DE ERROR: 356418 (SHILOH_BUGS)

Síntomas

Si envía una consulta que contenga al menos una combinación externa que tiene una condición de filtro en la cláusula WHERE en la tabla interna de la combinación externa (por ejemplo, una condición de filtrado en la tabla de la derecha de una combinación externa izquierda o de la tabla izquierda de una combinación externa derecha), SQL Server puede realizar combinaciones menos selectivas primero en lugar de realizar la combinación externa temprano y aplicar la condición del filtro. Si la condición de filtro de la combinación externa es uno de los criterios de la consulta más selectivos, pueden generar errores procesar los criterios temprano en el plan:
  • Tamaños mayores de combinación intermedia.
  • Mayor utilización de los recursos mediante el proceso de SQL Server.
  • Tiempo de respuesta más lento para la consulta.

Solución

Resolución para SQL Server 2005

Para resolver este problema, obtenga el service pack más reciente para SQL Server 2005. Para obtener más información, haga clic en el siguiente número de artículo para verlo en Microsoft Knowledge Base:
913089 cómo obtener el service pack más reciente para SQL Server 2005
Después de instalar el service pack de SQL Server 2005, debe activar el indicador de traza 4101 a resolver este problema.

Resolución de SQL Server 2000

Para resolver este problema, obtenga el service pack más reciente para Microsoft SQL Server 2000. Para obtener más información, haga clic en el siguiente número de artículo para verlo en Microsoft Knowledge Base:
290211 cómo obtener el service pack más reciente para SQL Server 2000
Nota: la revisión siguiente se creó antes del lanzamiento de Microsoft SQL Server 2000 Service Pack 3.

La versión en inglés de esta revisión debe tener los atributos de archivo siguientes o posteriores:
   Version       File name
-----------------------------

8.00.0584 Sqlservr.exe

Nota: debido a la interdependencia entre archivos, la revisión o característica que contiene los archivos más recientes también puede contener archivos adicionales.

Estado

Estado de SQL Server 2005

Microsoft ha confirmado que se trata de un problema de los productos de Microsoft que se enumeran en la sección "Aplicable a".
Este problema se corrigió primero en Microsoft SQL Server 2005 Service Pack 1.

Estado de SQL Server 2000

Microsoft ha confirmado que se trata de un problema de los productos de Microsoft que se enumeran en la sección "Aplicable a".
Este problema se corrigió primero en Microsoft SQL Server 2000 Service Pack 3.

Más información

El siguiente escenario artificioso combinación utiliza la base de datos pubs para demostrar el escenario:
set ansi_nulls offgo

use pubs
go

create procedure dbo.ansi_nulls_param @P1 varchar(11) as
select t.title_id, a.au_id, ta.title_id, s.stor_id from titles t
left outer join titleauthor ta on ta.title_id = t.title_id
inner join authors a on a.au_id = t.title_id
inner join sales s on s.title_id = t.title_id
where ta.title_id = @P1
go

exec dbo.ansi_nulls_param '123-45-6789'
go

drop proc dbo.ansi_nulls_param
go

--Slower Query Plan:
|--Filter(WHERE:([ta].[title_id]=[@P1]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([t].[title_id]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([s].[title_id]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[title_id]))
| | |--Index Scan(OBJECT:([pubs].[dbo].[sales].[titleidind] AS [s]))
| | |--Clustered Index Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [a]), SEEK:([a].[au_id]=[s].[title_id]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [t]), SEEK:([t].[title_id]=[s].[title_id]) ORDERED FORWARD)
|--Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [ta]), SEEK:([ta].[title_id]=[t].[title_id]) ORDERED FORWARD)

--Faster Query Plan:
|--Nested Loops(Inner Join, OUTER REFERENCES:([a].[au_id]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([t].[title_id]))
| |--Filter(WHERE:([ta].[title_id]=[@P1]))
| | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t].[title_id]))
| | |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [t]))
| | |--Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [ta]), SEEK:([ta].[title_id]=[t].[title_id]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [a]), SEEK:([a].[au_id]=[t].[title_id]) ORDERED FORWARD)
|--Index Seek(OBJECT:([pubs].[dbo].[sales].[titleidind] AS [s]), SEEK:([s].[title_id]=[a].[au_id]) ORDERED FORWARD)

Observe que la tabla titleauthor es la tabla derecha de una combinación externa izquierda y la condición de la cláusula WHERE en la tabla titleauthor que va a ser aplicada después de la combinación externa. El resultado muestra el plan de consulta original, más lento, donde primero se realizan todas las combinaciones internas y la combinación externa y el filtro se realiza por último, aunque es la condición más selectiva para la consulta. El segundo plan de consulta es un plan forzado que muestra el aspecto del plan más rápido, en la que la combinación externa y el filtro se realiza en primer lugar, seguida de las restantes combinaciones internas.

En este caso concreto, el optimizador continúa elegir el primer plan incluso después de aplicar la revisión. Esto es debido a que estas tablas son tan pequeñas y el costo estimado del primer plan es lo suficientemente bajo como para que se considere más conveniente ejecutar simplemente con ese plan de lo que es seguir buscando alternativas siguientes. A medida que aumentan los datos de las tablas, el costo del primer plan llegue a ser mayor y el optimizador comienza a elegir el segundo plan.
Propiedades

Id. de artículo: 318530 - Última revisión: 17 ene. 2017 - Revisión: 1

Comentarios