MSSQLWIKI

Karthick P.K on SQL Server

«
»

What is RESOURCE_SEMAPHORE_QUERY_COMPILE?

Posted by Karthick P.K on October 12, 2012

What is RESOURCE_SEMAPHORE_QUERY_COMPILE?

Before we understand the RESOURCE_SEMAPHORE_QUERY_COMPILE let us see what is compile memory.

Compile memory:  When a query is compiled in SQL Server, the compilation process needs memory (for parsing, algeberaization and optimization) called compile memory. This memory doesn’t include the memory required to execute the query.

Challenges with Compile memory:  This memory used for Query compilations are usually expected to fit into SQL Server main-memory and to be relatively short-lived.  Like any other consumers of Bpool,  this is implemented by “stealing” pages from the Buffer Pool and hence it blocks other memory consumers from using that memory until a query compilation completes.

Even if the SQL Server has enough memory to service multiple simultaneous query compilation, allowing all of them to occur at the same time might lead to stealing a significant number of pages from the buffer pool, with consequent increased physical IO , poor performance of query execution and causing memory pressure within SQL Server. However on the other side, a throttling mechanism that is too restrictive could lead to a non-optimal usage of the system resources and decreased throughput for compile intensive workloads, So SQL Server came with more dynamic approach to solve the problem  which  is to better manage system resources, memory in particular. Such management should hence be based on and driven by the amount of memory used.

Let us see it with example:

Assume SQL  Server Max server memory is set to 1000MB and Currently data /index pages is consuming 800MB with in Max server memory (bpool) and 3 queries are reaching SQL Server for compilation, each of them requiring 300 MB for compilation.

If all three queries are compiled simultaneously  total compilation might take 900MB of memory causing all the data and index pages to be dropped from BPOOL causing  consequent increased physical IO and poor performance of query during execution(to bring data pages back to memory). On the other hand let us assume each of this 3 queries need only 2 MB of compilation memory, There is no reason for SQL Server to throttle the number of compilation.

To overcome above challenges SQL 2005+ throttles the number of concurrent compiles that can happen at  any time based on memory usage during the compile. SQL Server memory grant is controlled by a object called “Resource Semaphore” and has internal mechanism to detect how much memory has been used by each compile. There are three gateways (semaphores) called the small, medium, and big gateway. When a request is received for compilation SQL Server will start compilation. There is no limit on how many queries can be compiled simultaneously, but when memory usage for a query reaches the threshold for a given gateway it will then acquire that semaphore of next gateway before continuing. The semaphores (Queries which can be compiled concurrently) are set up to allow 4*schedulers count for the small gateway, 1*schedulers count for the medium gateway and 1 (per SQL instance) for the big gateway.

The small gateway has a fixed threshold for how much memory must be consumed before you enter it.  The medium and big gateways have dynamic thresholds that vary depending on how much memory is available, how much is stolen, etc.

If the semaphore can’t be acquired then you see this wait type (Query is waiting for memory grant to compile a query =  RESOURCE_SEMAPHORE_QUERY_COMPILE wait). This behavior lets SQL Server to allow only a few memory-intensive compilations occur at the same time. Additionally, this behavior maximizes throughput for smaller queries.

clip_image001[4]

How to identify RESOURCE_SEMAPHORE_QUERY_COMPILE waits?

 

To get an idea look at the sys.sysprocesses  table for sessions waiting on  RESOURCE_SEMAPHORE_QUERY_COMPILE

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;

 

clip_image002[4]

 

 

 

There could be two possible reasons for RESOURCE_SEMAPHORE_QUERY_COMPILE waits

1.       Memory pressure within SQL Server caused by others using  lot of stolen memory or OS memory pressure

In this case you will see thresholds for medium and big gateways very low. In this situation you have to identify who is consuming most of stolen memory and  tune them to increase the available memory or add additional memory that can used by SQL server. When the available memory decrease, threshold for medium and big gateways would decrease significantly and increase the number of queries which have to enter medium /big gateways, So the number of parallel compiles will decrease increasing the overall wait time.

 

