URL Shrink Upgrade Sizing

Updating the solution → URLs Full URL Shrink Upgrade → URL Shrink Upgrade Sizing

This page is for administrators planning the URL-column upgrade from a build before 1634. It gives the measured timings, a query to size your own urls table, and estimates by table size and by Azure SQL tier. The steps are in the runbook.

2. Measured baseline performance

The upgrade was benchmarked against a freshly seeded database in a controlled environment. These figures were measured on the superseded varchar(1700) form of the migration. The operation for nvarchar(850) is the same shape, with two differences: (1) the lossy/representability pre-flight scan no longer runs, so the pre-flight is faster; and (2) nvarchar stores 2 bytes/char, so for typical (mostly-ASCII) URLs the rewritten column and index are larger — expect somewhat more transaction log, tempdb and final on-disk size than the numbers below, and budget headroom accordingly. Treat the timings as a representative order-of-magnitude guide, not a precise prediction.

Property	Value
urls row count	10,000,000
Distribution	All clean ASCII URLs, average 152 characters each (realistic SharePoint paths). None over 850 chars.
Pre-migration urls size on disk	3.27 GB (LOB-dominated)
Post-migration urls size on disk	8.19 GB (clustered PK now holds the URLs inline; new NC index = 1.68 GB)
SQL Server	SQL Server 2025 (RTM-GDR, 17.0.1115.1), Standard Developer Edition (EngineEdition = 2), default localhost instance
Host	Windows VM, Standard HDD disks (~500 IOPS, ~60 MB/s, ~10 ms latency — the slowest Azure storage tier)
ONLINE index?	No — EngineEdition = 2 doesn't qualify, so IX_urls_full_url was built offline. This is the pessimistic / non-Enterprise customer case.
Total ShrinkUrlsFullUrlColumn elapsed	6 min 6 sec (366 sec)
Per-row cost	~36.6 µs/row (4.3 µs scans + 13.3 µs ALTER + 19.0 µs CREATE INDEX)
Post-state	Column is nvarchar(850) NOT NULL, IX_urls_full_url present, row count unchanged

Per-step breakdown

Step	Duration	% of total
Pre-flight "too long" scan (LEN(full_url) > 850)	2.5 sec	<1%
ALTER COLUMN full_url nvarchar(850) NOT NULL	2 min 12.7 sec	~41%
CREATE INDEX IX_urls_full_url (OFFLINE)	3 min 9.9 sec	~58%
Total	~5 min 25 sec	100%

The original varchar(1700) benchmark also ran a "lossy varchar" pre-flight scan (40.9 sec) that no longer exists — nvarchar needs no representability check — so removing it brought the measured total down from 6 min 6 sec to ~5 min 25 sec. The ALTER COLUMN / CREATE INDEX durations shown are from the varchar run; on nvarchar(850) they will be somewhat longer because the rewritten data and index hold 2 bytes/char.

Why this is a "best-case at 10M, worst-case at 100M+" baseline. The test VM has Standard HDD storage, which is the slowest disk type Azure offers — about 500 IOPS and 60 MB/s with high (~10 ms) latency. At 10M rows the pre-migration urls table is only ~3 GB and fits comfortably in the SQL Server buffer pool, so the pre-flight scans were served almost entirely from memory and the slow HDD barely mattered. As the table grows past what fits in memory (somewhere between 25M and 50M rows on a typical SQL instance), the Standard HDD becomes the bottleneck and the per-row cost gets dramatically worse. Note also that on Standard Developer Edition the index is built offline — Enterprise / Azure SQL DB / Azure SQL MI build it ONLINE and finish in a comparable wall-clock time but without blocking writers. All Azure SQL DB tiers (even the cheapest) use SSD, so most Azure SQL DB customers will see times in line with — or better than — the Standard tier table in §4 below.

---

2a. How many rows do you have?

Before you look at the time estimates, run this against your database to see which row in the tables you should be looking at. It also surfaces how many URLs would cause the migration to abort, and whether the column/index are already in place (so the upgrade would be a no-op).

-- Instant estimate (reads metadata only, no table scan - safe on any DB size)
SELECT
    OBJECT_NAME(p.object_id) AS table_name,
    SUM(p.rows)              AS estimated_row_count
FROM sys.partitions p
WHERE p.object_id = OBJECT_ID('dbo.urls')
  AND p.index_id IN (0, 1)   -- heap or clustered index only
GROUP BY p.object_id;

-- Already-applied check - if both come back 1, the upgrade is a no-op
SELECT
    (SELECT CASE WHEN t.name = 'nvarchar' AND c.max_length = 1700 THEN 1 ELSE 0 END
       FROM sys.columns c
       INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
       WHERE c.object_id = OBJECT_ID('dbo.urls') AND c.name = 'full_url') AS column_already_nvarchar850,
    (SELECT COUNT(*) FROM sys.indexes
       WHERE object_id = OBJECT_ID('dbo.urls') AND name = 'IX_urls_full_url') AS index_already_present;
-- (nvarchar(850) reports max_length = 1700 bytes, i.e. 850 chars x 2 bytes.)

