Memory Grants: The mysterious SQL Server memory consumer with Many Names

by Contributed | Jan 27, 2021 | Technology

This article is contributed. See the original author and article here.

First published on MSDN on Jan 01, 2013
The Memory Consumer with Many Names

Have you ever wondered what Memory grants are? What about QE Reservations ? And Query Execution Memory ? Workspace memory ? How about Memory Reservations ?

As with most things in life, complex concepts often reduce to a simple one: all these names refer to the same memory consumer in SQL Server: memory allocated during query execution for Sort and Hash operations (bulk copy and index creation fit into the same category but a lot less common).

Allow me to provide some larger context: during its lifetime a query may request memory from different “buckets” or clerks, depending on what it needs to do. For example, when a query is parsed and compiled initially, it will consume compile or optimizer memory. Once the query is compiled that memory is released and the resulting query plan needs to be stored in cache. For that, the plan will consume procedure cache memory and will stay in that cache until server is restarted or memory pressure occurs. At that point, the query is ready for execution. If the query happens to be doing any sort operations or hash match (join or aggregates), then it will first reserve and later use part or all of the reserved memory to store sorted results or hash buckets. These memory operations during the execution of a query are what all these many names refer to.

Terminology and Troubleshooting Tools

Let’s review the different terms that you may encounter referring to this memory consumer. Again, all these describe concepts that relate to the same memory allocations:



Query Execution Memory (QE Memory):  This term is used to highlight the fact that sort/hash memory is used during the execution of a query and is the largest memory consumption that may come from a query during execution.

Update(9/17): QE Memory is the very type of memory that Resource Governor actually limits, when used. See Resource Pools Max and Min Memory percent

Query Execution (QE) Reservations or Memory Reservations: When a query needs memory for sort/hash operations, during execution it will make a reservation request based on the original query plan which contained a sort or a hash operator. Then as the query executes, it requests the memory and SQL Server will grant that request partially or fully depending on memory availability. There is a memory clerk (accountant) named ‘MEMORYCLERK_SQLQERESERVATIONS’ that keep track of these memory allocations (check out DBCC MEMORYSTATUS or sys.dm_os_memory_clerks).

Memory Grants: When SQL Server grants the requested memory to an executing query it is said that a memory grant has occurred. There is a Perfmon counter that keeps track of how many queries have been granted the requested memory: Memory Grants Outstanding . Another counter shows how many queries have requested sort/hash memory and have to wait for it because the Query Execution memory has run out (QE Reservation memory clerk has given all of it away): Memory Grants Pending . These two only display the count of memory grants and do not account for size. That is, one query alone could have consumed say 4 GB of memory to perform a sort, but that will not be reflected in either of these.

To view individual requests, and the memory size they have requested and have been granted, you can query the sys.dm_exec_query_memory_grants DMV. This shows information about currently executing queries, not historically.

In addition, you can capture the Actual Query Execution plan and find an XML element called <Query plan> which will contain an attribute showing the size of the memory grant (KB) as in the following example:

<QueryPlan DegreeOfParallelism=”8″ MemoryGrant =”2009216″

Another DMV- sys.dm_exec_requests – contains a column granted_query_memory which reports the size in 8 KB pages. For example a value of 1000 would mean 1000 * 8 KB , or 8000 KB of memory granted.

Workspace Memory: This is yet another term that describes the same memory. Often you will see this in the Perfmon counter Granted Workspace Memory (KB) which reflects the overall amount of memory currently used for sort/hash operations in KB. The Maximum Workspace Memory (KB) accounts for the maximum amount of workspace memory ever used since the start of the SQL Server.  In my opinion, the term Workspace Memory is a legacy one used to describe this memory allocator in SQL Server 7.0 and 2000 and was later superseded by the memory clerks terminology after SQL Server 2005.

