I’ve been messing around with long compilations recently, and a question was lingering in the back of my mind, based off of SET STATISTICS TIME ON…
Is there a difference between parse time and compile time? This led me to science time, aka beating up SQL Server and taking its secrets.
First, let’s build a proc that contains a very simple query but a gigabyte of whitespace. Errr, whoops…
Fine, just half a gig of whitespace:
DECLARE @w VARCHAR(MAX) = ' ' --8 spaces SET @w += @w --2^4 SET @w += @w --2^5 SET @w += @w --2^6 SET @w += @w --2^7 SET @w += @w --2^8 SET @w += @w --2^9 SET @w += @w --2^10 = 1024, 1KB SET @w += @w --2^11 SET @w += @w --2^12 SET @w += @w --2^13 SET @w += @w --2^14 SET @w += @w --2^15 SET @w += @w --2^16 SET @w += @w --2^17 SET @w += @w --2^18 SET @w += @w --2^19 SET @w += @w --2^20 = 1MB SET @w += @w --2^21 SET @w += @w --2^22 SET @w += @w --2^23 SET @w += @w --2^24 SET @w += @w --2^25 SET @w += @w --2^26 SET @w += @w --2^27 SET @w += @w --2^28 SET @w += @w --2^29 = 512MB --SET @w += @w --2^30 = 1GB DECLARE @sql VARCHAR(MAX) = ' CREATE OR ALTER PROC Parsimonious AS SELECT TOP(1)'+@w+' * FROM dbo.Comments OPTION(RECOMPILE) ' EXEC(@sql)
This is enough to prove parse time <> compile time, as they show up with different numbers. STATISTICS TIME captures the full duration.
While the execution plan shows compile time as less. (Also, the Extended Events that show compile time matched the execution plan time.)
sys.query_store_query contains columns related to this, with more than just the execution plan compilation number, but those don’t show the full time either.
In summary, parsing is different from compilation, and consumes CPU that doesn’t show up anywhere except STATISTICS TIME. This would make a server spending all its compute on parsing harder to detect, though I imagine it’s rare. I can’t say it never happens though, because I’ve seen things in production. Seen things.