Microsoft SQL Server Administration

18  Download (0)

Full text

(1)

Microsoft SQL Server

Administration

Disk & memory

David Hoksza

(2)

Lecture Outline

Query lifecycle

Data storage

(3)

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

(4)

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

(5)

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

(6)

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

(7)

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

(8)

Extent

8 pages

64KB

Types

o uniform o mixed

First page of a new table

mixed extent

New index

o large → uniform extents

(9)

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

(10)

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

(11)
(12)

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

(13)

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

(14)

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

(15)

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

(16)

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

(17)

Monitoring Memory

Performance monitor (perfmon)

o useful counters

Memory objectProcess objectPaging file

SQL Server:Buffer Manager (MSSQL$NDBI039:Buffer Manager) object

DMVs

o sys.dm_os_performance_counters o sys.dm_os_memory_clerks o sys.dm_os_process_memory

DBCC MEMORYSTATUS

o https://support.microsoft.com/kb/907877/en-us

SQL Server Profiler

(18)

Tasks

1. Identify names of 10 objects with the highest number of data

pages (sys.allocation_units, sys.partitions, OBJECT_NAME)

2. Create a table with rows exceeding page size and check

how ROW_OVERFLOW_DATA allocation units are used

(sys.allocation_units, REPLICATE)

3. Create a stored procedure returning the number of records

in a table based on the sys.partitions view.

4. Find out the number of dirty pages in each database on the

server (verify by using the CHECKPOINT command)

(sys.dm_os_buffer_descriptors).

5. Is a page set to dirty if an UPDATE changes a column to the

same value (UPDATE t SET col = col)?

Figure

Updating...

References

Related subjects :