- Victor Escott
- Jul 30, 2020
- 3 min read
Cada vez que le digo a alguien que el costo porcentual en el plan de ejecución miente y que no deben confiar en él cuando se está haciendo performance tuning a una consulta, recibo diferentes reacciones / preguntas como:
¡Tú eres el que miente!
¿Puedes probarlo?
¿Por qué lo pusieron si esta mal?
Siempre que recibo esta pregunta, les comparto una publicación hecha por mi amigo Forrest [t,b] . Deberías leer esta publicación antes de ponerte a leer mis locuras, el es mucho más inteligente que yo :)
Aqui esta el post: Percentage Non Grata
Ayer, alguien comenzó a hacer muchas preguntas después de leer el post arriba mencionado, esta persona asegura de que los porcentajes estaban mal porque las estadísticas fueron forzadas, etc. Intenté explicar que el punto principal fue probado, y las estadísticas no son perfectas en un entorno de producción, pero no fue suficiente para convencerlo(s), así que decidí compartir mi pantalla y demostrarlo en una sesión en web.
Aquí hay un script para crear nuestro laboratorio, una tabla y una función (UDF).
--\\Create table
CREATE TABLE [dbo].[UserProfile] (UserID INT PRIMARY KEY CLUSTERED IDENTITY(1,1)
,AboutMe NVARCHAR(1500) NOT NULL
, MyValue1 INT NOT NULL);
GO
--\\ Insert some data :)
INSERT INTO [dbo].[UserProfile](AboutMe,MyValue1)
SELECT TOP (2000000) AboutMe= REPLICATE( LEFT(CAST(NEWID() AS VARCHAR(520)), 5), 3)
, ROW_NUMBER() OVER (ORDER BY a.name)%3
FROM sys.all_objects a
CROSS JOIN sys.messages c
UNION ALL
SELECT TOP (2000000) AboutMe= REPLICATE( LEFT(CAST(NEWID() AS VARCHAR(520)), 5), 3)
, 3
FROM sys.all_objects a
CROSS JOIN sys.messages c;
GO
--\\Create and index to help our query.
CREATE INDEX IX_Value1 ON [dbo].[UserProfile] (MyValue1) WITH FILLFACTOR=100;
GO
--\\ Create scalar function.
CREATE OR ALTER FUNCTION dbo.Total(@MyValue1 INT)
RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Total BIGINT;
SELECT @Total=COUNT_BIG(*)
FROM [dbo].[UserProfile]
WHERE MyValue1=@MyValue1;
RETURN @Total;
END;
GO
Una vez que la tabla esté lista, ejecutaremos las siguientes consultas, asegúrese de incluir el Plan de ejecución (Actual Execution Plan).
Nota: El nivel de compatibilidad se establecio en 140 (SQL 2017)
--\\ Compare the function and raw query
--\\ Include Actual Execution Plan (Ctrl + M)
SELECT Total=COUNT_BIG(*)
FROM [dbo].[UserProfile]
WHERE MyValue1=3;
SELECT dbo.Total(3) as Total;
Ambas consultas son idénticas, la misma lógica, los mismos resultados y el mismo número de lecturas, la única diferencia es la función (UDF). ¿Qué pasa con los porcentajes? Serán los mismos?. Por favor vea a continuación ...
SQL Server no puede expandir la lógica detrás de la función (UDF), por lo tanto, los porcentajes están mintiendo
Soy consciente de SQL Server 2019 y Froid, pero seamos honestos, no vamos a actualizar pronto y Froid no va a arreglar todos las funciones (UDF).
Las consultas anteriores no fueron manipuladas, entonces, ¿qué sucede si confundimos un poco a SQL Server?
Ejecutemos estas tres consultas:
SELECT A.MyValue1
,A.AboutMe
FROM [dbo].[UserProfile] A
WHERE A.MyValue1=3
ORDER BY A.MyValue1
OPTION(FAST 10); --\\ Give me the first 10 rows as fast as you can.
SELECT TOP (10) A.MyValue1
, A.AboutMe
FROM [dbo].[UserProfile] A
WHERE A.MyValue1=3
ORDER BY A.MyValue1; --\\ Let SQL server handle.
SELECT TOP (10) A.MyValue1
, B.AboutMe
FROM [dbo].[UserProfile] A
INNER LOOP JOIN [dbo].[UserProfile] B --\\Forcing Loop Join and selference, expecting SQL Server seeks using cluster and noncluster indexes.
ON A.UserID= B.UserID
WHERE A.MyValue1=3
ORDER BY A.MyValue1;
Consulta #1, regresa 2,000,000 filas, pero yo le pedí a SQL Server que regresara las primeras 10 filas lo más rápido posible (Query Hint).
Consulta #2, regresa 10 filas, una consulta muy simple y sin trucos.
Consulta #3, regresa 10 filas, he forzado a que se genere un Nested Loop Join con una referencia a si mismo.
Como podras anticipar, los porcentajes mienten nuevamente... Vea más abajo porfavor.
Si tiene alguna pregunta o sugerencia, hágamelo saber en los comentarios.
