Assuming I have an problematic stored procedure in production which is taking 45 mins to execute, I want to trace or track which stmt is taking up more resources i/o,cpu,memory,or getting blocked and so long running(duration). Assume that within my stored proc I have 10 sql stmts and wanted to track down those 2-3 statements causing the slowness.(may be blocking . may be reading a whole of data , long running, high reads,high cpu or something else ..)
I also, want to track down compile time and run time parameter values to find out if there is any issue with parameters being passed or wrong plan is being picked up.
Now suppose, I want to setup some kind of trace to gather information , in such case, what columns do I need to choose while setting a trace for troubleshooting a specific stored procedure by putting a filter on dbname and stored proc name.
Second part is, after collecting the trace , how to do the analysis (reporting query) whether we need to check by total duration or reads or cpu time?
Can anyone provide your inputs on how to do multi-dimensional anlysis and why to do that slicing dicing ? Please provide some ideas.
If anyone has already implemented such trace, it would be a great help if it can be shared. I am using SQL 2016 EE.