UPDATED! Performance tuning a stored procedure with user defined function calls in SQL Server

I had a previous post on the topic of performance tuning a stored procedure with multiple user defined function calls. I thought the method I reviewed was a pretty nifty way of getting very useful performance information. 

However, it turns out there's a much more straightforward method of getting a comprehensive  (including functions) performance picture when executing a stored procedure. 

This method (only to be used on a development server, not production) uses DBCC FREEPROCCACHE to completely clear out sys.dm_exec_procedure_stats and sys.dm_exec_function_stats. It also clears out other DMVs, so be aware of that.

Here's a sample of how it can be used. Basically, for each version of the code, you free the procedure cache, run the code, and then query sys.dm_exec_procedure_stats and sys.dm_exec_function_stats. Then, compare the two outputs. You'll get a comprehensive picture of the resource usage for each version, including functions.

  -- Run this for both versions of the stored procedure  
 DBCC FREEPROCCACHE  
 exec API_GetTiers_Old  @LocaleID= 1033, @CountryCode = 'US',@UserID = 60190667  
 -- exec API_GetTiers_New  @LocaleID= 1033, @CountryCode = 'US',@UserID = 60190667

   
 -- Get stats from functions  
 Select   
      Object_Name = Object_Name(Object_ID)  
      ,execution_count  
      ,Total_Worker_Time  
      ,Total_Logical_Reads   
	  ,Total_Elapsed_Time      
 From sys.dm_exec_function_stats  
 Union all  
 -- Get stats from procedures  
 Select   
      Object_Name = Object_Name(Object_ID)  
      ,execution_count  
      ,Total_Worker_Time  
      ,Total_Logical_Reads   
	  ,Total_Elapsed_Time      
 From sys.dm_exec_procedure_stats  
 -- Sum everything for convenience  
 Union all  
 Select 'TOTAL', null  
 , Total_Worker_Time =   
      IsNull((Select sum(Total_Worker_Time) From sys.dm_exec_function_stats ) , 0)
      + IsNull((Select sum(Total_Worker_Time) From sys.dm_exec_procedure_stats ) , 0) 
 , Total_Logical_Reads   
      = IsNull((Select sum(total_logical_reads) From sys.dm_exec_function_stats ) , 0)  
      + IsNull((Select sum(total_logical_reads) From sys.dm_exec_procedure_stats ) , 0) 
 , Total_Elapsed_Time   
      = IsNull((Select sum(Total_Elapsed_Time) From sys.dm_exec_function_stats ) , 0)  
      + IsNull((Select sum(Total_Elapsed_Time) From sys.dm_exec_procedure_stats ) , 0)