Transcription
Microsoft SQL ServerAdministrationDisk & memoryDavid Hokszahttp://siret.cz/hoksza
Lecture Outline Query lifecycle Data storage SQL Server Memory
SELECT Query Lifecycle1.2.3.4.5.6.7.8.9.10.11.SNI client-server connectionusing TCP/IP (or other protocol)SELECT statement using TDS(Tabular Data Streams)messages between TDSendpointsUnwrapping TDS messageSending “SQL Command” toCommand ParserChecking Plan Cache in theBuffer PoolIf the plan is not cached,passing a query tree to theOptimizerQuery plan passed to QueryExecutorPassing Query Plan to AccessMethods (AM)Checking the existence of therequired page in the DataCache by the Buffer ManagerPassing the requested page(s)back to the AMPassing the results set to theRelational EngineSQLOScredit: SQL Server 2008 Internals and Troubleshooting
UDPATE Query Lifecycle1.2.3.4.5.6.Identification of thepage to update as inthe SELECT lifecycleThe Access Methodsrequire Write-AheadLogging from the LogManager (part of theTransactionManager)Page changes storedin the Transaction LogThe AM receiveconfirmation andpass the request onto the BufferManagerModification of thepage in cacheConfirmation sent tothe AM and the clientSQLOScredit: SQL Server 2008 Internals and Troubleshooting
Buffer Manager (1) Manages disk I/O functions Dirty pagesfor bringing data ando pages modified in cache but notindex pages into the dataimmediately written to diskcacheo durability maintained by the Buffero 8KB memory pageo all memory not used by othercomponents stored in the bufferpool Free buffer listo low free buffer listo lazywriter processtransaction logo flushing dirty pages worker thread when the freebuffer list is low checkpointo DBCC DROPCLEANBUFFERS flush clean pages from cache suitable for testing purposeso sys.dm os buffer descriptors is modified
Buffer Manager (2) Lazywriter processo process which periodically checks the size of the free buffer listo ages-out long enough not used pageso releases memory to OS Checkpoint processoooodirty pages of committed transactions are written to diskdoes not remove pages from cacheoccurs automatically or using CHECKPOINT commandtrace flag 3502 provides logging - DBCC TRACEON(3502, -1) DBCC TRACEOFF(3502, -1) checkpoint; EXEC xp readerrorlog BM state can be checked using thesys.dm os performance counters view
Pages 8KBo headero rowso row offsets Allocation unitso IN ROW DATA (hobt - Heap Or B-Tree) majority of datao LOB DATA (LOB) TEXT, NTEXT, IMAGE, stored out-rowo sp tableoption – “text in row”o ROW OVERFLOW DATA (SLOB - Small-LOB) VARCHAR, VARBINARY, .NET, stored in-row unless the row exceeds 8KBo sys.allocation units
Extent 8 pages 64KB Typeso uniformo mixed First page of a new table mixed extent New indexo large uniform extentso small mixed extents
Space Management (1) Global Allocation Map (GAM)o 1 free extent, 0 allocated extento covers 64,000 extents Shared Global Allocation Map(SGAM)o covers 64,000 extentso 1 extent is being used as a mixed extentand has a free page, 0 extent is not usedas a mixed extent, or it is a mixed extentand all its pages are being used Page Free Space (PFS)o allocation status of each page – 1BExtent statusGAM SGAMbitbitsetting settingFree1Uniform extent(allocated)00Mixed extentwith free pages01Full mixedextent00
Space Management (2) Index Allocation Map (IAM)o tracking extents in 4GB used by anallocation unito can be linked IAM chaino maps space allocation for heaps and b-trees LOB data row-overflow datao IAM page 96-byte page header containing infoabout what part of space the IAMmaps IAM page header (8 page-pointerslots) bitmap for extents belonging to theallocation unito sys.system internals allocation units Inserting a new rowo IAM extents for the allocation unito PFS free pages in identified extents
Database File Structure
Memory – 32 bit system 4GB address spaceo 2GB user modeo 2GB kernel mode 4GB tuningoooooallows to use 3GB user modeaddress spaceWindows Server 2003 /3GB in boot.iniWindows Server 2008 BCDEdit /set increaseUserVA3072application needs to be linked with/LARGEADDRESSAWAREnonpaged pool, paged pool andsystem PTEs (Page Table Entry)need to be monitored Physical Address Extension (PAE)ooooooooointroduced by Inteladdress bus 36 bits 64GBWindows EnterpriseWindows Server 2003 /PAE in boot.iniWindows Server 2008 bcdedit /set [{ID}] pae ForceEnableapplications must be written to be able touse AWE (Address Windowing Extensions)which allows a process to access memoryoutside of its VAS (by mapping thismemory into VAS)PAE must be enabled in SQL Server bysp configure or SSMSSQL Server service needs to have “LockPages in Memory” privilegeexploitable by data cache only
Memory information andrerestriction Memory size restrictiono min server memoryo max server memory sp configure 'max server memory', 4096; sys.dm os sys infoo information about the computer on which SQL Server is installed includingthe resources available to and consumed committed kbo committed physical memory in the memory manager commit target kbo memory needed by the memory manager
Memory Architecture Memory nodeso Memory divisiono At least one node depending on using the NUMA (Non-Uniform MemoryAccess) architecture SELECT DISTINCT memory node id FROM sys.dm os memory clerks Memory allocatorso Memory allocation on the memory nodes go through memory allocatorroutines tied to the memory nodeso Page allocators, Virutal memory allocator, Shared memory allocator Memory clerkso each consumer allocates memory through a memory clerko Can be used to track memory usage by componentso sys.dm os memory clerks Memory brokero centralized mechanism to distribute memory or control the allocations madeby each component in SQL Servero monitors the demand consumption of memory by each component
Memory clerks Clerk types (common caching mechanism implementedby 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 costingmechanisms
Cache sys.dm os memory cache counters Caches are clerks Data Cacheo largest cache in the buffer poolo sys.dm os buffer descriptors Plan Cacheo caching of execution planso sys.dm exec cached plans DBCC FREESYSTEMCACHE (DBCC FREESYSTEMCACHE('SQL Plans'))
Monitoring Memory Performance monitor (perfmon)o useful counters Memory object Process object Paging file SQL Server:Buffer Manager (MSSQL NDBI039:Buffer Manager) object DMVso sys.dm os performance counterso sys.dm os memory clerkso sys.dm os process memory DBCC MEMORYSTATUSo https://support.microsoft.com/kb/907877/en-us SQL Server Profiler
Tasks1. Identify names of 10 objects with the highest number of datapages (sys.allocation units, sys.partitions, OBJECT NAME)2. Create a table with rows exceeding page size and checkhow ROW OVERFLOW DATA allocation units are used(sys.allocation units, REPLICATE)3. Create a stored procedure returning the number of recordsin a table based on the sys.partitions view.4. Find out the number of dirty pages in each database on theserver (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 thesame value (UPDATE t SET col col)?
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 plan passed 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 .
