I keep seeing both dynamic management views and dynamic management functions referenced in SQL Server performance work, and I am not clear on the difference or when each is used.
What actually distinguishes a DMV from a DMF?
I keep seeing both dynamic management views and dynamic management functions referenced in SQL Server performance work, and I am not clear on the difference or when each is used.
What actually distinguishes a DMV from a DMF?
They are two forms of the same dynamic management feature, and the difference is simply whether it needs input, which decides how you call it, so the distinction then why it matters in practice:
The core difference, parameters: a dynamic management view, a DMV, is queried like a table with no input, SELECT from sys.dm_exec_sessions returning its rows directly, while a dynamic management function, a DMF, requires one or more parameters and is called with them, SELECT from sys.dm_exec_sql_text(handle) needing a handle passed in. The naming even hints at it, both start sys.dm_ but the function takes arguments in parentheses while the view does not. That is genuinely the whole structural difference, table like versus function taking input.
Why the parameter difference exists: a DMV exposes a set of information queryable wholesale, all sessions, all connections, all index stats, while a DMF exposes information that requires context to return, the SQL text for a specific handle, the physical stats for a specific index, since returning that for everything at once would be impractical or meaningless, so it takes the parameter identifying what you want. The function form exists precisely because some information only makes sense scoped to a specific input.
How they work together, the practical pattern: the two combine constantly, a DMV providing rows that include a handle or id, then a DMF called with that handle to expand the detail, the classic being sys.dm_exec_requests, a DMV, CROSS APPLY sys.dm_exec_sql_text, a DMF, on its sql_handle, the view giving the requests and the function giving each one's query text. CROSS APPLY is the operator that feeds each row's handle from the DMV into the DMF, the pairing behind most useful performance queries, covered in the DMV performance thread linked alongside.
The practical takeaway: you rarely choose between them, you use them together, querying DMVs for the overview and applying DMFs to expand specific rows and knowing which is which just tells you whether a given sys.dm_ object needs a parameter, a DMF erroring without its argument being the only time the distinction trips you up. Both are the same dynamic management family exposing server internals, the view and function forms being a calling convention difference rather than a conceptual divide.
The it just means whether it takes a parameter clarity was exactly what I needed, I had thought they were conceptually different things when it is a calling convention. The CROSS APPLY pairing pattern from the performance thread now makes complete sense, the DMV feeds handles into the DMF. Simple once explained.