Recently I was reviewing performance on a troublesome stored procedure. I used the method detailed in my recent blog post: UPDATED! Performance tuning a stored procedure with user defined function calls in SQL Server.
But in looking at the output, one of the functions just never showed up, as though it were not taking any resources. Why was that? After some research, I found that only scalar functions show up in the system table sys.dm_exec_function_stats, but the function that didn't show up was a table valued function.
So, back to the drawing board. It turns out that another way to get all the performance-related stats for stored procedures and all types of functions, is by using the system table sys.dm_exec_query_stats.
The steps are very similar, in that you're clearing the cache with FREEPROCCACHE, running the stored procedure, and then checking the query statistics. When I checked performance this way, the table valued function showed up properly.
This is what I ran:
DBCC FREEPROCCACHE
Exec API_REPORT_Transcript
Select top 20
ObjectName = Object_Name(QueryText.objectid, QueryText.dbid)
,Executions = QueryStats.execution_count
,CPUTime = QueryStats.total_worker_time
,LogicalReads = QueryStats.total_logical_reads
,LogicalWrites = QueryStats.total_logical_writes
,Duration = QueryStats.total_elapsed_time
,QueryText =
convert(
varchar(200), SUBSTRING(QueryText.text,QueryStats.statement_start_offset/2 +1,
(CASE WHEN QueryStats.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), QueryText.text)) * 2
ELSE QueryStats.statement_end_offset END -
QueryStats.statement_start_offset
)/2
)
)
From sys.dm_exec_query_stats AS QueryStats
CROSS APPLY sys.dm_exec_sql_text(QueryStats.sql_handle) AS QueryText
Where
DB_NAME(QueryText.dbid) = 'MyDatabaseName'
Order by CPUTime desc