-- Will the migration abort? (full scan - may take a while on huge tables;
-- skip if you just want time estimates and trust that your URLs are well-formed)
-- Only one condition can abort the upgrade: a URL longer than 850 characters.
-- (There is no "representable as varchar" check any more - nvarchar stores all Unicode,
--  so Greek and other non-Latin URLs are preserved, not rejected.)
SELECT COUNT_BIG(*) AS urls_longer_than_850_chars
FROM dbo.urls WHERE LEN(full_url) > 850;

What to do with the numbers:

• urls estimated_row_count → pick the corresponding row in §3 (if you self-host SQL on a VM) or §4 (if you're on Azure SQL DB).
• column_already_nvarchar850 = 1 AND index_already_present = 1 → the migration will detect this and exit in seconds. No action needed.
• urls_longer_than_850_chars > 0 → fix the offending rows before kicking off the upgrade; otherwise the migration will abort (safely, without changing anything) and you'll have to fix them and re-run. See §6 of the runbook.

---

3. Expected upgrade time vs table size

The work the upgrade does is essentially linear in the number of rows in urls — as long as the table fits in memory. Once it doesn't, every page has to be read from disk and the per-row cost balloons on slow storage. The pre-migration urls table is roughly (avg URL length + 30) × rowcount bytes for the data and a constant ~50% overhead for the LOB structures.

On the test VM (Azure VM, Standard HDD — the slowest option)

urls rows	Estimated upgrade time	Notes
1,000,000 (1M)	~40 sec	Fits in memory
5,000,000 (5M)	~3 min	Fits in memory
10,000,000 (10M)	~6 min (measured)	Fits in memory; HDD largely bypassed
25,000,000 (25M)	~15 – 25 min	Starting to spill from memory
50,000,000 (50M)	~45 min – 1.5 hr	HDD becomes painful
100,000,000 (100M)	~3 – 6 hr	Mostly disk-bound; 500-IOPS / 60 MB/s HDD is the bottleneck
250,000,000 (250M)	~10 – 18 hr	Sequential scan throughput-limited by HDD
500,000,000 (500M)	~1.5 – 2 days	HDD throughput is the wall; not realistic without better storage

If you're running SQL Server on an Azure VM and you have 25M+ urls, the single highest-impact change you can make is to switch the data disk from Standard HDD to Premium SSD (P30 or above) before applying the migration. This alone would shave 5–10× off the times above. Even Standard SSD would be a major improvement over Standard HDD for tables that no longer fit in memory.

If you're on Standard / Web / Express / Developer Edition the new index is built offline (the CREATE INDEX line above). On Enterprise / Azure SQL DB / Azure SQL MI that same step runs ONLINE = ON — wall-clock is similar, but other reads and writes against urls aren't blocked.

---

4. Expected upgrade time on Azure SQL DB (DTU model, Standard tier)

Good news for Azure SQL DB customers: every Azure SQL DB tier — even the cheapest — uses SSD storage under the hood. So the "table doesn't fit in memory" cliff that hits hard on a Standard HDD test VM is much gentler in Azure SQL DB. The dominant cost on Azure SQL DB is the DTU cap on CPU and log throughput (because both ALTER COLUMN and CREATE INDEX are fully logged), not raw disk IO.

These are estimates with a wide error margin (±50%). Real-world numbers depend on how busy your DB is, what's already cached, log throttling, and other concurrent workload. Treat them as orders of magnitude.

Tier	DTUs	Max log throughput	Max data IOPS	Notes
S3	100	~2.5 MB/s	~320	ALTER COLUMN log writes will be the bottleneck
S4	200	~5 MB/s	~640
S6	400	~10 MB/s	~1,280	A reasonable sweet spot for a one-off upgrade
S7	800	~20 MB/s	~2,560	Recommended if you have 50M+ rows

Estimated upgrade time by row count and tier

For context: the 10M-row baseline writes roughly 6 GB of transaction log (3.3 GB ALTER COLUMN row rewrite + 1.7 GB index leaf + overhead). This scales linearly with urls rowcount.

Rows	S3 (100 DTU)	S4 (200 DTU)	S6 (400 DTU)	S7 (800 DTU)
1M	~5 – 8 min	~3 – 5 min	~1 – 2 min	<1 min
10M	~45 – 70 min	~25 – 40 min	~12 – 20 min	~6 – 10 min
50M	~4 – 6 h	~2 – 3 h	~1 – 1.5 h	~30 – 50 min
100M	~8 – 12 h	~4 – 6 h	~2 – 3 h	~1 – 1.5 h
250M	~20 – 30 h	~10 – 15 h	~5 – 7 h	~2.5 – 3.5 h
500M	~1.5 – 2.5 days	~20 – 30 h	~10 – 14 h	~5 – 7 h

How to read this table: if you're seeing hours-on-end (let alone days), that's a strong signal to scale up before the upgrade — see §5.4 of the runbook. The cost of running on a higher tier for a few hours is trivial compared to a multi-day outage window.

Premium tier is dramatically faster. If your urls table is in the 50M+ range, scaling temporarily to Premium (P2 / P4 / P6) is the best move. Premium has order-of-magnitude better IOPS (P2 = 4,000 IOPS, P6 = 16,000 IOPS) and log throughput. As a rough guide, P2 will finish the upgrade in roughly half the time of S7, and P6 in roughly a quarter — and you can scale back down immediately afterwards.

---

Return to URLs Full URL Shrink Upgrade · All topics in this guide

Previous: URLs Full URL Shrink Upgrade · Next: URL Shrink Upgrade Runbook