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

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.

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.

Enjoy!

Continue reading...