Use (n)varchar(max) parameters only when you really need to.

The problem:

I was checking the query store of a database where performance testing was done.
The top CPU resource consumer was a simple query on 1 table with a perfect covering index, but the query plan looked more complicated than expected.

The problem? The varchar(max) datatype of parameters in the query didn’t match the data type of the columns in the table.

Example:

Let me show you this with an example.
I adapted the code that Brent Ozar used for his demo about index rebuilds with ADR and RCSI:
https://www.brentozar.com/archive/2025/01/index-rebuilds-make-even-less-sense-with-adr-rcsi/

We’ll create a products table in a database called ‘test’ and load it with about 10 million rows.
Then we’ll add a covering index to support the query we’ll use as an example.

CREATE DATABASE Test;

GO
CREATE TABLE Test.dbo.Products 
    (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
     Cuisine VARCHAR(100) ,
     Adjective VARCHAR(100),
     Dish VARCHAR(100),
     QtyInStock INT) ; 
GO

WITH Cuisines AS
(
    SELECT Cuisine
    FROM (VALUES
        ('Italian'), ('Mexican'), ('Chinese'), ('Japanese'),
        ('Indian'), ('French'), ('Greek'), ('Spanish'), 
        ('Korean'), ('Thai'), ('Cajun'), ('Cuban'), ('Moroccan'), 
        ('Turkish'), ('Lebanese'), ('Vietnamese'), ('Filipino'), 
        ('Ethiopian'), ('Caribbean'), ('Brazilian'), ('Peruvian'), 
        ('Argentinian'), ('German'), ('Russian'), ('Polish'), 
        ('Hungarian'), ('Swiss'), ('Swedish'), ('Norwegian'), 
        ('Danish'), ('Portuguese'), ('Irish'), ('Scottish'), 
        ('English'), ('American'), ('Hawaiian'), ('Middle Eastern'), 
        ('Afghan'), ('Pakistani'), ('Bangladeshi'), ('Nepalese'), 
        ('Sri Lankan'), ('Tibetan'), ('Malay'), ('Indonesian'), 
        ('Singaporean'), ('Malaysian'), ('Burmese'), ('Laotian'), 
        ('Cambodian'), ('Mongolian'), ('Uzbek'), ('Kazakh'), 
        ('Georgian'), ('Azerbaijani'), ('Armenian'), ('Persian'), 
        ('Iraqi'), ('Syrian'), ('Jordanian'), ('Saudi Arabian'), 
        ('Israeli'), ('Palestinian'), ('Yemeni'), ('Sudanese'), 
        ('Somali'), ('Kenyan'), ('Tanzanian'), ('Ugandan'), 
        ('Zimbabwean'), ('South African'), ('Nigerian'), ('Ghanaian'), 
        ('Senegalese'), ('Ivory Coast'), ('Cameroonian'), 
        ('Malagasy'), ('Australian'), ('New Zealand'), ('Canadian'), 
        ('Chilean'), ('Colombian'), ('Venezuelan'), ('Ecuadorian'), 
        ('Paraguayan'), ('Uruguayan'), ('Bolivian'), ('Guatemalan'), 
        ('Honduran'), ('Nicaraguan'), ('Salvadoran'), ('Costa Rican'), 
        ('Panamanian'), ('Belizean'), ('Jamaican'), ('Trinidadian'), 
        ('Barbadian'), ('Bahamian'), ('Antiguan'), ('Grenadian'),
        ('Grandma''s'), ('Grandpa''s')
    ) AS t(Cuisine)
),
Adjectives AS
(
    SELECT Adjective
    FROM (VALUES
        ('Spicy'), ('Savory'), ('Sweet'), ('Creamy'), ('Crunchy'),
        ('Zesty'), ('Tangy'), ('Hearty'), ('Fragrant'), ('Juicy'),
        ('Crispy'), ('Delicious'), ('Mouthwatering'), ('Toasted'),
        ('Smoky'), ('Rich'), ('Light'), ('Buttery'), ('Tender'),
        ('Flaky'), ('Succulent'), ('Bitter'), ('Peppery'), ('Charred'),
        ('Piquant'), ('Nutty'), ('Velvety'), ('Chewy'), ('Silky'),
        ('Golden'), ('Satisfying'), ('Gooey'), ('Caramelized'), ('Luscious'),
        ('Hot'), ('Cool'), ('Bold'), ('Earthy'), ('Subtle'),
        ('Vibrant'), ('Doughy'), ('Garlicky'), ('Herby'), ('Tangy'),
        ('Mild'), ('Spiced'), ('Infused'), ('Ripe'), ('Fresh'),
        ('Citrusy'), ('Tart'), ('Pickled'), ('Fermented'), ('Umami'),
        ('Wholesome'), ('Decadent'), ('Savoured'), ('Fizzy'), ('Effervescent'),
        ('Melty'), ('Sticky'), ('Toothsome'), ('Crumbly'), ('Roasted'),
        ('Boiled'), ('Braised'), ('Fried'), ('Baked'), ('Grilled'),
        ('Steamed'), ('Seared'), ('Broiled'), ('Poached'), ('Simmered'),
        ('Marinated'), ('Dusted'), ('Drizzled'), ('Glazed'), ('Charred'),
        ('Seared'), ('Plated'), ('Whipped'), ('Fluffy'), ('Homemade'),
        ('Comforting'), ('Heartwarming'), ('Filling'), ('Juicy'), ('Piping'),
        ('Savored'), ('Seasoned'), ('Briny'), ('Doused'), ('Herbed'),
        ('Basted'), ('Crusted'), ('Topped'), ('Pressed'), ('Folded'),
        ('Layered'), ('Stuffed')
    ) AS t(Adjective)
),
Dishes AS
(
    SELECT Dish
    FROM (VALUES
        ('Pizza'), ('Taco'), ('Noodles'), ('Sushi'), ('Curry'),
        ('Soup'), ('Burger'), ('Salad'), ('Sandwich'), ('Stew'),
        ('Pasta'), ('Fried Rice'), ('Dumplings'), ('Wrap'),
        ('Pancakes'), ('Stir Fry'), ('Casserole'), ('Quiche'), ('Ramen'),
        ('Burrito'), ('Chow Mein'), ('Spring Rolls'), ('Lasagna'), ('Paella'),
        ('Risotto'), ('Pho'), ('Gyoza'), ('Chili'), ('Bisque'),
        ('Frittata'), ('Toast'), ('Nachos'), ('Bagel'), ('Croissant'),
        ('Waffles'), ('Crepes'), ('Omelette'), ('Tart'), ('Brownies'),
        ('Cupcakes'), ('Muffins'), ('Samosa'), ('Enchiladas'), ('Tikka Masala'),
        ('Shawarma'), ('Kebab'), ('Falafel'), ('Meatballs'), ('Casserole'),
        ('Pot Pie'), ('Fajitas'), ('Ravioli'), ('Calzone'), ('Empanadas'),
        ('Bruschetta'), ('Ciabatta'), ('Donuts'), ('Macaroni'), ('Clam Chowder'),
        ('Gazpacho'), ('Gnocchi'), ('Ratatouille'), ('Poke Bowl'), ('Hotdog'),
        ('Fried Chicken'), ('Churros'), ('Stuffed Peppers'), ('Fish Tacos'), ('Kabobs'),
        ('Mashed Potatoes'), ('Pad Thai'), ('Bibimbap'), ('Kimchi Stew'), ('Tteokbokki'),
        ('Tamales'), ('Meatloaf'), ('Cornbread'), ('Cheesecake'), ('Gelato'),
        ('Sorbet'), ('Ice Cream'), ('Pavlova'), ('Tiramisu'), ('Custard'),
        ('Flan'), ('Bread Pudding'), ('Trifle'), ('Cobbler'), ('Shortcake'),
        ('Soufflé'), ('Eclairs'), ('Cannoli'), ('Baklava'), ('Pecan Pie'),
        ('Apple Pie'), ('Focaccia'), ('Stromboli'), ('Beignets'), ('Yorkshire Pudding')
    ) AS t(Dish)
),
Repeater AS (
    SELECT n FROM (VALUES
      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) r(n)
)
INSERT INTO Test.dbo.Products (Adjective, Cuisine, Dish, QtyInStock)
SELECT a.Adjective, c.Cuisine, d.Dish, 1 
FROM Cuisines   c
CROSS JOIN Adjectives a
CROSS JOIN Dishes     d
CROSS JOIN Repeater   r;
GO

