Lesson Learned #498:Understanding the Role of STATMAN in SQL Server and Its Resource Consumption

[Guest Jose_Manuel_Jurado]

Today, I worked on a service request that our customer reported a performance issue and they reported that this query: SELECT StatMan([sC0], [LC0], [sB0000]) FROM (SELECT TOP 100 PERCENT [sC0], [LC0], step_direction([sC0]) over (order by NULL) AS [sB0000] FROM .. TABLESAMPLE SYSTEM (@samplePercent PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [sC0], [sB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 16, RECOMPILE). I would like to share my lessons learned here.

 

 

 

It is important to mention that STATMAN is not a function that you can directly call in our queries. Instead, it refers to an internal process within SQL Server that is responsible for updating statistics on data distribution.

 

 

 

When we execute UPDATE STATISTICS TableExample WITH FULLSCAN or the statistics are automatically updated by SQL Server engine we could see this type of query:

 

 

 

DECLARE @samplePercent FLOAT;
SELECT StatMan([sC0], [LC0], [sB0000])
FROM (
SELECT TOP 100 PERCENT [sC0], [LC0], step_direction([sC0]) OVER (ORDER BY NULL) AS [sB0000]
FROM [YourTable]
TABLESAMPLE SYSTEM (@samplePercent PERCENT)
WITH (READUNCOMMITTED)
) AS _MS_UPDSTATS_TBL_HELPER
ORDER BY [sC0], [sB0000]
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 16, RECOMPILE);

 

 

 

 

 

• 
  Data Scanning: The TABLESAMPLE SYSTEM (@samplePercent PERCENT[iCODE])[/iCODE] clause indicates that a sample of the data is being scanned. Depending on the value of @samplePercent, this could involve a significant portion of the table's data.
   
   
  
• 
  Sorting and Window Functions: The use of step_direction and the ORDER BY clause introduces sorting and window functions that can be resource-intensive, especially on large datasets.
   
   
  
• 
  Parallel Execution: The OPTION (MAXDOP 16) part of the query allows the operation to use up to 16 processors in parallel. While parallelism can speed up the operation, it also increases CPU usage and can lead to contention for resources.
   
   
  
• 
  Recompilation: The RECOMPILE option forces the query to be recompiled each time.
   
  

 

Continue reading...