SQL Server Performance Tuning is my hobby, passion and a job. During my recent consulting engagement for Comprehensive Database Performance Health Check, I had to face a very interesting situation which I had never faced before. I personally take any consulting gig where SQL Server is running slow, it does not matter if it is Configuration Manager Console, Dynamics, NAV, SharePoint or any other application which runs SQL Server as a database.
One of my customers recently complained that their Configuration Manager Console is running slow. Configuration Manager Console is a snap-in for Microsoft Management Console (MMC). MMC provides you with a console for administrative tools where a variety of network and server administration tasks can occur within a single, integrated interface.
After going through various steps of Comprehensive Database Performance Health Check we were able to only improve performance of their system by only 10%. Usually, when we are down to the second hour of the consulting, we usually see around 50% performance improvement, but in this case, we just noticed only 10% of performance improvement and I was extremely worried about it. At one point, I was deliberating if I want to refund the money for the entire engagement or not.
Solution – Compatibility Level
Suddenly I realized that there is one step which I have yet not tried in this scenario – Compatibility Level.
I noticed that the user was running SQL Server, SQL server 2016 and the compatibility level was set to SQL Server 110. I think it was very clear that it was the major issue for this particular scenario. I immediately change the compatibility level to SQL Server 2016 and suddenly the performance of Configuration Manager Console went to the roof top. The system started with performance at the most optimal speed and queries which were getting timeouts were running in just a split second.
Well, that’s it – if you are using Configuration Manager Console and you are facing performance issue, changing the compatibility level can be the solution of the problems.
Here is how you can change the compatibility level for SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
