on March 21, 2018
I just spent 90 minutes of my life figuring out a detail about sys.dm_exec_query_stats which I’m pretty sure I figured out five years ago, but didn’t write a blog post about.
Time to write a blog post, so I can save time when I go searching for this in a couple years.
Functions can be tricksy in SQL Server
It can be difficult to see functions in execution plans, and to figure out when they’re eating a lot of CPU time on your SQL Server.
One way that’s handy to see if functions are being executed frequently is to check out the sys.dm_exec_query_stats Dynamic Management View and look at the execution count, CPU time, and logical reads of the ‘CREATE FUNCTION’ statement for a given function, to estimate its impact.
But recently, I was working on a project and…
I couldn’t find my functions in sys.dm_exec_query_stats! I also couldn’t find them in Query Store.
Or, rather, I couldn’t find SOME of my functions
Some of my functions in the demo code were showing up just fine. I was really puzzled by that. I thought …
Maybe this is a bug with ‘CREATE OR ALTER’? A sign of some weird memory pressure? Something introduced in SQL Server 2017? A buggy side effect of implicit conversions in some of the functions? A problem with the queries I was using? A weird setting on the database? (Also: about 100 other things that didn’t turn out to be the case.)
I finally wrote up some simple demo code, tested it against a SQL Server 2008 R2 instance (omitting the Query Store components), compared it with SQL Server 2017, and found it to be consistent.
Some functions just don’t accrue information in sys.dm_exec_query_stats, and that’s been true for quite a while.
I tested six types of functions:
- Scalar functions that do, and do not do data access
- Multi-statement table valued functions that do, and do not do data access
- Single statement table valued functions that do, and do not do data access
Which of these functions appear in sys.dm_exec_query_stats and sys.query_store_query?
To be clear, the queries run which call the functions all appear in sys.dm_exec_query_stats and sys.query_store_query. There are no missing calling queries.
However, the calling query only shows things like how many times it was executed. It doesn’t show how many times the function was executed (the function may be executed many times per query). Sometimes that’s handy to see when you’re troubleshooting.
CREATE FUNCTION entries with per-function-execution data only appeared for my:
- Scalar function that does data access
- Multi-statement table valued function that does data access
- Multi-statement table valued function that does NOT do data access
It makes sense to me that my single statement table valued functions are missing from the list, because those functions can be “inlined” into their outer queries. They don’t really run on their own.
However, it is a bit confusing that the scalar function that does not do data access is missing from the list. (I don’t think it’s a huge tragedy, but it makes it look like you’re missing something – hence me spending a while on this!)
Which of these functions appear in sys.dm_exec_function_stats?
We got sys.dm_exec_function_stats in SQL Server 2016, so you can’t use this on Ye Older SQL Instances. This view recorded information for my:
- Scalar function that does data access
- Scalar function that does NOT do data access
Why no table valued functions? This one is documented! The DMV simply doesn’t have information about Table Valued Functions.
What does this all mean?
If you have a lot of scalar functions that don’t do data access, and you’re on SQL Server 2016 and higher, check out sys.dm_exec_function_stats for aggregate information on how they are running.
And if you’re just confused about why not all your functions are showing up in sys.dm_exec_query_stats or Query Store, hopefully this post saves you some time!
REGISTER