Recently I had a situation where I was looking at a SQL Instance due to a contractor who controls the system wanting to drop some indexes and they couldn’t because they couldn’t get a lock request. Sadly, they were using the GUI to try to accomplish this. Always makes me sad when we aren’t using scripts. So, I took a deep drive into what was actually running on this system. I had them send over scripts for what they were trying to do. Meanwhile, I looked at the system and discovered some things. First, we had really had RESOURCE_SEMAPHORE wait types for two days which indicates it waiting on memory. So, I took a look at the memory clerks. The top memory clerk was for lock manager at 32 GBs and the system only has 64 GBs, which is abnormal. Several inserts into the inserts into one table had the RESOURCE_SEMAPHORE waits so ran DBCC FRESSYSEMCAHCE (‘ALL’). Now I might have been able to run a lesser FREESYSTEMCACHE command, but this worked.
Now while I was looking at the system, I was use sp_whoisactive @get_plans = 1 and noticed that every plan had implicit conversions and was using the JDBC driver. I knew they were using the Hibernate ORM and was curious if there was an easy way to get rid of implicit conversions within the ORM. So, I used Google, or it may have been Bing who really knows. But I stumbled across several StackOverflow post about adding “sendStringParametersAsUnicode=false” to the connection string. So, our CPU ulitzed for SQL Server was averaging 60%. I scheduled a meeting with the developer for the system. I took this as an opportunity to show him how he could see performance improvements with his index changes using Query Store, use sp_BlitzIndex to see if they were being used, and show him where I store sp_BlitzIndex so he can see historical data for index usage stats. And I wanted to ask him about setting in the connection strings to see if that would indeed get rid of our implicit conversions as I had never used this before. So, first here is our baseline out of DPA before we made our first change and Query Store.
When I drilled down all the queries had implicit conversions and Index Scans not exactly what you want to see, and they were only returning one record. So, the developer went and changed the connection string somewhere and these were the first set of results in performance.
So, looking below we went from 60% CPU to 20%. So, I drilled down on the bar in Query Store and was meet with this. All of the CPU was being used by one query and when I looked at it had an implicit conversion, so I went back to the developer to see if he knew where this was coming from, and he did.
So, he went and made that change, and the CPU went from 20% to 1%. And Query store can barely show you it is using CPU in comparison to what it was doing a month ago.
Overall Query Store shows all the things moving faster. Note indexes were changed by the developer on 12/28 so that reduced the logical reads. But our connection string change reduced them as well both times and the duration things are running on the 1/6 and 1/15. We had our locking issues over the Christmas period effect the duration.
Moral of the story if you are using Hibernate with JDBC driver look at whether you can add “sendStringParametersAsUnicode=false” to you connection string if you have implicit conversions.
The post Impact of Eliminating Implicit Conversions in JDBC Driver first appeared on Tracy Boggiano's Blog.