Resource Semaphore: To add more complications to this concept, SQL Server uses a thread synchronization object called a semaphore to keep track of how much memory has been granted. The idea is this: if SQL Server runs out of workspace memory/QE memory, then instead of failing the query execution with an out-of-memory error, it will cause the query to wait for memory. In this context, the Memory Grants Pending Perfmon counter makes sense. And so do wait_time_ms , granted_memory_kb = NULL, timeout_sec in sys.dm_exec_query_memory_grants . BTW, this and compile memory are the only places in SQL Server where a query will actually be made to wait for memory if it is not available; in all other cases, the query will fail outright with a 701 error – out of memory.

There is a Wait type in SQL Server that shows that a query is waiting for a memory grant – RESOURCE_SEMAPHORE. As the documentation states, this “occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.” You will observe this wait type in sys.dm_exec_requests for individual sessions. Here is a KB article written primarily for SQL Server 2000 which describes how to troubleshoot this issue and also what happens when a query finally “gets tired” of waiting for a memory grant.



Why do you Care About Memory Grants or Workspace Memory or Query Execution Memory, or whatever you call it?

Over the years of troubleshooting performance problems, I have seen this to be one of the most common memory-related issues. Applications often execute seemingly simple queries that end up wreaking tons of performance havoc on the SQL Server side because of huge sort or hash operations.  These not only end up consuming a lot of SQL Server memory during execution, but also cause other queries to have to wait for memory to become available – thus the performance bottleneck.

Using the tools I have outlined above (DMVs, Perfmon counters and actual query plan), you can investigate which queries are large-grant consumers and can have those tuned/re-written where possible.



What Can a Developer Actually Do about Sort/Hash Operations?

Speaking of re-writing queries, here are some things to look for in a query that may lead to large memory grants.



Reasons why a query would use a SORT operator (not all inclusive list):

ORDER BY (T-SQL)

GROUP BY (T-SQL)

DISTINCT (T-SQL)

Merge Join operator selected by the optimizer and one of the inputs of the Merge join has to be sorted because a clustered index is not available on that column.

Reasons why a query would use a Hash Match operator (not all inclusive list):

JOIN (T-SQL) – if SQL ends up performing a Hash Join. Typically, lack of good indexes may lead to the most expensive of join operators – Hash Join. Look at query plan.

DISTINCT (T-SQL) – a Hash Aggregate could be used to perform the distinct. Look at query plan.

SUM/AVG/MAX/MIN (T-SQL)– any aggregate operation could potentially be performed as a Hash Aggregate . Look at query plan.

UNION – a Hash Aggregate could be used to remove the duplicates.

Knowing these common reasons can help an application developer eliminate, as much as possible, the large memory grant requests coming to SQL Server.

As always, basic query tuning starts with checking if your queries have appropriate indexes to help them reduce reads, minimize or eliminate large sorts where possible.

Update : Since SQL Server 2012 SP3, there exist a query hint that allows you to control the size of your memory grant. You can read about it in New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server 2012 . Here is an example



SELECT * FROM Table1 ORDER BY Column1 OPTION (min_grant_percent = 3, max_grant_percent = 5 )



Memory Grant Internals:

Here is a great  blog post on Memory Grant Internals

Summary of Ways to Deal with Large Grants:

1. Re-write queries


2. Use Resource Governor


3. Find appropriate indexes for the query which may reduce the large number of rows processed and thus change the JOIN algorithms (see Database Engine Tuning Advisor and Missing Indexes DMVs)


4. Use OPTION (min_grant_percent = XX, max_grant_percent = XX ) hint


5. SQL Server 2017 and 2019 introduce Adaptive query processing allowing for Memory Grant feedback mechanism to adjust memory grant size dynamically at run-time.

Namaste!

Joseph

Best practices for leveraging Microsoft 365 Defender API's – Episode One

by Contributed | Jan 27, 2021 | Technology

This article is contributed. See the original author and article here.

We are strong supporters of automation, and fully acknowledge the value of automating repetitive actions and being able to adjust technology to the specific security practices and processes used by our customers and partners. This is what motivates us in developing and enriching our API layer. But as we all know, with great scale comes great responsibility, and here efficiency is the name of the game.  

This blog series will provide you best practices and recommendations on how to best use the different Microsoft 365 Defender features and APIs, in the most efficient way to power your automation to achieve the outcome you are desire. 

