SQL SERVER – Brief Note About RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Type Resource

dmvust the other day, I wrote a blog post about SQL SERVER – Enable Lock Pages in Memory LPIM. I have received some amazing feedback about it and also lots of question. Though, I never expected I signed up four new clients for Comprehensive Database Performance Health Check after reading the blog post as well. I personally, I have can only accept new projects in late October as my calendar is 100% full. Today we will see Brief Note About RESOURCE_SEMAPHORE_QUERY_COMPILE resource Wait Type.

RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Type

I received lots of question on LinkedIn, Twitter that how did I determine that my client needed Lock Pages in Memory. The answer is very simple, I ran the SQL Wait Statistics Query and looked at various top wait types. When I ran the wait types script the very first wait type was RESOURCE_SEMAPHORE_QUERY_COMPILE, which told me that there is absolutely problem with query compiling. Lots of queries are continuously compiling again and again and that means the queries need more memory or the query compilation plan is not staying in the memory.

Here is the script which you can run and identify all the queries which are waiting for the resource semaphore query compilation.

SELECT sp.*, st.text
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
WHERE sp.lastwaittype LIKE 'RESOURCE_SEMAPHORE_QUERY_COMPILE%'
ORDER BY sp.waittime DESC;

The above query will not return any results if you have no memory issue with the query compilation.

When I checked on my client’s system, it was very clear that we needed either more memory to the system or more memory to the instance. As there was way more memory available on the system, it was easy for me to assume that there is some server-level configuration issue.

As mentioned in the previous blog post here, when I asked the DBA suggested that they had just changed the OS and I assumed the issue may be with the Lock Pages in Memory (LPIM).

Before I add more memory, I tried to Enable LPIM and I was able to resolve the issue of resources for my client.

If your server is running slow and wants me to help you speed up, you can avail my Comprehensive Database Performance Health Check at 33% discount. You can book it this month and can avail the service anytime before Dec 31st.

Reference: Pinal Dave (https://blog.sqlauthority.com)