coeo sql memory
Post on 05-Apr-2018
228 Views
Preview:
TRANSCRIPT
-
8/2/2019 Coeo SQL Memory
1/47
Christian Bolton MCA:Database, MCM:SQL Server
Database Architectchristian@coeo.com
http://coeo.com
http://sqlblogcasts.com/blogs/christian
mailto:christian@coeo.comhttp://coeo.com/http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://coeo.com/mailto:christian@coeo.com -
8/2/2019 Coeo SQL Memory
2/47
32-bit memory addressing
Memtoleave
64-bit memory addressingQuery/Workspace Memory
Procedure Cache
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
3/47
232 = 4294967296 = 4GB
Virtual address space
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
4/47
Memory
SQLServer
Inst 2
0x00000000
0x7FFFFFFF
SQL
Server
Inst 1
2GB
2GB
0x00000000
0x7FFFFFFF
0001 0010 0101 10
0110 0000 1001 00
0101 0000 1101 00
0110 0111 1100 00
0001 0010 0111 110110 0000 1001 00
0101 0000 1101 00
0110 0111 1100 00
Page file
Physical RAM
Virtual
Memory
Manager
0101 0000 1101 00
0110 0111 1100 00
0001 0010 0111 11
0110 0000 1001 00
0101 0000 1101 000110 0111 1100 00
0001 0010 0101 10
0110 0000 1001 00
PTEs
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
5/47
> 2 GB virtual address
space
/3GB switch in boot.ini
/LARGEADDRESSAWARE
System PTEs
Paged Pool
Non_Paged Pool
0x00000000
0xFFFFFFFF
0x80000000
0x7FFFFFFF
0xC0000000
0xBFFFFFFF
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
6/47
Page Table Entries (PTEs)4KB mapping between virtual and physical memory
Memory\Free System Page Table Entries
Should be > 10,000
SymptomsServer stops responding
/USERVA switch in Windows Server 2003Gives less than 3GB to user mode
Extra space kept in reserve for System PTEs
Recommended values between 2900 and 3030
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
7/47
Paged Pool and Non-Paged Pool
Used to allocate system memory
Non-Paged Pool used by drivers
Accessed at any Interrupt Request Level (H/W priority)
Maximum values determined at boot time
Examples for Windows 2003 SP1:
System RAM NonPaged Max Paged Max
512MB 125MB 184MB
1024MB 202MB 168MB
1536MB 254MB 352MB
2048MB 252MB 352MB
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
8/47
Paged Pool and Non-Paged Pool Usage
Memory\Pool Paged Bytes
Memory\Pool NonPaged Bytes
Debugging tools or sysinternals Process Explorerlinked to Microsofts symbols server to get actual
max values
Windows Server 2008 and Vista have
dynamic max values
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
9/47
> 2/3GB virtualaddress space
PAE
Physical
Address
Extensions
AWE Address
Windowing
Extensions
Data pages only
0x00000000
0xFFFFFFFF
0x80000000
0x7FFFFFFF
0x00000000
0xFFFFFFFF
0xC0000000
0xBFFFFFFF
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
10/47
0x00000000
0xFFFFFFFF
0x80000000
0x7FFFFFFF
0101 0000 1101 0001 0111
0001 0110 1001 0001 0110
1101 0001 1101 0101 0001
0101 1111 1001 0001 0000
0000 1100 1101 1101 1111
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
11/47
/PAE and /3GB together?
Not recommended
/PAE increases PTE size from 4KB to 8KB
Already reduced in size by /3GB
Not supported 16GB+ RAM due to the kernel mode
space required to support that much RAM
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
12/47
Area of SQL Server address space
Outside the buffer pool
Linked servers, XPs, COM objectsMulti-Page Allocator (MPA)
-
8/2/2019 Coeo SQL Memory
13/47
MemToLeave = MemReserved + (NumThreads
* StackSize)
MemReserved = 256 MB(unless a different
value is specified withg at startup)
NumThreads = the total number of worker
threads configured
StackSize = (0.5MB x86, 2MB x64, 4MB IA64)
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
14/47
Calculating Number of worker threads
select max_workers_count
from sys.dm_os_sys_info
Number of CPUs 32-bit computer 64-bit computer
-
8/2/2019 Coeo SQL Memory
15/47
32-bit computer
256MB base value
-
8/2/2019 Coeo SQL Memory
16/47
-
8/2/2019 Coeo SQL Memory
17/47
-
8/2/2019 Coeo SQL Memory
18/47
-
8/2/2019 Coeo SQL Memory
19/47
Buffer Pool will never
be more than physical
RAM
1GB RAM
VAS/memtoleave:
834MB!
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
20/47
-
8/2/2019 Coeo SQL Memory
21/47
-
8/2/2019 Coeo SQL Memory
22/47
-
8/2/2019 Coeo SQL Memory
23/47
264
=18,446,744,073,709,551,616
=17,179,869,184 TB
= 16 EB
Virtual address space
8TB on x64
7TB on IA64
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
24/47
2 flavours
x64 created by AMD for server and desktop
IA64 strictly patented by Intel and HP for servers
Windows XP x64 announcedIntel responds with x64 compatible extensionscalled EM64T
AMD renamed their implementation AMD64
Intel renamed theirs to Intel64
We now refer to x64 as meaning either platform
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
25/47
64-bit computer
256MB base value
-
8/2/2019 Coeo SQL Memory
26/47
-
8/2/2019 Coeo SQL Memory
27/47
-
8/2/2019 Coeo SQL Memory
28/47
-
8/2/2019 Coeo SQL Memory
29/47
-
8/2/2019 Coeo SQL Memory
30/47
By default SQL engine will notacquire all requested
memory upon startup
Configure Max Server Memory
Leave some headroom for the OS and for memoryrequests outside the buffer pool
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
31/47
Calculating Max Server Memory
Calculate worse case
2GB for the OS
xGB for worker threads based on table below (2MB each on x64)
1GB for Multi-page allocations, linked servers etc
1-3GB for other applications
Number of CPUs 32-bit computer 64-bit computer
-
8/2/2019 Coeo SQL Memory
32/47
Scenario
8 cores, 32GB RAM
2GB for the OS
1GB for worker threads1GB for Multi-page allocations, linked servers etc
1GB for backup program
= 5GB
Max Server Memory to 27GB
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
33/47
SQL Server responds to memory pressure
If its not quick enough Windows can
aggressively trim SQL Servers working set
New message in SP2
A significant part of SQL Server process
memory has been paged out. This may result
in a performance degradation.
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
34/47
3 options to avoid this
Set max server memory appropriately so that
windows never needs to trim SQL Server
Upgrade to Windows Server 2008
Use AWE to allocate memory
Setting Max Server Memory is the safest
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
35/47
Why does AWE help?
http://support.microsoft.com/kb/918483
Memory allocatedusing the AWE APIs
cant be trimmed
A user must havethe Lock Pages in
Memory privilegeto use the AWE APIs
On 64-bit the AWEAPIs are used to lock
pages in the workingset
If service account forSQL Server has the
privilege and its
Enterprise Edition,SQL will use AWE
Only data cachethats locked, others
can be trimmed
AWE enabled
option insp_configure
ignored on 64-bit
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
36/47
Query Memory / Workspace Memory
Use by Hash and Sort operators to process data
Memory requirements of individual operators added up
to get total Query Memory requirement
Non-overlapping operators can share
Showplan with runtime information contains information
about the actual grant
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
37/47
-
8/2/2019 Coeo SQL Memory
38/47
Allocating Query Memory
Total Query Memory dynamically managed between 25%
and 75% on non-AWE buffer pool
Higher if buffer pool not under pressure
Perfmon: Memory Manager:Maximum Workspace
Memory
DBCC MEMORYSTATUS: Query Memory Objects
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
39/47
Allocating Query Memory
5% of Total Workspace Memory is reserved for small
queries
Max cost 3
Memory required < 5MB
DBCC MEMORYSTATUS: Small Query Memory Objects
No individual query gets a Grant or more than 20% of the
Total Query Memory (after the 5% is reserved)
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
40/47
Query Memory Grant Queue
Organised as 5 queues based on query cost
< 101, < 102, < 103, = 104
Each query put into appropriate queue based on costFCFS policy within each queue
Lets smaller queries be processed even as largerqueries wait
Query may timeout in Memory Grant QueueDefault timeout 25 * cost of the query in seconds
Controlled by the Query Wait configuration option
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
41/47
140
890
80
Used
Free
< 103< 10 < 102 < 104 < 104
1GB Query Memory, 1 CPU
60
120
120
80100120
150Memory
Grants
Outstanding
Memory
Grants
Pending
12020
60
910
180
790
60
910
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
42/47
Query Memory Diagnostics
Perfmon: Memory ManagerGranted Workspace Memory
Maximum Workspace MemoryMemory Grants Pending
Memory Grants Outstanding
DBCC MEMORYSTATUSQuery Memory Objects
Small Query Memory Objects
sys.dm_os_wait_stats
RESOURCE_SEMAPHORE
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
43/47
Procedure Cache
Allocated from the buffer pool
Query Plans Stored in Procedure Cache
Compiled plan
Executable plan
Parse tree
Extended stored procedure
sys.dm_exec_cached_plans to see the entries in thecache
DBCC MEMORYSTATUS to see the size of the cache
http://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christianhttp://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
44/47
-
8/2/2019 Coeo SQL Memory
45/47
Where does cost from?
http://sqlblogcasts.com/blogs/christian -
8/2/2019 Coeo SQL Memory
46/47
-
8/2/2019 Coeo SQL Memory
47/47
top related