Microsoft SQL Server
Administration
Disk & memory
David Hoksza
Lecture Outline
•
Query lifecycle
•
Data storage
SELECT Query Lifecycle
1. SNIclient-server connection using TCP/IP (or other protocol) 2. SELECT statement using TDS
(Tabular Data Streams) messagesbetween TDS endpoints
3. Unwrapping TDS message
4. Sending “SQL Command” to Command Parser
5. Checking Plan Cache in the
Buffer Pool
6. If the plan is not cached, passing a query tree to the
Optimizer
7. Query planpassed to Query Executor
8. Passing Query Plan to Access Methods (AM)
9. Checking the existence of the required page in the Data Cache by the Buffer Manager
10. Passing the requested page(s) back to the AM
11. Passing the results set to the
Relational Engine
credit: SQL Server 2008 Internals and Troubleshooting
UDPATE Query Lifecycle
1. Identification of the page to update as in the SELECT lifecycle 2. The Access Methods
require Write-Ahead Logging from the Log Manager (part of the
Transaction Manager)
3. Page changes stored in the Transaction Log
4. The AM receive confirmation and pass the request on to the Buffer
Manager
5. Modification of the page in cache
6. Confirmation sent to the AM and the client
credit: SQL Server 2008 Internals and Troubleshooting
Buffer Manager (1)
•
Manages disk I/O functions
for bringing data and
index pages into the data
cache
•
Buffer
o 8KB memory page
o all memory not used by other
components stored in the buffer pool
•
Free buffer list
o low free buffer list o lazywriter process
•
Dirty pages
o pages modified in cache but not immediately written to disk
o durability maintained by the
transaction log
o flushing dirty pages
• worker thread when the free buffer list is low
• checkpoint
o DBCC DROPCLEANBUFFERS
• flush clean pages from cache • suitable for testing purposes
o sys.dm_os_buffer_descriptors
Buffer Manager (2)
•
Lazywriter
process
o process which periodically checks the size of the free buffer list o ages-out long enough not used pages
o releases memory to OS
•
Checkpoint
process
o dirty pages of committed transactions are written to disk o does not remove pages from cache
o occurs automatically or using CHECKPOINT command
o trace flag 3502 provides logging - DBCC TRACEON(3502, -1)
• DBCC TRACEOFF(3502, -1)
• checkpoint;
• EXEC xp_readerrorlog
•
BM state can be checked using the
Pages
•
8KB
o header o rows o row offsets•
Allocation units
o IN_ROW_DATA (hobt - Heap Or B-Tree)
• majority of data
o LOB_DATA (LOB)
• TEXT, NTEXT, IMAGE, …
• stored out-row
o sp_tableoption – “text in row” o ROW_OVERFLOW_DATA (SLOB - Small-LOB)
• VARCHAR, VARBINARY, .NET, …
• stored in-row unless the row exceeds 8KB
Extent
•
8 pages
⇒
64KB
•
Types
o uniform o mixed
•
First page of a new table
→
mixed extent
•
New index
o large → uniform extents
Space Management (1)
•
G
lobal
A
llocation
M
ap (
GAM
)
o 1= free extent, 0 = allocated extent o covers 64,000 extents
•
S
hared
G
lobal
A
llocation
M
ap
(
SGAM
)
o covers 64,000 extents
o 1= extent is being used as a mixed extent
and has a free page, 0 = extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used
•
P
age
F
ree
S
pace (
PFS
)
o allocation status of each page – 1B
Extent status
GAM
bit
setting
SGAM
bit
setting
Free
1
Uniform extent
(allocated)
0
0
Mixed extent
with free pages
0
1
Full mixed
extent
Space Management (2)
•
I
ndex
A
llocation
M
ap (
IAM
)
o tracking extents in 4GB used by an
allocation unit
o can be linked → IAM chain
o maps space allocation for
• heaps and b-trees • LOB data
• row-overflow data
o IAM page
• 96-byte page header containing info
about what part of space the IAM maps
• IAM page header (8 page-pointer slots)
• bitmap for extents belonging to the
allocation unit
o sys.system_internals_allocation_units
•
Inserting a new row
o IAM → extents for the allocation unit
Memory – 32 bit system
•
4GB tuning
o allows to use 3GB user mode
address space
o Windows Server 2003 • /3GB in boot.ini o Windows Server 2008
• BCDEdit /set increaseUserVA 3072
o application needs to be linked with
/LARGEADDRESSAWARE
o nonpaged pool, paged pool and
system PTEs (Page Table Entry) need to be monitored
•
Physical Address Extension (PAE)
o introduced by Intel
o address bus = 36 bits →64GB
o Windows Enterprise o Windows Server 2003
• /PAE in boot.ini
o Windows Server 2008
• bcdedit /set [{ID}] pae ForceEnable
o applications must be written to be able to
use AWE (Address Windowing Extensions) which allows a process to access memory outside of its VAS (by mapping this
memory into VAS)
o PAE must be enabled in SQL Server by
sp_configure or SSMS
o SQL Server service needs to have “Lock Pages in Memory” privilege
o exploitable by data cache only
•
4GB address space
o 2GB user mode o 2GB kernel mode
Memory information and
rerestriction
•
Memory size restriction
o min server memory o max server memory
• sp_configure 'max server memory', 4096;
•
sys.dm_os_sys_info
o information about the computer on which SQL Server is installed including
the resources available to and consumed
• committed_kb
o committed physical memory in the memory manager
• commit_target_kb
Memory Architecture
•
Memory nodes
o Memory division
o At least one node depending on using the NUMA (Non-Uniform Memory
Access) architecture
• SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks
•
Memory allocators
o Memory allocation on the memory nodes go through memory allocator
routines tied to the memory nodes
o Page allocators, Virutal memory allocator, Shared memory allocator
•
Memory clerks
o each consumer allocates memory through a memory clerk o Can be used to track memory usage by components
o sys.dm_os_memory_clerks
•
Memory broker
o centralized mechanism to distribute memory or control the allocations made
by each component in SQL Server
Memory clerks
•
Clerk types (common caching mechanism implemented
by SQLOS)
o
object store
•
simple store, homogeneous data (SNI – pooling network buffers)
o
cache store
•
SQLOS management of life time and visibility control (plan cache)
o
user store
•
cache store + storage semantics (metadata cache)
o
different stores use different replacing strategies and costing
Cache
•
sys.dm_os_memory_cache_counters
•
Caches are clerks
•
Data Cache
o largest cache in the buffer pool o sys.dm_os_buffer_descriptors
•
Plan Cache
o caching of execution plans o sys.dm_exec_cached_plans
•
DBCC FREESYSTEMCACHE (DBCC FREESYSTEMCACHE
Monitoring Memory
•
Performance monitor (perfmon)
o useful counters
• Memory object • Process object • Paging file
• SQL Server:Buffer Manager (MSSQL$NDBI039:Buffer Manager) object
