This article is contributed. See the original author and article here.

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!


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.