Hello,
I'm looking to optimize Store procedures which run hundreds of times in a day and see if I can optimize them in anyway by checking there execution plan and code. I have sample param for couple of SP..but how can I get param used for running any store procedure. There should be some trace of them in the background, cache, somewhere. if you are aware how to get the param of SP which were provided for execution by user..please let me know, it will be just amazing!
I dont want to take the approach by looking into tables and using sample values on my own as param can change the exen time so I want to get the details which user is passing. Please share any script or tips on figuring out the param for SP proc.
The parameters used to cache the plan are stored, but each execution is not.
The cached plan parameters are easily extracted from the plan cache.
If you want every execution parameters you will need to create an extended event session to capture those.
I would first look at Brent Ozar and the First Responder Kit specifically sp_blitz cache and tackle the plans that way first, checking take the usual pain points. High average reads / writes / cpu / time etc.
Much easier than reinventing the wheel.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply