W moim biurze mamy zapytanie, które jest dość brzydkie, ale działa całkiem dobrze w środowisku produkcyjnym i programistycznym (odpowiednio 20 sekund i 4 sekundy). Jednak w naszym środowisku testowym trwa 4 godziny. SQL2005 (+ najnowsze łaty) działa podczas produkcji i rozwoju. SQL2008R2 działa podczas testowania.
Rzuciłem okiem na plan zapytań i pokazuje on, że SQL2008R2 używa TempDB za pomocą buforu tabel (opóźnionego buforowania) do przechowywania zwróconych wierszy z połączonego serwera. Następnym krokiem jest pokazanie zagnieżdżonych pętli (lewe anty semi-złączenie) jako zjedzenie 96,3% zapytania. Linia między dwoma operatorami wynosi 5 398 MB!
Plan zapytań dla SQL 2005 nie pokazuje użycia tempdb i lewy anty semi-join.
Poniżej znajduje się zdezynfekowany kod, a plany wykonania zawierają plan z 2005 roku na górze, a 2008R2 na dole.
Co powoduje drastyczne spowolnienie i zmianę? Spodziewałem się zobaczyć inny plan wykonania, więc mi to nie przeszkadza. Niepokoi mnie dramatyczne spowolnienie czasu zapytania.
Czy muszę patrzeć na podstawowy sprzęt, ponieważ wersja 2008R2 korzysta z tempdb, muszę się zastanowić, jak zoptymalizować wykorzystanie tego?
Czy istnieje lepszy sposób na napisanie zapytania?
Dzięki za pomoc.
INSERT INTO Table1_GroupLock (iGroupID, dLockedDate)
SELECT
Table1.iGroupID,
GETDATE()
FROM Table1
WHERE
NOT EXISTS (
SELECT 1
FROM LinkedServer.Database.Table2 Alias2
WHERE
(
Alias2.FirstName + Alias2.LastName = dbo.fnRemoveNonLetter(Table1.FullName)
AND NOT dbo.fnRemoveNonLetter(Table1.FullName) IS NULL
AND NOT Alias2.FirstName IS NULL
AND NOT Alias2.LastName IS NULL
) OR (
Alias2.FamilyName = dbo.fnRemoveNonLetter(Table1.FamilyName)
AND Alias2.Child1Name = dbo.fnRemoveNonLetter(Table1.Child1Name)
AND NOT dbo.fnRemoveNonLetter(Table1.FamilyName) IS NULL
AND NOT dbo.fnRemoveNonLetter(Table1.Child1Name) IS NULL
AND NOT Alias2.Familyname IS NULL
AND NOT Alias2.Child1Name IS NULL
) OR (
Alias2.StepFamilyName = dbo.fnRemoveNonLetter(Table1.StepFamilyName)
AND Alias2.StepFamilyNameChild1 = dbo.fnRemoveNonLetter(Table1.StepFamilyNameChild2)
AND NOT Alias2.StepFamilyName IS NULL
AND NOT Alias2.StepFamilyNameChild1 IS NULL
AND NOT dbo.fnRemoveNonLetter(Table1.StepFamilyName) IS NULL
AND NOT dbo.fnRemoveNonLetter(Table1.StepFamilyNameChild2) IS NULL
)
) AND NOT EXISTS (
SELECT 1
FROM Table3
INNER JOIN Table4
ON Table4.FirstNameType = Table3.FirstNameType
INNER JOIN table5
ON table5.LastNameType = Table3.LastNameType
WHERE
Table3.iGroupID = Table1.iGroupID
AND Table3.bIsClosed = 0
AND Table4.sNameTypeConstant = 'new_lastname'
AND table5.sFirstNameConstant = 'new_firstname'
)
:: EDIT :: Wykonano zapytanie z innej instancji SQL2005, prawie taki sam plan wykonania jak „dobry”. Nadal nie jestem pewien, w jaki sposób dwie wersje 2005 działają lepiej na połączonym serwerze 2008R2, niż instancje 2008R2 z instancjami 2008R2.
Chociaż nie zaprzeczam, że kod może przydać się do pracy, to gdyby to był problem, czyż nie widziałbym tych samych planów wykonania we wszystkich moich testach? Niezależnie od wersji SQL?
:: EDYCJA :: Zastosowałem SP1 i CU3 do obu instancji 2008R2, wciąż nie ma kości. Specjalnie ustawiłem kolokację na połączonym serwerze, bez kości. Specjalnie ustawiłem uprawnienia mojego acct użytkownika na sysadmin w obu przypadkach, bez kości. Pamiętam również moje wewnętrzne funkcje SQL Server 2008 i rozwiązywanie problemów, zobaczymy, czy uda mi się to jakoś wyśledzić.
Dziękujemy wszystkim za pomoc i wskazówki.
:: EDYCJA :: Wprowadziłem różne zmiany uprawnień do połączonego serwera. Używałem loginów SQL, loginów domen, podszywałem się pod użytkowników, korzystałem z opcji „bądź przy użyciu tego kontekstu bezpieczeństwa”. Utworzyłem użytkowników po obu stronach połączonego serwera, którzy mają uprawnienia sysadmin na serwerze. Nie mam pomysłów.
Wciąż chciałbym wiedzieć, dlaczego SQL2005 wykonuje zapytanie tak bardzo różni się od SQL2008R2. Gdyby to zapytanie było złe, widziałbym czas działania 4+ zarówno na SQL2005, jak i SQL2008R2.