Just another day, I was involved with a client during a Comprehensive Database Performance Health Check and ended up at a very interesting situation. Our client was struggling with the memory issue and the server was running super slow. Eventually, we fixed the problem by disabling the resource governor which was accidentally enabled by one of their database administrators. Let us learn how to disable resource governor in this blog post.
The resource governor was accidentally enabled by DBA as the command of enabling the resource governor does not say there is a word Enable.
Enable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
DBA, when executed the above script, though he is just configuring Governor and not enabling it. After running the above script, he got busy with the other task and did not come back. However, this is what actually created a very poor situation for their organizations as enabled Governor due to some reason lead to a memory issue.
When I was not able to find any other issue with their performance, I thought of checking resource governor and I figured out the issue may be related to it. To test the theory, we disabled it and immediately the performance was restored. Here is the script to disable Resource Governor.
Disable Resource Governor
ALTER RESOURCE GOVERNOR DISABLE;
Once we disabled resource governor the problem went away for us.
Now here is one thing I want to stress upon. I believe Resource Governor is an amazing tool to manage resources in SQL Server. I have been using it with many of my clients over the years. Particularly, I believe it works great with In-Memory Technology and memory-optimized tables. However, if you incorrectly configure it, it can create a real stressful situation as well. I strongly recommend getting your resource governor configuration checked by Performance Tuning Expert before you enable it.
Reference: Pinal Dave (https://blog.sqlauthority.com)