This DBCC memory status output is from system which has 48 processor and has excessive RESOURCE_SEMAPHORE_QUERY_COMPILE waits because of memory pressure.

Small Gateway (default)                  Value

—————————————- ———–

Configured Units                         192        // 190 number of units .  48  CPU’s * 4=192

Available Units                            109

Acquires                                         83

Waiters                                            0

Threshold Factor                         380000

Threshold                                       380000

(6 row(s) affected)

Medium Gateway (default)                 Value

—————————————- ———–

Configured Units                         48              //48 number of units. SO 48 CPU’s *1=48

Available Units                               0             //There is no available slots. All slots are busy.

Acquires                                           48

Waiters                                             34           //34 Queries are waiting

Threshold Factor                         12

Threshold                                    2204603    //Threshold is very low 2 MB (This value is in bytes)

(6 row(s) affected)

Big Gateway (default)                    Value

—————————————- ———–

Configured Units                         1            //1 per instance

Available Units                             0            //There is no available slots. All slots are busy.

Acquires                                          1

Waiters                                          47            // 47 Queries are waiting

Threshold Factor                         8

Threshold                                3306905     //Threshold is very low 3 MB (This value is in bytes)

 

 

2.       There is huge amount of available memory but the available units in gateway is exhausted. This situation normally occurs when we have many queries that have high compile time and use lot of memory for compilation.

Compile time and Compile memory can be captures using the show plan XML for query compile event in profiler.

Below is extract from show plan XML for query compile event in profiler. This query has used approximately 150 MB of compile memory and the threshold for Big gateway is around 143 MB . So only one query which needs more than 143 MB can compile at a time , This can cause contention when there are multiple queries waiting for compile/recompile. Also an important factor to notice in this XML plan is compile time is ~139 times the CPU. So likely this query waited for most of the time for resource semaphore.

Big Gateway (default)                    Value

—————————————- ———–

Configured Units                         1

Available Units                             0

Acquires                                         1

Waiters                                          47

Threshold Factor                         8

Threshold                                149640500

 

         <QueryPlan CachedPlanSize="312" CompileTime="139847" CompileCPU="1002" CompileMemory="152320">

 

How to fix RESOURCE_SEMAPHORE_QUERY_COMPILE waits?

1.       Add additional memory to SQL Server.

2.       Reduce the number of compile and recompiles happening in SQL Server.

3.       Threshold for the gateways are dynamic (except for small gateway) and therefore memory pressure occurs from other sources (Internal to SQL Server or system wide) reduces the  amount of memory available for compiles and Queries are throttled to    higher gateways sooner. Make sure SQL Server is not starving for memory.

4.       Identify the queries which are consuming large compile memory and tune them (CompileMemory  in show plan XML query compile can be used).

5.       RESOURCE_SEMAPHORE_QUERY_COMPILE waits are very common in 32-bit SQL Server because of virtual address space limitation, so migrate to 64-Bit SQL Server.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

This entry was posted on October 12, 2012 at 1:17 PM and is filed under Memory, Performance, SQL Query, SQL Server Engine. Tagged: , , , , , . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

