Requêtes hétérogènes et le cache

Submitted by st on

Les requêtes qui deviennent quasi-hétérogènes en cas de BD différentes sont toujours caché par SQL Server. Voici une exemple simple.

USE AdventureWorks
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE 
GO
 
SELECT E.EmployeeID, E2.EmployeeKey, E.LoginID, E2.FirstName, E2.LastName
FROM AdventureWorks.HumanResources.Employee E
   INNER JOIN AdventureWorksDW.dbo.DimEmployee E2
   ON E.LoginID = E2.LoginID
WHERE E.EmployeeID BETWEEN 10 AND 100
GO
 
SELECT E.EmployeeID, E.LoginID, E.Title, A.City, A.PostalCode
FROM AdventureWorks.HumanResources.Employee E
   INNER JOIN AdventureWorks.HumanResources.EmployeeAddress EA
      ON E.EmployeeID = EA.EmployeeID
   INNER JOIN AdventureWorks.Person.Address A
      ON EA.AddressID = A.AddressID
WHERE E.EmployeeID BETWEEN 10 AND 100
GO

Maintenenat, si vous regardez la table système "syscacheobjects", la requête hétérogène y est.

SELECT * 
FROM master..syscacheobjects 
WHERE objtype = 'AdHoc'