In this first blog we will focus on two aspects: 

• Don’t automatically default to the Advanced hunting API 


• If you do need to use the Advanced hunting API for your scenario, how to use it in the most optimal way.

When to use the Advanced hunting API and when to use other APIs / features?  

The Advanced hunting API is a very robust capability that enables retrieving raw data from all Microsoft 365 Defender products (covering endpoints, identities, applications docs and email), and can also be leveraged to generate statistics on entities, translating identifiers, e.g. to which machine IP X.X.X.X belongs to. While this is a great feature with broad reach across your data, it can also be challenging to maintain, because;

• More team members need to know the internals of KQL to leverage it, and; 


• Consuming the hunting resource pool where there is no real need for that  

Below are a few examples of how we have developed a dedicated API to provide you with the intended answer in a single API call: 

1.   You have a 3^rd party alert on an IP address. You would like to see which device this IP was assigned to at that time and to get more information on this device. Easy ! You can do it by :

a.   First using Find devices by internal IP API  – Find devices seen with the requested internal IP in the time range of 15 minutes prior and after a given timestamp.  

b.   Once you have device ID you can use Get machine by ID API to get more details on the device including its OS Platform, MDE groups, tags, and exposure level.

2.   You have a malicious domain IOC and you would like to see its prevalence in your organization.

Easy! You can use Get domain statistics API for that, it retrieves the organization statistics on the given domain for the lookback time you configured, by default the last 30 days, based on Microsoft Defender for Endpoint (MDE) including: 

a.   Prevalence

b.   First seen

c.   Last seen

For example: https://wdatpapi-eus-stg.cloudapp.net/api/domains/microsoft.com/stats?lookBackHours=24

3.   You have a list of IOCs, and you would like to make sure you are alerted if there is any activity associated with this URL in your organization.

To implement this scenario you can use Indicators:

a.   Add IOCs to MDE indicators via Indicators API and set the required action (“Alert” or “Alert and Block”).

b.   To check if any of the IOCs was observed in the organization in the last 30 days, you can run a single Advanced hunting query:

// See if any process created a file matching a hash on the list 
let covidIndicators = (externaldata(TimeGenerated:datetime, FileHashValue:string, FileHashType: string )
[@”https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/Microsoft.Covid19.Indicators.csv”]
with (format=”csv”))
| where FileHashType == ‘sha256’; //and TimeGenerated > ago(1d);
covidIndicators
| join (DeviceFileEvents
| where ActionType == ‘FileCreated’
| take 100) on $left.FileHashValue  == $right.SHA256

How to optimize your Advanced hunting queries

Once you determine that the only way to resolve your scenario is using Advanced hunting queries, you should write efficient optimized queries so your queries will execute faster and will consume less resources. Queries may be throttled or limited based on how they’re written, to limit impact to other sessions. You can read all our best practices recommendations, and also watch this webcast to learn more. In this section we will highlight a few recommendations to improve query performance.

1. Always use time filters as your first query condition. Most of the time you will use Advanced hunting to get more information on an entity following an incident, so make sure to insert the time of the incident, and narrow your lookback time. The shorter the lookback time is, the faster the query will be executed.

  There are multiple ways to insert time filters to your query.

  Scenario example – get all logon activities of the Finance departments users in Office 365.

// Filter timestamp in the query using “ago”
IdentityInfo
| where Department == “Finance”
| distinct AccountObjectId
| join (IdentityLogonEvents | where Timestamp > ago(10d)) on AccountObjectId
| where Application == “Office 365”

// Filter timestamp in the query using “between”
let selectedTimestamp = datetime(2020-11-12T19:35:03.9859771Z);
IdentityInfo
| where Department == “Finance”
| distinct AccountObjectId
| join (IdentityLogonEvents | where Timestamp between ((selectedTimestamp – 2h) .. (selectedTimestamp + 2h))) on AccountObjectId
| where Application == “Office 365”

  In general, always filter your query by adding Where conditions, so it will be accurate and will query for the exact data you are looking for.

