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 
(ID INT IDENTITY(1,1) PRIMARY KEY, 
 Age INT, 
 CustomerData NVARCHAR(MAX) )

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

 

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. 

 

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT * FROM Academy_BlobData

 

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

 

Jose_Manuel_Jurado_0-1673780196486.png

 

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. 

 

Jose_Manuel_Jurado_1-1673780302747.png

 

Enjoy!

 

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.