警告 NoJoinPredicate
今天早上,我們的 SQL Server 2016 開始使用高 CPU 百分比(90%),通常是 30% 左右。
使用 sp_blitzcache 查看記憶體計劃,我在 CPU 時間的頂部看到了針對舊查詢的新計劃。
完整的查詢計劃在這裡。
這個計劃有一個關於沒有連接謂詞的警告。
(@TokenCentro nvarchar(36),@Tarjeta nvarchar(max) ,@TarjetaAux nvarchar(max) ) SELECT Socios.id, Socios.id AS IdSocio, Centros.id AS IDCentro, Centros.nombre, ISNULL(NombreWEB, Centros.Nombre) AS NombreWeb, Socios.Token, Foto, ISNULL(Socios.IdIdioma, 1) AS IdIdioma, AplicacionPropia, ISNULL(Socios.IdConf_Regional, 1) AS IdUnidad, Socios_Asignar_Centro.AplicacionWEB, CASE WHEN Socios.Email = '' THEN 1 ELSE CASE WHEN Socios.Email IS NULL THEN 1 ELSE 0 END END AS ConEmail, Centros.HorarioVisible, Centros.PerfilApp, TactilEntrenoSocio, Centros.ColorKiosco, Conf_Regional.PrimerDia, ISNULL(Centros.IdGrupoApp, 0) AS IdGrupoApp, Centros.requiere_LOPD, ISNULL(Centros.LOPD, '') AS LOPD, Socios.aceptada_lopd, Centros.IntegracionDieta, Empresas.Chat, Centros.field, Centros.valoracionApp, Empresas.p_Premios, Centros.Reservas, ISNULL(CustomCss, '') AS CustomCss, Socios_Asignar_Centro.PuedeReservar, zonasHorarias.Zona, zonasHorarias.ZonaIana, ISNULL(Socios.TokenExten, '') AS TokenExtern, Centros.field1, Centros.tipoServicioPush, Socios.Nombre AS NombreSocio, Socios.Apellidos AS ApellidosSocio, Centros_Opciones.forzarLogin FROM Socios INNER JOIN Socios_Asignar_Centro ON Socios_Asignar_Centro.IdSocio = Socios.Id INNER JOIN Conf_Regional ON Conf_Regional.Id = Socios.IdConf_Regional INNER JOIN Centros ON Centros.Id = Socios_Asignar_Centro.IdCentro AND Centros.FechaBaja IS NULL INNER JOIN Empresas ON Empresas.Id = Centros.IdEmpresa INNER JOIN zonasHorarias ON Socios.idzonahoraria = zonasHorarias.id INNER JOIN Centros_Opciones ON Centros_Opciones.IdCentro = Centros.Id INNER JOIN Centros_Tactiles ON Centros_Tactiles.IdCentro = Centros.Id WHERE Socios.tarjeta IS NOT NULL AND Socios.tarjeta <> '' AND Socios.tarjeta <> '0' AND ( Socios.Tarjeta = @Tarjeta OR Socios.Tarjeta = @TarjetaExtend ) AND Centros_Tactiles.Token = @Token AND ( Socios_Asignar_Centro.FechaBaja IS NULL OR Socios_Asignar_Centro.FechaBaja > CONVERT(DATE, GETUTCDATE())) ORDER BY IdCentro, IdSocio DESC
將參數長度從nvarchar(max)改為nvarchar(50)後,新方案又正常了。但是,我看不出問題出在哪裡。
有任何想法嗎?
跳出來的第一件事是表訪問方法上的差異
Socios
。兩個查詢都使用79271_79270_Socios
索引,但它們以非常不同的方式使用它們。慢查詢會尋找所有Tarjeta
不等於 的行''
。查詢優化器預期讀取表中 6498640 行中的 6496920 行。應用謂詞來過濾掉不等於 的行'0'
。應用過濾器運算符將行限制為等於@Tarjeta
OR的行@TarjetaAux
,它們是NVARCHAR(MAX)
變數。這種策略似乎很奇怪,並且對索引的使用效率不高。您說更改數據類型有幫助,所以讓我們做一些簡單的測試,看看 SQL Server 是否NVARCHAR(MAX)
以某種特殊方式處理謂詞。我將假設該
Tarjeta
列的數據類型為,NVARCHAR(50)
但我認為這對於此展示並不特別重要。這是我的範例數據:create table #demo (col nvarchar(50)); INSERT INTO #demo WITH (TABLOCK) SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 1000 FROM master..spt_values t1 CROSS JOIN master..spt_values t2; create index ix on #demo (col);
對於這個查詢,我得到了一個不錯的、簡單的索引查找,它的成本很低,並且只希望讀取和返回 1000 行:
SELECT col FROM #demo WHERE col = '123';
此查詢具有相同的計劃:
SELECT col FROM #demo WHERE col <> '0' AND col = '123';
看起來優化器足夠聰明,可以消除
col <> '0'
過濾器,因為它是多餘的。如果我將過濾器值轉換
NVARCHAR(MAX)
為意外的轉折點:SELECT col FROM #demo WHERE col = CAST('123' AS NVARCHAR(MAX));
查詢的性能很好。它仍然只從索引中讀取 1000 行。然而,這個計劃要復雜得多。XML 中似乎缺少一些資訊,我不知道如何正確讀取它。在 XML 中我發現了這個:
<ScalarOperator ScalarString="GetRangeWithMismatchedTypes(CONVERT(nvarchar(max),'123',0),CONVERT(nvarchar(max),'123',0),(62))">
這似乎暗示 SQL Server 必須進行某種轉換來處理
nvarchar(max)
常量值。您可以在Dynamic Seeks 和 Hidden Implicit Conversions中找到有關此的更多資訊。如果我使查詢更複雜一點,我會得到與您遇到的錯誤查詢類似的訪問模式:
SELECT col FROM #demo WITH (FORCESEEK) WHERE col <> '0' AND col = CAST('123' AS NVARCHAR(MAX));
該
FORCESEEK
提示試圖使查詢計劃更好,但沒有奏效。SQL Server 將從索引中讀取所有行,此計劃的成本比以前高得多。請注意,如果我們阻止 SQL Server 在過濾器上應用索引搜尋,<> '0'
我們可以獲得更合理的計劃:SELECT col FROM #demo WHERE LEFT(col, 50) <> '0' AND col = CAST('123' AS NVARCHAR(MAX));
但是,更好的解決方案是盡可能更改數據類型。當你像你一樣切換
to NVARCHAR(50)
時,我得到了一個很好的,簡單的索引搜尋,程式碼如下:DECLARE @Tarjeta nvarchar(50) = '123'; SELECT col FROM #demo WHERE col <> '0' AND col = @Tarjeta;
總之,在某些情況下,不必要的使用
VARCHAR(MAX)
似乎會導致額外的工作,並在其他情況下導致不必要的低效計劃。根據我們掌握的資訊,更改變數的類型似乎是正確的預防措施。關於 No Join Predicate 警告,當 SQL Server 將部分查詢實現為交叉連接時,您總是會看到這種情況。查看執行計劃中的 StarJoinInfo,了解有關為什麼會發生這種情況的更多資訊。有時交叉連接有利於性能和正確的做法。有時它們可能並不理想,但交叉連接可能是足夠好的計劃的一部分,或者是在優化器用完移動之前找到的最佳計劃。查詢優化器根據行估計為交叉連接分配了非常低的成本。在交叉連接中將 414 行連接到 1 行並不是什麼大問題,但如果這些估計不准確,則可能會出現問題。
也很難說為什麼這個計劃突然比以前更糟,尤其是我們通過
NVARCHAR(MAX)
上面的簡單測試觀察到的情況。也許有參數嗅探,SQL 伺服器記憶體了一個由不幸參數生成的計劃。RECOMPILE
重要的是要意識到 SQL Server 在沒有提示的情況下對計劃中的局部變數的值一無所知。您發布的更好的查詢具有硬編碼值,與需要 SQL Server 猜測或使用記憶體計劃的其他查詢相比,這不是公平的比較。