Big Gateway Query Compiles in SQL Server

Microsoft has added a new feature to SQL Server 2016, and it is also available in SQL Server 2014 after a free update. When you try to execute multiple large queries at the same time in Microsoft SQL Server 2014, some queries may wait for RESOURCE_SEMAPHORE_QUERY_COMPILE. Only one query that requires more memory than the big gateway threshold (as provided by the output of the DBCC MEMORYSTATUS command) to compile can proceed, even if the server has sufficient memory available.

This SQL Server 2014 hotfix introduces a new trace flag -T 6498. This trace flag lets more than one large query compilation gain access to the big gateway when there is sufficient memory available. It is based on the 80 percentage of SQL Server Target Memory, and it allows for one large query compilation per 25 gigabytes (GB) of memory. For example, this allows for two large query compilations on a 64 GB Target Memory computer.

You do not need the trace flag is SQL Server 2016 (starting with CTP 2.4). You will not require the trace flag because the dynamic policy for Big Gateway is on by default. In addition, Microsoft has also introduced a new DMV (sys.dm_exec_query_optimizer_memory_gateways) that will give both configuration and run time information on all the compile gateways for that instance.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s