Vandaag werd ik door mijn collega getriggerd om onderzoek te doen naar extreme lengtes in (n)varchar velden en de impact daarvan op performance. Welnu… riemen vast, want dit is belangrijk.
Management samenvatting
Een goede inrichting en adequate keuze van veldlengtes bij (n)varchar en (n)char velden draagt bij aan een optimaler query plan en daarmee aan een betere query en server performance in het algemeen.
Hoe zit dit precies
U kent het wel: “Laten we dat tekstveld alvast maar wat langer maken, zodat we ook in de toekomst deze velden niet zo snel hoeven te verlengen”. Die beslissing kan impact hebben op de query performance van uw query’s op deze velden. SQL Server kijkt bij het maken van het query plan (die SQL Server bij query’s gebruikt) naar de grootte van de tekstvelden die in de query wordt gebruikt. Op basis daarvan maakt SQL Server een inschatting van het benodigd geheugen. Hoe meer de veldlengte van het veld in de tabel afwijkt van de daadwerkelijke vulling des te meer geheugen zal SQL Server onnodig aan deze query toewijzen. Gelukkig zal SQL Server (2019) u hier in het Actual Query Plan op wijzen bij het meest linkse (SELECT) component. U vindt in het Actual Query Plan op dat component een geel driehoekje met een waarschuwing.
Eén en ander is met onderstaand TestLab verduidelijkt. Deze set met query’s helpt u hopelijk beter te begrijpen wat er precies fout gaat. Dit is overigens geen ‘incident’. De onjuiste bepaling van het benodigd geheugen zal continue fout gaan wanneer de betrokken tabel (of, indien dit zich in meerdere tabellen voordoet, betrokken tabellen) in een query wordt betrokken. Draaien er parallel meerdere query’s, dan zal dit potentieel onnodig veel resources op het geheugen van uw SQL Server gaan leggen; en geheugen is schaars en kostbaar.
Vragen staat vrij!
/*
Auteur : Mickel Reemer
Datum : 23 maart 2022
Thanks to : Erik Darling
Credits by : https://www.brentozar.com/archive/2017/02/memory-grants-data-size/
SQL Server : SQL Server 2019 Developer Edition
Doel van het script:
--------------------
Aantonen dat varchar die te groot zijn aangemaakt
oorzaak zijn excessive memory grants op een SQL Server
*/
-- Zet tijdstatistieken aan
SET STATISTICS TIME ON
-- Verwijder de testtabel wanneer deze al bestaat
DROP TABLE IF EXISTS dbo.MemoryGrants;
-- Maak de testtabel aan
CREATE TABLE dbo.MemoryGrants
(
ID INT PRIMARY KEY CLUSTERED,
Ordering INT,
Field1 VARCHAR(10),
);
-- Voeg de records in, in de tabel
INSERT dbo.MemoryGrants WITH (TABLOCK)
( ID, Ordering, Field1)
SELECT
c,
c % 1000,
REPLICATE('X', c * 10 % 10)
FROM (
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS c
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) x
-- Zet Actual Query Plan aan en draai de query hieronder
SELECT mg.ID, mg.Ordering, mg.Field1 --Returning a VARCHAR(10)
FROM dbo.MemoryGrants AS mg
ORDER BY mg.Ordering
GO
-- De doorlooptijd is 1751ms. De memory grant is 13MB
/*
Dat gaan we nu nog een keer testen, maar dan met varchar(8000)
*/
-- Verwijder de testtabel opnieuw wanneer deze bestaat
DROP TABLE IF EXISTS dbo.MemoryGrants;
-- Maak de testtabel aan
CREATE TABLE dbo.MemoryGrants
(
ID INT PRIMARY KEY CLUSTERED,
Ordering INT,
Field1 VARCHAR(8000), -- We gaan nu Field1 een lengte van 8000 geven
);
-- Voeg de records in, in de tabel
INSERT dbo.MemoryGrants WITH (TABLOCK)
( ID, Ordering, Field1)
SELECT
c,
c % 1000,
REPLICATE('X', c * 10 % 10)
FROM (
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS c
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) x
-- Zet Actual Query Plan aan en draai de query hieronder
SELECT mg.ID, mg.Ordering, mg.Field1 --Returning a VARCHAR(10)
FROM dbo.MemoryGrants AS mg
ORDER BY mg.Ordering
GO
-- De memory grant is 574MB. De doorlooptijd is 2706ms
