Lesson Learned #286: Compilation vs Execution time running a TSQL query

Posted by

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

In several situations we found that our customer reported that their query is taking too much time to execute, but, it is important to  determine what is the phase of the TSQL query execution is taking time. 


It is important to explain that when you execute a query we have different phases: 


  • Compilation:
    • Parser: To Verify the TSQL syntax
    • Algebrizer: To resolve all the names of the objects, columns, etc.. 
    • Optimization: To consider the alternatives to achieve the requested query. 
  • Execution:
    • Execution Engine: Executes the query per the instrucctions set out by compilation. 


Our customer has the following script:


CREATE Table Academy_BlobData 
 Age INT, 
 CustomerData NVARCHAR(MAX) )

DECLARE @Times Integer =0
WHILE(@Times <=100000)
  SET @Times=@Times+1
  INSERT INTO Academy_BlobData (Age,CustomerData) VALUES(RAND()*(100-5)+5,REPLICATE('xyz',200000))


But, when our customer executes the query we saw around 2 minutes to complete the query using SQL SERVER Management Studio from OnPremise to Azure SQL Database.


SELECT * FROM Academy_BlobData


In this situation, all points that the query is trivial and we need to identify why the query is taking too much time, for this reason, we suggested running the following query to investige if the problem is how we compile the query or execute the query. 



SELECT * FROM Academy_BlobData


We found that the parse and compile time took 0 ms and execution took the almost time. 




So, right now, that we know that the phase was execution time, let's try to identify what was the component that took time, running the following query:


SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid ORDER BY max_wait_time_ms DESC


In this case, the wait stats "ASYNC_NETWORK_IO" took the almost time, indicating that the main cause was downloading the data from SQL Server to SQL Server Management and we need to improve our network, reducing the number of rows or query the information needed. 






Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.