July 19, 2018 at 9:08 am
I need an SP that I can pass 1 valid SQL statement for dynamic execution which also returns performance counters (e.g., cpu time, elapsed time, memory usage, reads, logical reads, writes, etc.) similar to what is in DMVs like dm_exec_sessions and dm_exec_requests. I have not been able to find any code samples to do this. Sounds easy enough but the numbers I get back from dm_exec_sessions are unchanged (zero difference) so all my counters are zero with this logic:
select columns from dm_exec_sessions for sessionId=@@SPID into "before" performance variables (e.g., @CPUTimeBefore)
execute the SQL statement using EXEC or sp_executeSQL
select columns from dm_exec_sessions for sessionId=@@SPID into "after" performance variables (e.g., @CPUTimeBefore)
Compute "actual" performance variables as after minus before
I think the problem may be that the entire SP including the select-before/execute/select-after/compute executes as one batch/request so the before/after numbers are the same.
Has anybody tried to do anything like this?
July 19, 2018 at 11:44 am
Looks like it's a problem with dm_exec_sessions but not dm_exec_requests or dm_exec_connections or sysprocesses so I can get the data I want from those instead. I know sysprocesses is to-be-deprecated but I'll accept the risk of that going away after my retirement in 14 years.
July 19, 2018 at 1:05 pm
Did you consider executing the code to be executed as a called stored procedure ? and returning enough properties of that session to be able to identify it later ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 19, 2018 at 1:27 pm
Thanks for your suggestion Steve. I need to modify an existing SP that executes queries in a "test framework" tool. I'm unable to make major design changes to break it out into other SPs. I'm able to get data from the requests/connections DMVs and sysprocesses but not the sessions DMV so I'll go with that for now. Looks like the data agrees with what I in the SSMS message window for "SET STATISTICS TIME ON" and "SET STATISTICS IO ON" so I think I have what I need for now.
July 20, 2018 at 8:02 am
Consider using system views such as sys.dm_db_index_operational_stats. Presumably you know at least the db(s) in which the code operates, although hopefully object names as well (or at least most of them). Capture those stats before and after the execution.
For CPU, I'd just go with SET STATISTICS CPU ON;, but I'm very open to a better way if someone knows one.
Accurate memory usage is trickier to get. You've got the ring buffers sys view, and other assorted %memory% system view, but I'm not sure if/how you'd determine which changes/results in it were just from your specific query. There might be a way, I'm just not sure off the top off my head.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 20, 2018 at 8:29 am
ScottPletcher - Friday, July 20, 2018 8:02 AMConsider using system views such as sys.dm_db_index_operational_stats. Presumably you know at least the db(s) in which the code operates, although hopefully object names as well (or at least most of them). Capture those stats before and after the execution.For CPU, I'd just go with SET STATISTICS CPU ON;, but I'm very open to a better way if someone knows one.
Accurate memory usage is trickier to get. You've got the ring buffers sys view, but I'm not sure how you'd determine which changes/results in it were just from your specific query.
I was thinking about that as well. If it's cached, sys.dm_exec_query_stats give the last reads/writes, last worker time, last grant, etc.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply