Problema de rendimiento al consultar vistas entre servidores vinculados

Se aplica a: SQL Server

Síntomas

Ejecutar una consulta en vistas de un servidor vinculado tarda más tiempo que ejecutar la misma consulta directamente en una tabla base en el servidor vinculado.

Causa

La ejecución de una consulta en una vista y una tabla base da como resultado un comportamiento diferente debido a la estimación de cardinalidad de datos, que se usa para calcular el número esperado de filas devueltas. El número de vistas de consulta se establece en un valor constante de 10 000 mientras que el número para consultar tablas base se deriva de información estadística.

Estimación de cardinalidad en una tabla base consultada por servidores vinculados

El proveedor OLE DB de Microsoft (SQLOLEDB) (ya no se mantiene) o Microsoft® OLE DB Driver 18 for SQL Server® (MSOLEDBSQL) admiten estadísticas de distribución en tablas base. SQL Server puede usar las estadísticas y el histograma en servidores vinculados de la misma manera que cualquier consulta normal. Por ejemplo, si crea un servidor vinculado denominado LS1 que tiene la base de datos con la AdventureWorks2019 Sales.SalesOrderDetail tabla, las siguientes consultas pueden usar el histograma estadístico:

SELECT * FROM LS1.AdventureWorks2019.Sales.SalesOrderDetail;
SELECT * FROM LS1.AdventureWorks2019.Sales.SalesOrderDetail WHERE SalesOrderID=43659;

Los siguientes planes gráficos de consulta muestran el número estimado de filas devueltas en cada consulta remota.

Estimación de cardinalidad en vistas consultadas por servidores vinculados

Las consultas de servidor vinculado en vistas no usan la estimación de cardinalidad basada en estadísticas. La estimación de cardinalidad en una vista es un valor constante de 10 000. En la prueba siguiente se muestra lo siguiente:

1. Cree una vista denominada dbo.View1 que haga referencia a la Sales.SalesOrderDetail tabla de la AdventureWorks2019 base de datos del servidor vinculado LS1.

   USE AdventureWorks2019;
   GO
   CREATE VIEW [dbo].[view1]
   AS
   SELECT * FROM Sales.SalesOrderDetail;
   

2. Consulte la vista de forma remota.

   SELECT * FROM LS1.AdventureWorks2019.dbo.view1;
   

3. La estimación de cardinalidad de la consulta muestra exactamente 10 000 filas.

   

Estimación de cardinalidad en una vista consultada por servidores vinculados con la cláusula WHERE

Si ejecuta una consulta con la WHERE cláusula en las vistas de un servidor vinculado, la estimación de cardinalidad con vistas también es un valor constante. Sin embargo, el valor varía en función del nivel de compatibilidad de la base de datos.

Nota:

El valor de estimación de cardinalidad no se verá afectado por el nivel de compatibilidad de la base de datos donde se define la vista.

• Para un nivel de compatibilidad de base de datos de 120 o superior (nueva versión del estimador de cardinalidad), la estimación de cardinalidad es 100.
• Para un nivel de compatibilidad de base de datos de 110 o inferior (estimador de cardinalidad heredado), la estimación de cardinalidad es 1000.

Consulte las siguientes consultas para obtener un ejemplo:

SELECT * FROM LS1.AdventureWorks2019.dbo.view1
WHERE SalesOrderID=43659 
    OPTION (USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));
GO
SELECT * FROM LS1.AdventureWorks2019.dbo.view1 
WHERE SalesOrderID=43659
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Consulte la siguiente estimación de cardinalidad de las consultas para obtener un ejemplo.

Solución

En la mayoría de los casos, no se necesita ninguna acción porque la mayoría de las cargas de trabajo de aplicaciones no se verán afectadas por la estimación de cardinalidad en las vistas cuando se consultan desde servidores vinculados. Si la carga de trabajo se ve afectada, use uno de los métodos siguientes:

• Actualice y cambie la vista como una vista indizada. La vista indizada tiene al menos un índice con las estadísticas correspondientes. Consulte las siguientes consultas para crear y consultar una vista indizada.

  Nota:

  Este método expondrá estadísticas reales que representan los datos subyacentes.

  --Set the options to support indexed views.
  SET NUMERIC_ROUNDABORT OFF;
  SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
     QUOTED_IDENTIFIER, ANSI_NULLS ON;
  GO
  IF OBJECT_ID ('Sales.vOrderDetails1', 'view') IS NOT NULL
     DROP VIEW Sales.vOrderDetails1;
  GO
  --Create view with schemabinding
  CREATE VIEW Sales.vOrderDetails1
     WITH SCHEMABINDING
     AS  
        SELECT SalesOrderID, 
         SalesOrderDetailID, 
         CarrierTrackingNumber, 
         OrderQty, 
         ProductID,
         UnitPrice,
         UnitPriceDiscount,
         LineTotal,
         ModifiedDate
        FROM Sales.SalesOrderDetail
  GO
  --Create an index on the view
  CREATE UNIQUE CLUSTERED INDEX IDX_V1
     ON Sales.vOrderDetails1 (SalesOrderID, ProductID);
  --Select from the materialized view
  SELECT * FROM LS1.adventureworks2019.Sales.vOrderDetails1;
  

  Consulte la siguiente estimación de cardinalidad de la consulta para obtener un ejemplo.

  

• Si ejecuta una consulta con la WHERE cláusula , puede aumentar la estimación de cardinalidad mediante (Query Hint OPTIONUSE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Este enfoque puede ayudar en algunos casos, especialmente cuando se une con tablas grandes.

  Nota:

  Este método es adecuado para los casos en los que la vista expone muchas filas.

• Ejecute la consulta directamente en las tablas base en lugar de las vistas en el servidor vinculado si procede.

Consulte también

• Estimación de cardinalidad (SQL Server)
• Supervisión y optimización del rendimiento
• Sugerencias de consulta
• Sugerencias de consulta USE HINT
• Guía de arquitectura de procesamiento de consultas