I am using Wildfly 8.2.0 Final AS in the production environment and we recently faced performance issues and traced it to frequently use of sp_prepexecute and sp_unprep calls on MS SQL from Application's ORM i.e. hibernate. The application is using Microsoft JDBC 6.0 which does not have any cache for Prepared Statement to improve the performance experience with regard to the issue observed.
I am looking to introduce the prepared-statement-cache-size in the Application server connection pool and initiate test show marked improvements. I would like to arrive at and ideal cache size I need to keep but did not find much information on how typically user do arrive at the figures.
Additionally, I am unclear whether I should be setting share-prepared-statements to True (default is False). Are there any pointers that can help me decide if this flag needs to be set True? Note that I am using Microsoft JDBC driver 6.0.