2.   Only use “join” when it is necessary for your scenario.

a.   If you are using a join, try to reduce the dataset before joining to limit the join size. Filter the table on the left side, to reduce its size as much as you can.

b.   Use an accurate key for the join.

c.   Choose the join flavor(kind) according to your scenario.

  In the following example we want to see all details of emails and their attachments.

  The following example is an inefficient query, because:

a.   EmailEvents table is the largest table, it should never be on the left side of the join, without substantial filtering on it.

b.   Join kind=leftouter returns all emails, including ones without attachments, which make the result set very large. We don’t need to see emails without attachments therefore this kind of join is not the right kind for this scenario.

c.   The Key of the join is not accurate , NetworkMessageId. This is an email identifier, but the same email can be set to multiple recipients.

EmailEvents
| project NetworkMessageId, Subject, Timestamp, SenderFromAddress , SenderIPv4 , RecipientEmailAddress , AttachmentCount
| join kind=leftouter(EmailAttachmentInfo
| project NetworkMessageId,FileName, FileType, MalwareFilterVerdict, SHA256, RecipientEmailAddress )
on NetworkMessageId

  This query should be changed and improved to the following query by:

a.   Putting the smaller table, EmailAttachmentInfo, on the left.

b.   Increasing join accuracy using join kind=inner

c.   Using an accurate key for the join (NetworkMessageId, RecipientEmailAddress)

d.   Filtering the EmailEvents table to only include emails with attachments before the join.

// Smaller table on the left side, with kind = inner, as default join (innerunique)
// will remove left side duplications, so if a single email has more than one attachments we will miss it
EmailAttachmentInfo
| project NetworkMessageId, FileName, FileType, MalwareFilterVerdict, SHA256, RecipientEmailAddress
| join kind=inner
(EmailEvents
| where AttachmentCount > 0
|project NetworkMessageId, Subject, Timestamp, SenderFromAddress , SenderIPv4 , RecipientEmailAddress , AttachmentCount)
on NetworkMessageId, RecipientEmailAddress

 3.   When you want to search for an attribute/entity in multiple tables, use the search in operator instead of using union. For example, if you want to search for list of Urls, use the following query:

let ListOfIoc = dynamic([“t20saudiarabia@outlook.sa”, “t20saudiarabia@hotmail.com”, “t20saudiarabia@gmail.com”, “munichconference@outlook.com”,
“munichconference@outlook.de”, “munichconference1962@gmail.com”, “ctldl.windowsupdate.com”]);
search in (DeviceNetworkEvents, DeviceFileEvents, DeviceEvents, EmailUrlInfo )
Timestamp > ago(1d) and
RemoteUrl in (ListOfIoc) or FileOriginUrl in (ListOfIoc) or FileOriginReferrerUrl in (ListOfIoc)

4.   Using “Has” is better than “contains”: When looking for full tokens, “has” is more efficient,

      since it doesn’t look for substrings.

     Instead of using “contains”:

DeviceNetworkEvents
| where RemoteUrl contains “microsoft.com”
| take 50

            Use “has”:

DeviceNetworkEvents
| where RemoteUrl has “microsoft.com”
| take 50

     If possible, Use case-sensitive operators

DeviceNetworkEvents
| where RemoteUrl has_cs “microsoft.com”
| take 50

For more information about Advanced hunting and the features discussed in this article, read:

• Advanced hunting overview


• Advanced hunting best practices


• Advanced hunting APIs – Microsoft 365 security | Microsoft Docs


• Advanced hunting quota and resources


• Microsoft 365 Defender APIs


• Microsoft Defender for Endpoint APIs

As always, we’d love to know what you think. Leave us feedback directly on Microsoft 365 security center or start a discussion in Microsoft 365 Defender community.

Application Guard for Office is now generally available!

by Contributed | Jan 27, 2021 | Technology

This article is contributed. See the original author and article here.

As of today, Application Guard for Office is now generally available.

