I'm stress testing our software running many transactions and reports at the same time on one same DB. analyzing the most recent expensive queries, I found that the top query is the sp_procedure_param_rowset. Research on sp_procedure_param_rowset shows me that it is when an API calls a Stored procedure and get the metadata of the parameters of each stored procedure. What I'm trying to figure out and I can't find: 1. Is this the same as parameter Sniffing issue? 2. how can I minimize the impact of Sp_procedure_param_rowset?

It's an undocumented system procedure...

Please help thank you John

  • 1
    Does your app have set nocount on ? see this SSC article – Kin Shah Nov 24 '17 at 19:30
  • 1
    Is your app using OLE DB? It seems the app code is requesting parameter meta data on each call. I think that can be eliminated by passing the known strongly-typed parameter collection rather than asking for the definition on each call. – Dan Guzman Nov 24 '17 at 22:51
  • @dan-guzman We are using Delphi's SDAC. What do you mean by passing the know strongly-typed parameter collection? would you have an example? – JohnG Nov 27 '17 at 13:27
  • @Kin We use Delphi's SDAC, I read the article you pointed me to, but not sure I understand it... I'm not a programmer... I will discuss oit with the developpers to see if they understand it... – JohnG Nov 27 '17 at 13:35
  • 1
    @JohnG, avoiding the request for parameter meta-data will avoid the extra round trip. I haven't used Delphi for 20 years so I can't speak to specifics, but the app code simply needs to pass parameters with data types matching that expected by the proc. – Dan Guzman Nov 28 '17 at 2:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.