USE [Test]

CREATE NONCLUSTERED INDEX [I_Products_01] ON [dbo].[Products]
(
      [Cuisine] ASC,
      [Adjective] ASC,
      [Dish] ASC,
      [QtyInStock] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

CREATE DATABASE Test;

CREATE TABLE Test.dbo.Products

(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

Cuisine VARCHAR(100) ,

Adjective VARCHAR(100),

Dish VARCHAR(100),

QtyInStock INT) ;

WITH Cuisines AS

(

SELECT Cuisine

FROM (VALUES

('Italian'), ('Mexican'), ('Chinese'), ('Japanese'),

('Indian'), ('French'), ('Greek'), ('Spanish'),

('Korean'), ('Thai'), ('Cajun'), ('Cuban'), ('Moroccan'),

('Turkish'), ('Lebanese'), ('Vietnamese'), ('Filipino'),

('Ethiopian'), ('Caribbean'), ('Brazilian'), ('Peruvian'),

('Argentinian'), ('German'), ('Russian'), ('Polish'),

('Hungarian'), ('Swiss'), ('Swedish'), ('Norwegian'),

('Danish'), ('Portuguese'), ('Irish'), ('Scottish'),

('English'), ('American'), ('Hawaiian'), ('Middle Eastern'),

('Afghan'), ('Pakistani'), ('Bangladeshi'), ('Nepalese'),

('Sri Lankan'), ('Tibetan'), ('Malay'), ('Indonesian'),

('Singaporean'), ('Malaysian'), ('Burmese'), ('Laotian'),

('Cambodian'), ('Mongolian'), ('Uzbek'), ('Kazakh'),

('Georgian'), ('Azerbaijani'), ('Armenian'), ('Persian'),

('Iraqi'), ('Syrian'), ('Jordanian'), ('Saudi Arabian'),

('Israeli'), ('Palestinian'), ('Yemeni'), ('Sudanese'),

('Somali'), ('Kenyan'), ('Tanzanian'), ('Ugandan'),

('Zimbabwean'), ('South African'), ('Nigerian'), ('Ghanaian'),

('Senegalese'), ('Ivory Coast'), ('Cameroonian'),

('Malagasy'), ('Australian'), ('New Zealand'), ('Canadian'),

('Chilean'), ('Colombian'), ('Venezuelan'), ('Ecuadorian'),

('Paraguayan'), ('Uruguayan'), ('Bolivian'), ('Guatemalan'),

('Honduran'), ('Nicaraguan'), ('Salvadoran'), ('Costa Rican'),

('Panamanian'), ('Belizean'), ('Jamaican'), ('Trinidadian'),

('Barbadian'), ('Bahamian'), ('Antiguan'), ('Grenadian'),

('Grandma''s'), ('Grandpa''s')

) AS t(Cuisine)

Adjectives AS

(

SELECT Adjective

FROM (VALUES

('Spicy'), ('Savory'), ('Sweet'), ('Creamy'), ('Crunchy'),

('Zesty'), ('Tangy'), ('Hearty'), ('Fragrant'), ('Juicy'),

('Crispy'), ('Delicious'), ('Mouthwatering'), ('Toasted'),

('Smoky'), ('Rich'), ('Light'), ('Buttery'), ('Tender'),

('Flaky'), ('Succulent'), ('Bitter'), ('Peppery'), ('Charred'),

('Piquant'), ('Nutty'), ('Velvety'), ('Chewy'), ('Silky'),

('Golden'), ('Satisfying'), ('Gooey'), ('Caramelized'), ('Luscious'),

('Hot'), ('Cool'), ('Bold'), ('Earthy'), ('Subtle'),

('Vibrant'), ('Doughy'), ('Garlicky'), ('Herby'), ('Tangy'),

('Mild'), ('Spiced'), ('Infused'), ('Ripe'), ('Fresh'),

('Citrusy'), ('Tart'), ('Pickled'), ('Fermented'), ('Umami'),

('Wholesome'), ('Decadent'), ('Savoured'), ('Fizzy'), ('Effervescent'),

('Melty'), ('Sticky'), ('Toothsome'), ('Crumbly'), ('Roasted'),

('Boiled'), ('Braised'), ('Fried'), ('Baked'), ('Grilled'),

('Steamed'), ('Seared'), ('Broiled'), ('Poached'), ('Simmered'),

('Marinated'), ('Dusted'), ('Drizzled'), ('Glazed'), ('Charred'),

('Seared'), ('Plated'), ('Whipped'), ('Fluffy'), ('Homemade'),

('Comforting'), ('Heartwarming'), ('Filling'), ('Juicy'), ('Piping'),

('Savored'), ('Seasoned'), ('Briny'), ('Doused'), ('Herbed'),

('Basted'), ('Crusted'), ('Topped'), ('Pressed'), ('Folded'),

('Layered'), ('Stuffed')

) AS t(Adjective)

Dishes AS

(

SELECT Dish

FROM (VALUES

('Pizza'), ('Taco'), ('Noodles'), ('Sushi'), ('Curry'),

('Soup'), ('Burger'), ('Salad'), ('Sandwich'), ('Stew'),

('Pasta'), ('Fried Rice'), ('Dumplings'), ('Wrap'),

('Pancakes'), ('Stir Fry'), ('Casserole'), ('Quiche'), ('Ramen'),

('Burrito'), ('Chow Mein'), ('Spring Rolls'), ('Lasagna'), ('Paella'),

('Risotto'), ('Pho'), ('Gyoza'), ('Chili'), ('Bisque'),

('Frittata'), ('Toast'), ('Nachos'), ('Bagel'), ('Croissant'),

('Waffles'), ('Crepes'), ('Omelette'), ('Tart'), ('Brownies'),

('Cupcakes'), ('Muffins'), ('Samosa'), ('Enchiladas'), ('Tikka Masala'),

('Shawarma'), ('Kebab'), ('Falafel'), ('Meatballs'), ('Casserole'),

('Pot Pie'), ('Fajitas'), ('Ravioli'), ('Calzone'), ('Empanadas'),

('Bruschetta'), ('Ciabatta'), ('Donuts'), ('Macaroni'), ('Clam Chowder'),

('Gazpacho'), ('Gnocchi'), ('Ratatouille'), ('Poke Bowl'), ('Hotdog'),

('Fried Chicken'), ('Churros'), ('Stuffed Peppers'), ('Fish Tacos'), ('Kabobs'),

('Mashed Potatoes'), ('Pad Thai'), ('Bibimbap'), ('Kimchi Stew'), ('Tteokbokki'),

('Tamales'), ('Meatloaf'), ('Cornbread'), ('Cheesecake'), ('Gelato'),

('Sorbet'), ('Ice Cream'), ('Pavlova'), ('Tiramisu'), ('Custard'),

('Flan'), ('Bread Pudding'), ('Trifle'), ('Cobbler'), ('Shortcake'),

('Soufflé'), ('Eclairs'), ('Cannoli'), ('Baklava'), ('Pecan Pie'),

('Apple Pie'), ('Focaccia'), ('Stromboli'), ('Beignets'), ('Yorkshire Pudding')

) AS t(Dish)

Repeater AS (

SELECT n FROM (VALUES

(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

) r(n)

)

INSERT INTO Test.dbo.Products (Adjective, Cuisine, Dish, QtyInStock)

SELECT a.Adjective, c.Cuisine, d.Dish, 1

FROM Cuisines c

CROSS JOIN Adjectives a

CROSS JOIN Dishes d

CROSS JOIN Repeater r;

USE [Test]

CREATE NONCLUSTERED INDEX [I_Products_01] ON [dbo].[Products]

(

[Cuisine] ASC,

[Adjective] ASC,

[Dish] ASC,

[QtyInStock] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

Matching parameters:

First we’ll execute a query where the parameters match the datatype in the database:

DECLARE @p1 VARCHAR(100) = 'Italian',
        @p2 VARCHAR(100) = 'Savoured',
        @p3 VARCHAR(100) = 'Meatballs';

SELECT TOP (1) Id
FROM dbo.Products
WHERE 
  Cuisine    = @p1 AND 
  Adjective  = @p2 AND 
  Dish       = @p3 AND 
  QtyInStock = 1

DECLARE @p1 VARCHAR(100) = 'Italian',

@p2 VARCHAR(100) = 'Savoured',

@p3 VARCHAR(100) = 'Meatballs';

SELECT TOP (1) Id

FROM dbo.Products

WHERE

Cuisine = @p1 AND

Adjective = @p2 AND

Dish = @p3 AND

QtyInStock = 1

When we look at the query plan we see an ‘Index Seek’ that returns 1 row.

A total of 4 pages is read to retrieve the information.

With varchar(max) parameters:

DECLARE @p1 VARCHAR(MAX) = 'Italian',
        @p2 VARCHAR(MAX) = 'Savoured',
        @p3 VARCHAR(MAX) = 'Meatballs';

SELECT TOP (1) Id 
FROM dbo.Products 
WHERE 
  Cuisine    = @p1 AND 
  Adjective  = @p2 AND 
  Dish       = @p3 AND 
  QtyInStock = 1

DECLARE @p1 VARCHAR(MAX) = 'Italian',

@p2 VARCHAR(MAX) = 'Savoured',

@p3 VARCHAR(MAX) = 'Meatballs';

SELECT TOP (1) Id

FROM dbo.Products

WHERE

Cuisine = @p1 AND

Adjective = @p2 AND

Dish = @p3 AND

QtyInStock = 1

SQL Server can’t match varchar(max) parameters directly to the indexed columns.
So instead of doing an equality seek on all three keys (Cuisine, Adjective, Dish), it changes the search into a range seek on only the first key (Cuisine). After that, it still has to check the other conditions (Adjective=@p2 AND Dish=@p3) one row at a time as a filter.

Result: SQL Server has to read through 70101 rows in the range before it finds the right one, 384 pages are read instead of 4.

When we filter on Cuisine = ‘Italian’, we can see that row 70101 is the first one that matches our conditions, hence the 70101 rows read.

summary

Parameter type	logical reads	cpu time
varchar(100)	4	0 ms
varchar(max)	384	62 ms

conclusion

Using parameters where the data type doesn’t match the actual data types in the database can cause a performance penalty.
The good news? There’s a quick fix: just match your parameter types to your column types.

The problem:

Example:

Matching parameters:

With varchar(max) parameters:

conclusion

Share via:

Leave a Comment Cancel Reply