Files from the internet and other potentially unsafe locations can contain viruses, worms, or other kinds of malware that can harm your users’ computer and data. To help protect your users, Office opens files from potentially unsafe locations in Application Guard, a secure container that’s isolated from the device through hardware-based virtualization. When Office opens files in Application Guard, users can securely read, edit, print, and save those files without having to re-open files outside the container. This feature will be off by default.

Here is the installation guide to get started:
Application Guard for Office 365 for admins – Office 365 | Microsoft Docs

Customers will receive a Message center post on Wednesday, 1/27/2021. Microsoft 365 Roadmap Featured ID is 67101. Application Guard for Office is only available to organizations with a Microsoft 365 E5 or Microsoft 365 E5 Security license.

Azure SQL News Update: February 2021

by Contributed | Jan 27, 2021 | Technology

This article is contributed. See the original author and article here.

Today, and every Wednesday Data Exposed goes live at 9AM PT on LearnTV. Every 4 weeks (mostly the first week of each month), we’ll do a News Update. We’ll include product updates, videos, blogs, etc. as well as upcoming events and things to look out for. We’ve included an iCal file, so you can add a reminder to tune in live to your calendar. If you missed the episode, you can find it at https://aks.ms/AzureSQLYT along with other videos.

Along with the News Update on Data Exposed Live, you can read this blog to get the guide and links to all the things discussed in the show. Here’s the February 2021 update:

Product updates

Confidential computing using Always Encrypted with secure enclaves went into public preview last week. This expands Always Encrypted by enabling in-place encryption and rich confidential queries, including pattern matching, range comparisons, and sorting. This announcement is available in the new DC-series hardware configuration in preview for Azure SQL Database. Jakub Szymaszek came on the show to explain more and show a demo. You can read more in the blog here.

Also related to security is the topic of Azure Defender for SQL. Over the past month, a few updates and enhancements were made to Azure Defender for SQL in the Azure Security Center:

• Azure Defender for SQL servers on machines is generally available


• Azure Defender for SQL support for Azure Synapse Analytics dedicated SQL pool is generally available


• Revitalized Security Center experience in Azure SQL Database & SQL Managed Instance

A great resource which I have recently found is the Release notes for Azure Security Center. Not always related to SQL, but oftentimes relevant in some form or fashion.

Also slightly unrelated but at the same time very much related to Azure SQL was the announcement that Microsoft will establish a new datacenter region in Chile as part of a “Transforma Chile” initiative.

Microsoft recently released a website dedicated to the topic of .NET application migration. Of course, Azure SQL plays a big part here, so you might find the new site and resources interesting.

Alan Yu came on the show to talk about the latest updates to Azure Data Studio including some sneak peeks.

Videos

We continued to release new and exciting Azure SQL videos this month. Here is the list, or you can just see the playlist we created with all the episodes!

• What is aka.ms/sqlworkshops? – Buck Woody


• [MVP Edition] Always Encrypted in SQL Server 2019 – Mladen Prajdic


• Azure SQL Connectivity Performance Tips & Tricks – Silvano Coriani


• Simplify Authentication with Managed Identities for Azure Resources – Silvano Coriani


• How to Troubleshoot Elastic Job in Azure SQL Database – Kate Smith

Blogs

As always, our team is busy writing blogs to share with you all. Blogs contain announcements, tips and tricks, deep dives, and more. Here’s the list I have of SQL-related topics you might want to check out.

• Azure Blog
  
  
  
  • Azure SQL Database named among the top 3 databases of 2020, John ‘JG’ Chirapurath
  
  
  
  


• SQL Server Tech Community
  
  
  
  • Released: SSRS and SSAS Management Packs (7.0.29.0)
  
  
  • Deploy SQL Server on Azure Kubernetes Service cluster via Helm Charts – on a windows client machine!
  
  
  • Security Update for SQL Server 2012 SP4
  
  
  • Security Update for SQL Server 2014 SP3 GDR
  
  
  • Security Update for SQL Server 2014 SP3 CU4
  
  
  • Security Update for SQL Server 2016 SP2 GDR
  
  
  • Security Update for SQL Server 2016 SP2 CU15
  
  
  • Security Update for SQL Server 2017 RTM GDR
  
  
  • Security Update for SQL Server 2017 RTM CU22
  
  
  • Security Update for SQL Server 2019 RTM GDR
  
  
  • Security Update for SQL Server 2019 RTM CU8
  
  
  
  


