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://erikdarling.com/using-sp_tableoption-to-store-lob-data-off-row/