[NVARCHAR](MAX)

Product:

Microsoft Azure SQL

Issue:

Store procedures / process take a long time to finish – than a few weeks ago to a lot less of time.
The usage of resources in the SQL server is flat in the monitor view.

Solution:

Check that you do not have any columns that use [NVARCHAR](MAX) in a stage table or other table. This can when data grow in your tables, make the SQL CPU /Memory resources – be to small for it to handle.

Check also if you can make a CREATE NONCLUSTERED INDEX on you largest table that are in use in the query, to help improve performance.

As a very short term solution, you can increase cpu and memory on your Azure SQL instance, but this is not a good solution.

When you combine an nvarchar(max) with an nvarchar(100), SQL Server often implicitly promotes the result to nvarchar(max). Once an expression becomes a LOB (Large Object) type, SQL Server may switch to much more expensive processing. Nvarchar(max) is treated differently than normal string types. nvarchar(100) is stored directly in-row.
nvarchar(max) may be stored off-row. “Stored off-row” means SQL Server may not keep the actual string data inside the data page that contains the rest of the row. A SQL Server data page is only 8 KB. Then SQL Server must maintain LOB pointers and perform additional I/O.

This increases CPU and memory usage. When SQL Server sees nvarchar(max), it must assume values could be huge (up to 2 GB).

Check whether SQL Server is using off-row storage

SELECT
OBJECT_NAME(object_id),
lob_used_page_count,
used_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.Test');

 

If lob_used_page_count is non-zero, SQL Server is storing LOB (nvarchar(max), varchar(max), xml, etc.) data on separate pages.

 

To see what SQL are doing, enter:

sp_who2

More Information:

https://www.brentozar.com/archive/2016/10/max-data-types/

https://www.brentozar.com/archive/2025/10/which-should-you-use-varchar-or-nvarchar/

https://www.brentozar.com/free-sql-server-training-videos/ 

https://vladdba.com/2024/05/26/the-curious-case-of-the-truncating-nvarcharmax-variable/

https://www.conradakunga.com/blog/how-to-index-a-nvarcharmax-table-in-sql-server/ 

https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver17 

https://erikdarling.com/using-sp_tableoption-to-store-lob-data-off-row/ 

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-memory-issues