• Azure SQL Tech Community
  
  
  
  • Getting insights into the utilization of SQL Server licenses on Azure to optimize cost
  
  
  • Intro into security principles in the context of database systems
  
  
  • The Principle of Least Privilege (POLP)
  
  
  • Always Encrypted with secure enclaves in Azure SQL Database preview
  
  
  • Improving your databases with Azure SQL Database tips
  
  
  • The Positive Impact of Intelligent Query Processing
  
  
  
  


• Azure SQL Devs’ Corner
  
  
  
  • Modern development: creating a REST API, via CI/CD and back
  
  
  • Solving the River Crossing problem with SQL Graph
  
  
  
  


• Azure Database Support (SQL-related posts)
  
  
  
  • Importing bacpac failed to Azure SQL with User, group, or role ‘XXX’ already exists
  
  
  • Lesson Learned #156: Creating a Linked Server connecting to Read-Scale Managed Instance Replica
  
  
  • Lesson Learned #157: Connecting to Azure Managed Instance Using Oracle Database Gateway for ODBC
  
  
  
  


• Microsoft Learn Tech Community
  
  
  
  • Chris Testa-O’Neill came on the show to talk to use about Microsoft Azure certification updates. This included the announcement of two new Data & AI related exams, DP-203 and AI-102. For more information, see the Microsoft Learn Blog with all the details.
  
  
  
  

Upcoming events

As always, there are lots of events coming up this month. Here are a few to put on your calendar and register for:

2/3: Data Exposed Special: The Azure SQL and Azure Data Factory engineering teams are partnering for an all-day learning event where the community can come together to learn about Azure SQL and Azure Data Factory, connect with the experts, and participate in a Hackathon, all across two time zones – America and Asia. Learn more

2/11: Azure Webinar Series: Gain Economic Value Migrating to Azure SQL

2/20: Multicloud4U
SQL Edge to Cloud, Bob Ward

2/27: Scottish Summit
Notebooks 101 for SQL People, Julie Koesmarno

In addition to these upcoming events, here’s the schedule for Data Exposed Live:
2/3: Around the Clock with Azure SQL & Azure Data Factory
2/10: Deep Dive: Best practices assessment for Azure SQL VMs, Managed Instances, and SQL Servers
2/17: Azure SQL Security: The What, Why & How of Securing your Data with Azure SQL
2/24: Something Old, Something New with Buck Woody 

Plus find new, on-demand Data Exposed episodes released every Thursday, 9AM PT at aka.ms/DataExposedyt

Featured Microsoft Learn Module

Learn with us! This month I highlighted the Deploy and configure servers, instances, and databases for Azure SQL module. Check it out!

Anna’s pick of the month: SQL Server Virtual Conference by C# Corner

This pick of the month is technically in January, but it’s at the very end and it hasn’t happened yet, so here we are. This is an awesome, community-run event taking place this Friday. When you register, you can buy a ticket (for only $5!) or donate to a charity focused on helping food-insecure children related to COVID 19. I am a big fan of C# Corner already but using this event to help raise money for the kids is special. Additionally, the SQL Server and Azure SQL product group leader, Asad Khan, will be delivering the keynote! Get your tickets here, the event is January 29-30^th.

Until next time…

That’s it for now! Be sure to check back next month for the latest updates, and tune into Data Exposed Live every Wednesday at 9AM PST on LearnTV. We also release new episodes on Thursdays at 9AM PST and new #MVPTuesday episodes on the last Tuesday of every month at 9AM PST at aka.ms/DataExposedyt.

Having trouble keeping up? Be sure to follow us on twitter to get the latest updates on everything, @AzureSQL. You can also download the iCal link with a recurring invite!

We hope to see you next time, on Data Exposed!

–Anna and Marisa