Is it possible to return server data with every query?
As an example, I might run a stored procedure which uses a lot of resources. If I were to log onto the server at the same time as I'm running the stored procedure, I could see the memory usage/CPU/etc. Is there any circumstance in which MSSQL returns that kind of data on a SPID basis?
It is also possible for me to build out (as part of the software) that as my query is running, it also runs sp_whoisactive or sp_who2, etc to collect that information every few seconds. Though it seems like an inefficient way to figure out what my query performance was.
If you want to monitor what you are running yourself you could turn on Live execution Plan or set statistics IO on/off or Set statistics Time on/off.
SET STATISTICS TIME (Transact-SQL) - SQL Server | Microsoft Docs
For your second question Query store would be an option but you can also use extended events. I only use that last one with a specific stored procedure to find the parameters when a stored procedure is much slower then I expect.
1 Like
Thanks, this is what I needed
Just make sure you don't use them when Scalar or mTVF functions are used. It's ok to use when iTVF's are in play. They seriously lie when either of those types of functions are used.
2 Likes