14 Responses to “What is RESOURCE_SEMAPHORE_QUERY_COMPILE?”

  1. Query optimization « MSSQLWIKI said

    November 15, 2012 at 10:30 AM

    […] What is RESOURCE_SEMAPHORE_QUERY_COMPILE? […]

    Reply

  2. Thierry said

    July 25, 2013 at 2:25 PM

    Interesting article
    I cannot really deduce what is affecting my instance. We have a lot of these waits now and then. This is a sample from the DBCC output at the time we see some of these waits
    Any ideas on this?
    Thx, Thierry

    Small Gateway (internal) Value
    —————————————- ———–
    Configured Units 12
    Available Units 0
    Acquires 12
    Waiters 3
    Threshold Factor 380000
    Threshold 380000

    (6 row(s) affected)

    Medium Gateway (internal) Value
    —————————————- ———–
    Configured Units 3
    Available Units 3
    Acquires 0
    Waiters 0
    Threshold Factor 12
    Threshold 161427057

    (6 row(s) affected)

    Big Gateway (internal) Value
    —————————————- ———–
    Configured Units 1
    Available Units 1
    Acquires 0
    Waiters 0
    Threshold Factor 8
    Threshold -1

    Reply

  3. Thierry said

    July 26, 2013 at 1:47 PM

    Hi Karthick,
    this is on 2008R2. Apparently the headings changed. It was plain ‘gateway’ on 2005, ‘gateway (default)’ on 2008 and ‘gateway (internal)’ on 2008R2
    Chrz
    Thierry
    ps: I can send you the error log but it’s quite large and doesn’t really contain any warnings or errors. We don’t have any complaints from customers either so everyone is happy except me because I would like to get rid of those resource_semaphore… waits or at least know why they occur

    Reply

    • Karthick P.K said

      July 26, 2013 at 10:18 PM

      Send me the output of dbcc memory status collected while the sessions are waiting on resource semaphore query compile Sent from my Windows Phone ________________________________

      Reply

  4. Thierry said

    July 30, 2013 at 1:07 PM

    Memory Manager KB
    —————————————- ———–
    VM Reserved 63210968
    VM Committed 32762852
    Locked Pages Allocated 0
    Reserved Memory 1024
    Reserved Memory In Use 0

    (5 row(s) affected)

    Memory node Id = 0 KB
    —————————————- ———–
    VM Reserved 63201432
    VM Committed 32753568
    Locked Pages Allocated 0
    MultiPage Allocator 45960
    SinglePage Allocator 1759672

    (5 row(s) affected)

    Memory node Id = 1 KB
    —————————————- ———–
    VM Reserved 5824
    VM Committed 5696
    Locked Pages Allocated 0
    MultiPage Allocator 4136
    SinglePage Allocator 1759376

    (5 row(s) affected)

    Memory node Id = 64 KB
    —————————————- ———–
    VM Reserved 2624
    VM Committed 2552
    Locked Pages Allocated 0
    MultiPage Allocator 2472
    SinglePage Allocator 1759192

    (5 row(s) affected)

    MEMORYCLERK_SQLGENERAL (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 5392
    MultiPage Allocator 11240

    (7 row(s) affected)

    MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
    —————————————- ———–
    VM Reserved 62996480
    VM Committed 32555008
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 424

    (7 row(s) affected)

    MEMORYCLERK_SQLQUERYEXEC (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 240
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLQUERYEXEC (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLQUERYEXEC (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 256
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLOPTIMIZER (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 17536
    MultiPage Allocator 1792

    (7 row(s) affected)

    MEMORYCLERK_SQLOPTIMIZER (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 9888
    MultiPage Allocator 704

    (7 row(s) affected)

    MEMORYCLERK_SQLOPTIMIZER (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 27424
    MultiPage Allocator 2496

    (7 row(s) affected)

    MEMORYCLERK_SQLUTILITIES (node 0) KB
    —————————————- ———–
    VM Reserved 120
    VM Committed 120
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 120
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLSTORENG (node 0) KB
    —————————————- ———–
    VM Reserved 17472
    VM Committed 17472
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 9008
    MultiPage Allocator 4424

    (7 row(s) affected)

    MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 2384
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 2232
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 4616
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLCLR (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 144
    MultiPage Allocator 544

    (7 row(s) affected)

    MEMORYCLERK_SQLHTTP (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SNI (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 320
    MultiPage Allocator 16

    (7 row(s) affected)

    MEMORYCLERK_SNI (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 304
    MultiPage Allocator 16

    (7 row(s) affected)

    MEMORYCLERK_SNI (node 64) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 16

    (7 row(s) affected)

    MEMORYCLERK_SNI (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 624
    MultiPage Allocator 48

    (7 row(s) affected)

    MEMORYCLERK_FULLTEXT (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 24
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLXP (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_BHF (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 2048
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_XE_BUFFER (node 0) KB
    —————————————- ———–
    VM Reserved 2816
    VM Committed 2816
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_XE_BUFFER (node 1) KB
    —————————————- ———–
    VM Reserved 1408
    VM Committed 1408
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_XE_BUFFER (Total) KB
    —————————————- ———–
    VM Reserved 4224
    VM Committed 4224
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_HOST (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 40
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SOSNODE (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 29672
    MultiPage Allocator 14832

    (7 row(s) affected)

    MEMORYCLERK_SOSNODE (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 26560
    MultiPage Allocator 3208

    (7 row(s) affected)

    MEMORYCLERK_SOSNODE (node 64) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 2392

    (7 row(s) affected)

    MEMORYCLERK_SOSNODE (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 56240
    MultiPage Allocator 20432

    (7 row(s) affected)

    MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 288
    SM Committed 288
    SinglePage Allocator 0
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
    ———————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 48
    MultiPage Allocator 0

    (7 row(s) affected)

    MEMORYCLERK_XE (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 3080
    MultiPage Allocator 2496

    (7 row(s) affected)

    CACHESTORE_OBJCP (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 171696
    MultiPage Allocator 2232

    (7 row(s) affected)

    CACHESTORE_SQLCP (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 1232008
    MultiPage Allocator 2504

    (7 row(s) affected)

    CACHESTORE_PHDR (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 62064
    MultiPage Allocator 16

    (7 row(s) affected)

    CACHESTORE_XPROC (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 112
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_TEMPTABLES (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 24
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_NOTIF (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_VIEWDEFINITIONS (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_XMLDBTYPE (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_XMLDBELEMENT (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_XMLDBATTRIBUTE (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_STACKFRAMES (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 8

    (7 row(s) affected)

    CACHESTORE_STACKFRAMES (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 8

    (7 row(s) affected)

    CACHESTORE_STACKFRAMES (node 64) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 8

    (7 row(s) affected)

    CACHESTORE_STACKFRAMES (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 24

    (7 row(s) affected)

    CACHESTORE_BROKERTBLACS (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 40
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_BROKERKEK (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_BROKERDSH (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_BROKERRSB (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_BROKERREADONLY (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 32
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_BROKERTO (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_EVENTS (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_SYSTEMROWSET (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 3872
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_CONVPRI (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 32
    MultiPage Allocator 0

    (7 row(s) affected)

    CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 32
    MultiPage Allocator 0

    (7 row(s) affected)

    USERSTORE_SCHEMAMGR (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 11152
    MultiPage Allocator 464

    (7 row(s) affected)

    USERSTORE_DBMETADATA (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 12424
    MultiPage Allocator 0

    (7 row(s) affected)

    USERSTORE_TOKENPERM (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 12208
    MultiPage Allocator 4664

    (7 row(s) affected)

    USERSTORE_TOKENPERM (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 1448
    MultiPage Allocator 0

    (7 row(s) affected)

    USERSTORE_TOKENPERM (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 13656
    MultiPage Allocator 4664

    (7 row(s) affected)

    USERSTORE_OBJPERM (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 1752
    MultiPage Allocator 0

    (7 row(s) affected)

    USERSTORE_SXC (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 1008
    MultiPage Allocator 0

    (7 row(s) affected)

    USERSTORE_SXC (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 1048
    MultiPage Allocator 0

    (7 row(s) affected)

    USERSTORE_SXC (node 64) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 row(s) affected)

    USERSTORE_SXC (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 2064
    MultiPage Allocator 0

    (7 row(s) affected)

    OBJECTSTORE_LBSS (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 16
    MultiPage Allocator 64

    (7 row(s) affected)

    OBJECTSTORE_LBSS (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 32
    MultiPage Allocator 128

    (7 row(s) affected)

    OBJECTSTORE_LBSS (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 48
    MultiPage Allocator 192

    (7 row(s) affected)

    OBJECTSTORE_SNI_PACKET (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 4784
    MultiPage Allocator 56

    (7 row(s) affected)

    OBJECTSTORE_SNI_PACKET (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 5720
    MultiPage Allocator 56

    (7 row(s) affected)

    OBJECTSTORE_SNI_PACKET (node 64) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 0
    MultiPage Allocator 56

    (7 row(s) affected)

    OBJECTSTORE_SNI_PACKET (Total) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 10504
    MultiPage Allocator 168

    (7 row(s) affected)

    OBJECTSTORE_SERVICE_BROKER (node 0) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 424
    MultiPage Allocator 0

    (7 row(s) affected)

    OBJECTSTORE_LOCK_MANAGER (node 0) KB
    —————————————- ———–
    VM Reserved 131072
    VM Committed 131072
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 54432
    MultiPage Allocator 0

    (7 row(s) affected)

    OBJECTSTORE_LOCK_MANAGER (node 1) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 75152
    MultiPage Allocator 0

    (7 row(s) affected)

    OBJECTSTORE_LOCK_MANAGER (node 64) KB
    —————————————- ———–
    VM Reserved 0
    VM Committed 0
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 24
    MultiPage Allocator 0

    (7 row(s) affected)

    OBJECTSTORE_LOCK_MANAGER (Total) KB
    —————————————- ———–
    VM Reserved 131072
    VM Committed 131072
    Locked Pages Allocated 0
    SM Reserved 0
    SM Committed 0
    SinglePage Allocator 129608
    MultiPage Allocator 0

    (7 row(s) affected)

    Buffer Pool Value
    —————————————- ———–
    Committed 3932160
    Target 3932160
    Database 3710872
    Dirty 3630
    In IO 0
    Latched 3
    Free 966
    Stolen 220322
    Reserved 113839
    Visible 3932160
    Stolen Potential 3401391
    Limiting Factor 17
    Last OOM Factor 0
    Last OS Error 0
    Page Life Expectancy 85999

    (15 row(s) affected)

    Process/System Counts Value
    —————————————- ——————–
    Available Physical Memory 1486143488
    Available Virtual Memory 8730682634240
    Available Paging File 32010682368
    Working Set 33667334144
    Percent of Committed Memory in WS 100
    Page Faults 528706180
    System physical memory high 1
    System physical memory low 0
    Process physical memory low 0
    Process virtual memory low 0

    (10 row(s) affected)

    Procedure Cache Value
    —————————————- ———–
    TotalProcs 160968
    TotalPages 183878
    InUsePages 3158

    (3 row(s) affected)

    Global Memory Objects Pages
    —————————————- ———–
    Resource 1046
    Locks 16201
    XDES 339
    SETLS 12
    SE Dataset Allocators 24
    SubpDesc Allocators 12
    SE SchemaManager 525
    SE Column Metadata Cache 710
    SQLCache 22029
    Replication 2
    ServerGlobal 51
    XP Global 2
    SortTables 2

    (13 row(s) affected)

    Query Memory Objects (internal) Value
    —————————————- ———–
    Grants 1
    Waiting 0
    Available 2759209
    Current Max 2872960
    Future Max 2872960
    Physical Max 2872960
    Next Request 0
    Waiting For 0
    Cost 0
    Timeout 0
    Wait Time 0

    (11 row(s) affected)

    Small Query Memory Objects (internal) Value
    —————————————- ———–
    Grants 2
    Waiting 0
    Available 81664
    Current Max 81920
    Future Max 81920

    (5 row(s) affected)

    Optimization Queue (internal) Value
    —————————————- ——————–
    Overall Memory 25820135424
    Target Memory 23039500288
    Last Notification 1
    Timeout 6
    Early Termination Factor 5

    (5 row(s) affected)

    Small Gateway (internal) Value
    —————————————- ———–
    Configured Units 12
    Available Units 0
    Acquires 12
    Waiters 89
    Threshold Factor 380000
    Threshold 380000

    (6 row(s) affected)

    Medium Gateway (internal) Value
    —————————————- ———–
    Configured Units 3
    Available Units 3
    Acquires 0
    Waiters 0
    Threshold Factor 12
    Threshold 159996529

    (6 row(s) affected)

    Big Gateway (internal) Value
    —————————————- ———–
    Configured Units 1
    Available Units 1
    Acquires 0
    Waiters 0
    Threshold Factor 8
    Threshold -1

    (6 row(s) affected)

    Memory Pool Manager Pages
    —————————————- ———–
    Reserved Current 114007
    Reserved Limit 3515391

    (2 row(s) affected)

    Memory Pool (internal) Pages
    —————————————- ———–
    Allocations 334040
    Predicted 938554
    Private Target 0
    Private Limit 0
    Total Target 3735552
    Total Limit 3735552
    OOM Count 0

    (7 row(s) affected)

    MEMORYBROKER_FOR_CACHE (internal) Pages
    —————————————- ———–
    Allocations 204787
    Rate 86
    Target Allocations 3001871
    Future Allocations 0
    Overall 3151872
    Last Notification 1

    (6 row(s) affected)

    MEMORYBROKER_FOR_STEAL (internal) Pages
    —————————————- ———–
    Allocations 15374
    Rate 67
    Target Allocations 2812439
    Future Allocations 0
    Overall 3151872
    Last Notification 1

    (6 row(s) affected)

    MEMORYBROKER_FOR_RESERVE (internal) Pages
    —————————————- ———–
    Allocations 113879
    Rate 105002
    Target Allocations 3151872
    Future Allocations 718240
    Overall 3151872
    Last Notification 1

    (6 row(s) affected)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Reply

  5. Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said

    January 11, 2014 at 12:57 PM

    […] What is RESOURCE_SEMAPHORE_QUERY_COMPILE? […]

    Reply

  6. Whoo's Party (@whoosparty) said

    June 4, 2014 at 8:47 AM

    How do i know the compiletime is bigger than the CPU?
    Basing from your “Also an important factor to notice in this XML plan is compile time is ~139 times the CPU”

    Reply

  7. Polaris QT Blower Replacement Parts Cover Plate Set PV140020 said

    July 2, 2014 at 5:36 AM

    Hurrah, that’s what I was searching for, what a stuff!
    existing here at this webpage, thanks admin of this website.

    Reply

  8. pat at pool table 911 said

    July 25, 2014 at 7:48 AM

    Write more, thats all I have to say. Literally,
    it seems as though you relied on the video to make your point.
    You obviously know what youre talking about, why throw
    away your intelligence on just posting videos to your blog whyen you could be giving us something informative to read?

    Reply

  9. estate planning legal team said

    July 25, 2014 at 7:54 AM

    Howdy! This is kind of off topic but I need some guidance from an established blog.

    Is it very difficult to set up your own blog? I’m not very
    techincal but I can figure things oout pretty fast.

    I’m thinking about setting up my own but I’m
    not sure where to begin. Do you have any tips
    or suggestions? Cheers

    Reply

  10. buy oriental rug online said

    September 21, 2014 at 2:05 AM

    Why users ѕtill maҡе use of to read news papers աhen in tɦis technological globe all iѕ ρresented on web?

    Reply

  11. Memory troubleshooting « Simon's SQL said

    December 1, 2016 at 6:52 AM

    […] What is RESOURCE_SEMAPHORE_QUERY_COMPILE? […]

    Reply

Leave a comment

«
»