Hi Massimo,
Are you creating #tables with an index in those sp/trigger with ddl in tran turned on by any chance? Please take a look at this KBA article:
https://launchpad.support.sap.com/#/notes/2224058
Regards
Mehrab
The way I read the SAP/Sybase documentation, if there are multiple users executing a stored proc (or trigger) in parallel, then multiple copies of the stored proc get compiled into procedure cache memory. Here's a quote from the Performance and Tuning Series: Basics -> Memory Use and Performance -> Procedure Cache document page:
"If no plan is in memory, or if all copies are in use, the query tree for the procedure is read from the sysprocedures table. The query tree is then optimized, using the parameters provided to the procedure, and placed at the MRU end of the chain, and execution begins. Plans at the LRU end of the page chain that are not in use are aged out of the cache."
So my question is, if you have a request to execute a stored proc , and that requires compiling (right word?) the proc into memory, it's going to use the "parameters provided to the procedure" to decide what to do. I think it would be possible for certain parameters to cause the stored proc to be compiled differently, causing performance differences when the cached/compiled memory copy gets reused.
Also, when you say there's a large variation in execution times for different plans for the same stored proc, could there be variation based on the number of rows searched and/or returned in different executions? (ie, for different calling parameters). Can you tell if any of the bad stored proc plans contain erroneous table scans?
I can't seem to find a way to display the query plan for a cached compiled copy of a stored procedure. The monCachedProcedures table has a PlanID field, but there doesn't seem to be any call for displaying the query plan for ObjectID & PlanID (there is for statement cache stuff, but not stored procs)
Ben near Washington DC
Add comment