Database Reference
In-Depth Information
A few of the common resolutions for memory bottlenecks are as follows:
•
Optimizing application workload
•
Allocating more memory to SQL Server
•
Moving in-memory tables back to standard storage
•
Increasing system memory
•
Changing from a 32-bit to a 64-bit processor
•
Enabling 3GB of process space
•
Compressing data
•
Addressing fragmentation
And of course, fixing any of the query issues that can lead to excessive memory use is always an option. Let's take
a look at each of these in turn.
Optimizing Application Workload
Optimizing application workload is the most effective resolution most of the time, but because of the complexity and
challenges involved in this process, it is usually considered last. To identify the memory-intensive queries, capture all
the SQL queries using Extended Events (which you will learn how to use in Chapter 3) and then group the trace output
on the
Reads
column. The queries with the highest number of logical reads contribute most often to memory stress,
but there is not a linear correlation between the two. You can also use sys.dm_exec_query_stats, a DMV that collects
query metrics for queries that are actively in cache to identify the same thing. But, since this DMV is based on cache,
it may not be as accurate as capturing metrics using Extended Events, although it will be quicker and easier. You will
see how to optimize those queries in more detail throughout this topic.
Allocating More Memory to SQL Server
As you learned in the “SQL Server Memory Management” section, the max server memory configuration can limit
the maximum size of the SQL Server buffer memory pool. If the memory requirement of SQL Server is more than the
max server memory value, which you can tell through the number of hard page faults, then increasing the value will
allow the memory pool to grow. To benefit from increasing the max server memory value, ensure that enough physical
memory is available in the system.
If you are using in-memory OLTP storage, you may need to adjust the memory percentages allocated to the
resource pools you have defined for your in-memory objects. But, that will take memory from other parts of your SQL
Server instance.
Moving In-Memory Tables Back to Standard Storage
Introduced in SQL Server 2014, a new table type was introduced called the
in-memory
table. This moves the storage of
tables from the disk to memory, radically improving the performance. But, not all tables or all workloads will benefit
from this new functionality. You need to keep an eye on your general query performance metrics for in-memory
tables and take advantage of the specific DMVs that let you monitor the in-memory tables. I'll be covering all this in
detail in Chapter 23. If your workload doesn't work well with in-memory tables or you just don't have enough memory
in the system, you may need to move those in-memory tables back to disk storage.
