guida sull'architettura di gestione della memoriaMemory Management Architecture Guide

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzureSìAzure SQL Data Warehouse Sì Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Virtual Memory Manager di WindowsWindows Virtual Memory Manager

Viene eseguito il mapping delle aree di cui è stato eseguito il commit dello spazio degli indirizzi alla memoria fisica disponibile tramite Virtual Memory Manager (VMM) di Windows.The committed regions of address space are mapped to the available physical memory by the Windows Virtual Memory Manager (VMM).

Per altre informazioni sulla quantità di memoria fisica supportata dai diversi sistemi operativi, vedere la documentazione di Windows relativa ai limiti di memoria per le versioni di Windows.For more information on the amount of physical memory supported by different operating systems, see the Windows documentation on Memory Limits for Windows Releases.

I sistemi con memoria virtuale consentono il commit in eccesso della memoria fisica, per cui il rapporto tra memoria virtuale e memoria fisica può essere maggiore di 1:1.Virtual memory systems allow the over-commitment of physical memory, so that the ratio of virtual-to-physical memory can exceed 1:1. Di conseguenza, i computer con diverse configurazioni di memoria fisica consentono l'esecuzione di programmi di dimensioni elevate.As a result, larger programs can run on computers with a variety of physical memory configurations. Tuttavia l'utilizzo di una quantità di memoria virtuale di molto superiore alla combinazione dei set di lavoro medi per tutti i processi determina un peggioramento delle prestazioni.However, using significantly more virtual memory than the combined average working sets of all the processes can cause poor performance.

Architettura della memoria di SQL ServerSQL Server SQL ServerSQL Server Memory Architecture

In SQL ServerSQL Server la memoria viene acquisita e liberata in modo dinamico in base alle esigenze. SQL ServerSQL Server dynamically acquires and frees memory as required. In genere non è necessario che un amministratore specifichi la quantità di memoria da allocare a SQL ServerSQL Server. L'opzione corrispondente, tuttavia, è ancora disponibile e in alcuni ambienti è necessario impostarla.Typically, an administrator does not have to specify how much memory should be allocated to SQL ServerSQL Server, although the option still exists and is required in some environments.

Uno dei principali obiettivi di progettazione di tutti i software di database è la riduzione del disco I/O dal momento che le letture e le scritture del disco sono le operazioni che consumano più risorse.One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. SQL ServerSQL Server crea un pool di buffer in memoria per contenere le pagine lette dal database. builds a buffer pool in memory to hold pages read from the database. Gran parte del codice in SQL ServerSQL Server è dedicata alla riduzione del numero di letture e scritture fisiche tra il disco e il pool di buffer.Much of the code in SQL ServerSQL Server is dedicated to minimizing the number of physical reads and writes between the disk and the buffer pool. SQL ServerSQL Server tenta di raggiungere un equilibrio tra i due obiettivi: tries to reach a balance between two goals:

  • Evitare che le dimensioni del pool di buffer aumentino fino a limitare la memoria dell'intero sistema.Keep the buffer pool from becoming so big that the entire system is low on memory.
  • Ridurre al minimo l'I/O fisico sui file di database aumentando la dimensione del pool di buffer fino a raggiungere il valore massimo possibile.Minimize physical I/O to the database files by maximizing the size of the buffer pool.

Nota

In un sistema con carichi pesanti, alcune query di grandi dimensioni la cui esecuzione richiede una grande quantità di memoria non possono ottenere la quantità minima di memoria richiesta e ricevono un errore di timeout mentre sono in attesa delle risorse della memoria.In a heavily loaded system, some large queries that require a large amount of memory to run cannot get the minimum amount of requested memory and receive a time-out error while waiting for memory resources. Per risolvere il problema, aumentare il valore dell' opzione query wait.To resolve this, increase the query wait Option. Per una query parallela, provare a ridurre l' opzione Massimo grado di parallelismo.For a parallel query, consider reducing the max degree of parallelism Option.

Nota

In un sistema con carichi elevati e con un numero eccessivo di richieste di memoria, le query con merge join, sort e bitmap nel piano di query possono eliminare il bitmap quando non ottengono la memoria minima necessaria per il bitmap.In a heavily loaded system under memory pressure, queries with merge join, sort and bitmap in the query plan can drop the bitmap when the queries do not get the minimum required memory for the bitmap. Ciò può influire sulle prestazioni delle query e, se il processo di ordinamento non può essere contenuto nella memoria, è possibile aumentare l'uso delle tabelle di lavoro nel database tempdb, causando la crescita di tempdb.This can affect the query performance and if the sorting process can not fit in memory, it can increase the usage of worktables in tempdb database, causing tempdb to grow. Per risolvere questo problema, aggiungere memoria fisica o ottimizzare le query per l'uso di un piano di query diverso e più rapido.To resolve this problem add physical memory or tune the queries to use a different and faster query plan.

Assegnazione della quantità massima di memoria a SQL ServerSQL ServerProviding the maximum amount of memory to SQL ServerSQL Server

Usando AWE e il privilegio Blocco di pagine in memoria, è possibile fornire al motore di database di SQL ServerSQL Server le quantità di memoria seguenti.By using AWE and the Locked Pages in Memory privilege, you can provide the following amounts of memory to the SQL ServerSQL Server Database Engine.

Nota

La tabella seguente include una colonna per le versioni a 32 bit non più disponibili.The following table includes a column for 32-bit versions, which are no longer available.

32 bit 132-bit 1 64 bit64-bit
Memoria convenzionaleConventional memory Tutte le edizioni di SQL ServerSQL Server .All SQL ServerSQL Server editions. Fino al limite dello spazio degli indirizzi virtuali di processo:Up to process virtual address space limit:
- 2 GB- 2 GB
- 3 GB con parametro di avvio /3gb 2- 3 GB with /3gb boot parameter 2
- 4 GB su WOW64 3- 4 GB on WOW64 3
Tutte le edizioni di SQL ServerSQL Server .All SQL ServerSQL Server editions. Fino al limite dello spazio degli indirizzi virtuali di processo:Up to process virtual address space limit:
- 7 TB con architettura IA64 (IA64 non è supportato in SQL Server 2012SQL Server 2012 e versioni successive)- 7 TB with IA64 architecture (IA64 not supported in SQL Server 2012SQL Server 2012 and above)
- Valore massimo del sistema operativo con architettura x64 4- Operating system maximum with x64 architecture 4
Meccanismo AWE (consente a SQL ServerSQL Server di superare il limite dello spazio degli indirizzi virtuali di processo nelle piattaforme a 32 bit)AWE mechanism (Allows SQL ServerSQL Server to go beyond the process virtual address space limit on 32-bit platform.) SQL ServerSQL Server edizioni Standard, Enterprise e Developer: il pool di buffer è in grado di accedere a una memoria limite di 64 GB. Standard, Enterprise, and Developer editions: Buffer pool is capable of accessing up to 64 GB of memory. Non applicabile 5Not applicable 5
Privilegio Blocco di pagine in memoria del sistema operativo (consente di bloccare la memoria fisica, impedendo il paging della memoria bloccata da parte del sistema operativo). 6Lock pages in memory operating system (OS) privilege (allows locking physical memory, preventing OS paging of the locked memory.) 6 SQL ServerSQL Server edizioni Standard, Enterprise e Developer: necessari per il processo di SQL ServerSQL Server che usa il meccanismo AWE. Standard, Enterprise, and Developer editions: Required for SQL ServerSQL Server process to use AWE mechanism. La memoria allocata tramite il meccanismo AWE non può essere trasferita.Memory allocated through AWE mechanism cannot be paged out.
La concessione di questo privilegio senza l'attivazione di AWE non ha alcun effetto sul server.Granting this privilege without enabling AWE has no effect on the server.
Usato solo quando necessario, ovvero in presenza di segnali di page out del processo sqlservr. In questo caso, verrà segnalato l'errore 17890 nel log degli errori, simile a quello riportato nell'esempio seguente:A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.Only used when necessary, namely if there are signs that sqlservr process is being paged out. In this case, error 17890 will be reported in the Errorlog, resembling the following example: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

1 Le versioni a 32 bit non sono disponibili a partire da SQL Server 2014SQL Server 2014.1 32-bit versions are not available starting with SQL Server 2014SQL Server 2014.
2 /3gb è un parametro di avvio del sistema operativo.2 /3gb is an operating system boot parameter. Per altre informazioni, consultare MSDN Library.For more information, visit the MSDN Library.
3 WOW64 (Windows on Windows 64) è una modalità che consente di eseguire la versione a 32 bit di SQL ServerSQL Server in un sistema operativo a 64 bit.3 WOW64 (Windows on Windows 64) is a mode in which 32-bit SQL ServerSQL Server runs on a 64-bit operating system.
4 SQL ServerSQL Server Standard Edition supporta fino a 128 GB.4 SQL ServerSQL Server Standard Edition supports up to 128 GB. SQL ServerSQL Server Enterprise Edition supporta il valore massimo del sistema operativo. Enterprise Edition supports the operating system maximum.
5 Si noti che l'opzione sp_configure awe enabled è presente in SQL ServerSQL Servera 64 bit, ma viene ignorata.5 Note that the sp_configure awe enabled option was present on 64-bit SQL ServerSQL Server, but it is ignored.
6 Se viene concesso il privilegio Blocco di pagine in memoria (LPIM), sia su supporto a 32 bit per AWE che a 64 bit indipendente, è consigliabile impostare anche la memoria massima del server.6 If lock pages in memory privilege (LPIM) is granted (either on 32-bit for AWE support or on 64-bit by itself), we recommend also setting max server memory. Per altre informazioni su Blocco di pagine in memoria, vedere Opzioni di configurazione del server Server Memory.For more information on LPIM, refer to Server Memory Server Configuration Options

Nota

Le versioni precedenti di SQL ServerSQL Server potrebbero essere eseguite in un sistema operativo a 32 bit.Older versions of SQL ServerSQL Server could run on a 32-bit operating system. L'accesso a più di 4 gigabyte (GB) di memoria in un sistema operativo a 32 bit richiede estensioni AWE (Address Windowing Extensions) per la gestione della memoria.Accessing more than 4 gigabytes (GB) of memory on a 32-bit operating system required Address Windowing Extensions (AWE) to manage the memory. Queste estensioni non sono necessarie quando SQL ServerSQL Server è in esecuzione su sistemi operativi a 64 bit.This is not necessary when SQL ServerSQL Server is running on 64-bit operation systems. Per altre informazioni su AWE, vedere Spazio degli indirizzi di processo e Gestione della memoria per database di grandi dimensioni nella documentazione di SQL Server 2008SQL Server 2008.For more information about AWE, see Process Address Space and Managing Memory for Large Databases in the SQL Server 2008SQL Server 2008 documentation.

Modifiche apportate alla gestione della memoria a partire da SQL Server 2012SQL Server 2012Changes to Memory Management starting with SQL Server 2012SQL Server 2012

Nelle versioni precedenti di SQL Server ( SQL Server 2005SQL Server 2005, SQL Server 2008SQL Server 2008 e SQL Server 2008 R2SQL Server 2008 R2), l'allocazione di memoria veniva gestita con cinque meccanismi diversi:In earlier versions of SQL Server ( SQL Server 2005SQL Server 2005, SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2), memory allocation was done using five different mechanisms:

  • Allocatore di pagine singole, che include solo le allocazioni di memoria minori o uguali a 8 KB nel processo di SQL ServerSQL Server.Single-page Allocator (SPA), including only memory allocations that were less than, or equal to 8-KB in the SQL ServerSQL Server process. Le opzioni di configurazione max server memory (MB) e min server memory (MB) che determinano i limiti di memoria fisica usata dall'allocatore di pagine singole.The max server memory (MB) and min server memory (MB) configuration options determined the limits of physical memory that the SPA consumed. Il pool di buffer è contemporaneamente il meccanismo per l'allocazione di pagine singole e il maggiore consumer di allocazioni di pagine singole.THe buffer pool was simultaneously the mechanism for SPA, and the largest consumer of single-page allocations.
  • Allocatore di più pagine, per le allocazioni di memoria che richiedono più di 8 KB.Multi-Page Allocator (MPA), for memory allocations that request more than 8-KB.
  • Allocatore CLR, che include gli heap CLR SQL e le relative allocazioni globali create durante l'inizializzazione di CLR.CLR Allocator, including the SQL CLR heaps and its global allocations that are created during CLR initialization.
  • Allocazioni di memoria per gli stack di thread nel processo di SQL ServerSQL Server.Memory allocations for thread stacks in the SQL ServerSQL Server process.
  • Allocazioni di Windows dirette, per le richieste di allocazione di memoria effettuate direttamente da Windows.Direct Windows allocations (DWA), for memory allocation requests made directly to Windows. Sono incluse le allocazioni per l'utilizzo dell'heap di Windows e le allocazioni virtuali dirette effettuate dai moduli caricati nel processo di SQL ServerSQL Server.These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL ServerSQL Server process. Alcuni esempi di queste richieste di allocazione di memoria sono le allocazioni da DLL di stored procedure estese, gli oggetti creati tramite procedure di automazione (chiamate sp_OA) e le allocazioni dai provider di server collegati.Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

A partire da SQL Server 2012SQL Server 2012, le allocazioni di pagine singole, le allocazioni di più pagine e le allocazioni CLR sono tutte consolidate in un allocatore di pagine di "qualsiasi dimensione" e sono incluse nei limiti di memoria controllati dalle opzioni di configurazione max server memory (MB) e min server memory (MB).Starting with SQL Server 2012SQL Server 2012, Single-lage allocations, Multi-Page allocations and CLR allocations are all consolidated into a "Any size" Page Allocator, and it's included in memory limits that are controlled by max server memory (MB) and min server memory (MB) configuration options. Questa modifica introduce capacità di ridimensionamento più accurate per tutti i requisiti di memoria che passano attraverso lo strumento di gestione della memoria di SQL ServerSQL Server.This change provided a more accurate sizing ability for all memory requirements that go through the SQL ServerSQL Server memory manager.

Importante

Controllare con attenzione le configurazioni correnti di max server memory (MB) e min server memory (MB) dopo l'aggiornamento alle versioni da SQL Server 2012SQL Server 2012 a SQL Server 2017SQL Server 2017.Carefully review your current max server memory (MB) and min server memory (MB) configurations after you upgrade to SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017. Infatti, a partire da SQL Server 2012SQL Server 2012, queste configurazioni ora includono e tengono conto di più allocazioni di memoria rispetto alle versioni precedenti.This is because starting in SQL Server 2012SQL Server 2012, such configurations now include and account for more memory allocations compared to earlier versions. Queste modifiche sono valide sia per le versioni a 32 bit che a 64 bit di SQL Server 2012SQL Server 2012 e SQL Server 2014SQL Server 2014, sia per le versioni da SQL Server 2016SQL Server 2016 a SQL Server 2017SQL Server 2017 a 64 bit.These changes apply to both 32-bit and 64-bit versions of SQL Server 2012SQL Server 2012 and SQL Server 2014SQL Server 2014, and 64-bit versions of SQL Server 2016SQL Server 2016 through SQL Server 2017SQL Server 2017.

La tabella seguente indica se un tipo specifico di allocazione di memoria è controllato dalle opzioni di configurazione max server memory (MB) e min server memory (MB):The following table indicates whether a specific type of memory allocation is controlled by the max server memory (MB) and min server memory (MB) configuration options:

Tipo di allocazione di memoriaType of memory allocation SQL Server 2005SQL Server 2005, SQL Server 2008SQL Server 2008 e SQL Server 2008 R2SQL Server 2008 R2, SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2 A partire da SQL Server 2012SQL Server 2012Starting with SQL Server 2012SQL Server 2012
Allocazioni di singole pagineSingle-page allocations Yes Sì, consolidata in allocazioni di pagine "di qualsiasi dimensione"Yes, consolidated into "any size" page allocations
Allocazioni di più pagineMulti-page allocations NoNo Sì, consolidata in allocazioni di pagine "di qualsiasi dimensione"Yes, consolidated into "any size" page allocations
Allocazioni CLRCLR allocations NoNo Yes
Memoria stack di threadThread stacks memory NoNo NoNo
Allocazioni dirette da WindowsDirect allocations from Windows NoNo NoNo

A partire da SQL Server 2012SQL Server 2012, SQL ServerSQL Server potrebbe allocare altra memoria rispetto al valore specificato nell'impostazione max server memory.Starting with SQL Server 2012SQL Server 2012, SQL ServerSQL Server might allocate more memory than the value specified in the max server memory setting. Questo comportamento può verificarsi quando il valore Memoria totale server (KB) ha già raggiunto il valore dell'impostazione Memoria prevista server (KB) (come specificato da max server memory).This behavior may occur when the Total Server Memory (KB) value has already reached the Target Server Memory (KB) setting (as specified by max server memory). Se la memoria contigua disponibile è insufficiente per soddisfare le richieste di più pagine di memoria (più di 8 KB) a causa della frammentazione della memoria, SQL ServerSQL Server può eseguire l'overcommit anziché rifiutare la richiesta di memoria.If there is insufficient contiguous free memory to meet the demand of multi-page memory requests (more than 8 KB) because of memory fragmentation, SQL ServerSQL Server can perform over-commitment instead of rejecting the memory request.

Non appena viene eseguita questa allocazione, l'attività in background Monitoraggio risorse inizia a segnalare a tutti i consumer di memoria di rilasciare la memoria allocata e tenta di portare il valore Memoria totale server (KB) al di sotto del valore specificato per Memoria prevista server (KB).As soon as this allocation is performed, the Resource Monitor background task starts to signal all memory consumers to release the allocated memory, and tries to bring the Total Server Memory (KB) value below the Target Server Memory (KB) specification. Pertanto, l'utilizzo della memoria di SQL ServerSQL Server potrebbe entro breve superare l'impostazione max server memory.Therefore, SQL ServerSQL Server memory usage could briefly exceed the max server memory setting. In questo caso, la lettura del contatore delle prestazioni Memoria totale server (KB) risulterà superiore alle impostazioni max server memory e Memoria prevista server (KB).In this situation, the Total Server Memory (KB) performance counter reading will exceed the max server memory and Target Server Memory (KB) settings.

Questo comportamento viene in genere osservato durante le operazioni seguenti:This behavior is typically observed during the following operations:

  • Query su indici Columnstore di grandi dimensioni.Large Columnstore index queries.
  • Compilazioni o ricompilazioni di indici ColumnStore che usano grandi quantità di memoria per eseguire operazioni di hash e ordinamento.Columnstore index (re)builds, which use large volumes of memory to perform Hash and Sort operations.
  • Operazioni di backup che richiedono buffer di memoria di grandi dimensioni.Backup operations that require large memory buffers.
  • Operazioni di traccia che devono archiviare parametri di input di grandi dimensioni.Tracing operations that have to store large input parameters.

Modifiche apportate a "memory_to_reserve" a partire da SQL Server 2012SQL Server 2012Changes to "memory_to_reserve" starting with SQL Server 2012SQL Server 2012

Nelle versioni precedenti di SQL Server ( SQL Server 2005SQL Server 2005, SQL Server 2008SQL Server 2008 e SQL Server 2008 R2SQL Server 2008 R2), lo strumento di gestione della memoria di SQL ServerSQL Server riserva parte dello spazio indirizzi virtuali dl processo per l'allocatore di più pagine, l'allocatore CLR, le allocazioni di memoria per gli stack di thread nel processo di SQL Server e le allocazioni di Windows dirette.In earlier versions of SQL Server ( SQL Server 2005SQL Server 2005, SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2), the SQL ServerSQL Server memory manager set aside a part of the process virtual address space (VAS) for use by the Multi-Page Allocator (MPA), CLR Allocator, memory allocations for thread stacks in the SQL Server process, and Direct Windows allocations (DWA). Questa parte dello spazio indirizzi virtuali è nota anche come area MemToLeave o "pool non di buffer".This part of the virtual address space is also known as "Mem-To-Leave" or "non-Buffer Pool" region.

Lo spazio indirizzi virtuali riservato per queste allocazioni varia a seconda dell'opzione di configurazione memory_to_reserve.The virtual address space that is reserved for these allocations is determined by the memory_to_reserve configuration option. Il valore predefinito usato da SQL ServerSQL Server è 256 MB.The default value that SQL ServerSQL Server uses is 256 MB. Per sostituire il valore predefinito, usare il parametro di avvio SQL ServerSQL Server -g.To override the default value, use the SQL ServerSQL Server -g startup parameter. Fare riferimento alla pagina della documentazione Opzioni di avvio del servizio del motore di database per informazioni sul parametro di avvio -g.Refer to the documentation page on Database Engine Service Startup Options for information on the -g startup parameter.

Dato che a partire da SQL Server 2012SQL Server 2012 il nuovo allocatore di pagine "di qualsiasi dimensione" gestisce anche le allocazioni di dimensioni superiori a 8 KB, il valore memory_to_reserve non include le allocazioni di più pagine.Because starting with SQL Server 2012SQL Server 2012, the new "any size" page allocator also handles allocations greater than 8 KB, the memory_to_reserve value does not include the multi-page allocations. Ad eccezione di questa modifica, non vi sono altre novità per questa opzione di configurazione.Except for this change, everything else remains the same with this configuration option.

La tabella seguente indica se un tipo specifico di allocazione di memoria rientra nell'area memory_to_reserve dello spazio indirizzi virtuali per il processo SQL ServerSQL Server:The following table indicates whether a specific type of memory allocation falls into the memory_to_reserve region of the virtual address space for the SQL ServerSQL Server process:

Tipo di allocazione di memoriaType of memory allocation SQL Server 2005SQL Server 2005, SQL Server 2008SQL Server 2008 e SQL Server 2008 R2SQL Server 2008 R2, SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2 A partire da SQL Server 2012SQL Server 2012Starting with SQL Server 2012SQL Server 2012
Allocazioni di singole pagineSingle-page allocations NoNo No, consolidata in allocazioni di pagine "di qualsiasi dimensione"No, consolidated into "any size" page allocations
Allocazioni di più pagineMulti-page allocations Yes No, consolidata in allocazioni di pagine "di qualsiasi dimensione"No, consolidated into "any size" page allocations
Allocazioni CLRCLR allocations Yes Yes
Memoria stack di threadThread stacks memory Yes Yes
Allocazioni dirette da WindowsDirect allocations from Windows Yes Yes

Gestione della memoria dinamicaDynamic Memory Management

Il comportamento predefinito per la gestione della memoria del Motore di database di SQL ServerSQL Server Database Engine di SQL ServerSQL Server consiste nell'acquisire la quantità di memoria necessaria senza causare insufficienza di memoria nel sistema.The default memory management behavior of the SQL ServerSQL Server Motore di database di SQL ServerSQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. In Motore di database di SQL ServerSQL Server Database Engine questo comportamento è reso possibile tramite l'utilizzo delle API di notifica della memoria di Microsoft Windows.The Motore di database di SQL ServerSQL Server Database Engine does this by using the Memory Notification APIs in Microsoft Windows.

Quando SQL ServerSQL Server utilizza la memoria in modo dinamico, esegue query periodiche sul sistema per determinare la quantità di memoria libera disponibile.When SQL ServerSQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Il mantenimento di tale memoria libera impedisce il paging del sistema operativo.Maintaining this free memory prevents the operating system (OS) from paging. Se è disponibile una quantità minore di memoria libera, SQL ServerSQL Server rilascia memoria al sistema operativo.If less memory is free, SQL ServerSQL Server releases memory to the OS. Se è disponibile una quantità maggiore di memoria libera, in SQL ServerSQL Server può essere allocata più memoria.If more memory is free, SQL ServerSQL Server may allocate more memory. SQL ServerSQL Server aggiunge memoria solo se richiesto dal relativo carico di lavoro. In un server non operativo non vengono aumentate le dimensioni del proprio spazio degli indirizzi virtuali. adds memory only when its workload requires more memory; a server at rest does not increase the size of its virtual address space.

Max server memory controlla l'allocazione di memoria di SQL ServerSQL Server, la memoria per la compilazione, tutte le cache (incluso il pool di buffer), le concessioni di memoria per l'esecuzione di query, la memoria per la gestione blocchi e la memoria CLR1 (in particolare i clerk di memoria presenti in sys.dm_os_memory_clerks).Max server memory controls the SQL ServerSQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks).

1 La memoria CLR viene gestita nelle allocazioni max_server_memory a partire da SQL Server 2012SQL Server 2012.1 CLR memory is managed under max_server_memory allocations starting with SQL Server 2012SQL Server 2012.

La query seguente restituisce le informazioni sulla memoria attualmente allocata:The following query returns information about currently allocated memory:

SELECT 
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage, 
    process_physical_memory_low AS sql_process_physical_memory_low, 
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;  

La memoria per gli stack di thread1, CLR2, i file DLL di procedure estese, i provider OLE DB a cui fanno riferimento query distribuite, gli oggetti di automazione con riferimenti nelle istruzioni Transact-SQLTransact-SQL e qualsiasi allocazione di memoria eseguita da una DLL non SQL ServerSQL Server non sono controllate da max server memory.Memory for thread stacks1, CLR2, extended procedure .dll files, the OLE DB providers referenced by distributed queries, automation objects referenced in Transact-SQLTransact-SQL statements, and any memory allocated by a non SQL ServerSQL Server DLL are not controlled by max server memory.

1 Fare riferimento alla pagina della documentazione Configurare l'opzione di configurazione del server max worker threads per informazioni sui thread di lavoro predefiniti calcolati per un determinato numero di CPU per cui è stata impostata l'affinità nell'host corrente.1 Refer to the documentation page on how to Configure the max worker threads Server Configuration Option, for information on the calculated default worker threads for a given number of affinitized CPUs in the current host. Le dimensioni di stack di SQL ServerSQL Server sono le seguenti: SQL ServerSQL Server stack sizes are as follows:

Architettura di SQL ServerSQL Server Architecture Architettura del sistema operativoOS Architecture Dimensioni dello stackStack Size
x86 (32 bit)x86 (32-bit) x86 (32 bit)x86 (32-bit) 512 KB512 KB
x86 (32 bit)x86 (32-bit) x64 (64 bit)x64 (64-bit) 768 KB768 KB
x64 (64 bit)x64 (64-bit) x64 (64 bit)x64 (64-bit) 2048 KB2048 KB
IA64 (Itanium)IA64 (Itanium) IA64 (Itanium)IA64 (Itanium) 4096 KB4096 KB

2 La memoria CLR viene gestita nelle allocazioni max_server_memory a partire da SQL Server 2012SQL Server 2012.2 CLR memory is managed under max_server_memory allocations starting with SQL Server 2012SQL Server 2012.

SQL ServerSQL Server usa l'API di notifica di memoria QueryMemoryResourceNotification per determinare i casi in cui è possibile allocare e rilasciare memoria con lo strumento di gestione della memoria di SQL ServerSQL Server. uses the memory notification API QueryMemoryResourceNotification to determine when the SQL ServerSQL Server Memory Manager may allocate memory and release memory.

All'avvio di SQL ServerSQL Server , viene calcolata la dimensione dello spazio degli indirizzi virtuali per il pool di buffer in base a parametri come la quantità di memoria fisica nel sistema, il numero di thread del server e vari parametri di avvio.When SQL ServerSQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. In SQL ServerSQL Server viene riservata la quantità calcolata di spazio degli indirizzi virtuali del processo per il pool di buffer, ma viene acquisita solo la quantità di memoria fisica necessaria (ovvero ne viene eseguito il commit) per il carico corrente. SQL ServerSQL Server reserves the computed amount of its process virtual address space for the buffer pool, but it acquires (commits) only the required amount of physical memory for the current load.

L'istanza continua quindi ad acquisire la memoria necessaria per supportare il carico di lavoro.The instance then continues to acquire memory as needed to support the workload. Man mano che altri utenti si connettono ed eseguono query, in SQL ServerSQL Server viene acquisita ulteriore memoria fisica su richiesta.As more users connect and run queries, SQL ServerSQL Server acquires the additional physical memory on demand. La memoria fisica continua a essere acquisita da un'istanza di SQL ServerSQL Server fino a quando viene raggiunto il limite di allocazione definito dall'opzione Memoria massima del server o fino a quando in Windows viene indicato che non è più disponibile memoria in eccesso. La memoria viene liberata quando viene superato il valore dell'impostazione Memoria minima del server e in Windows viene indicata un'insufficienza di memoria disponibile.A SQL ServerSQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and Windows indicates that there is a shortage of free memory.

L'avvio di altre applicazioni in un computer in cui viene eseguita un'istanza di SQL ServerSQL Servercomporta l'utilizzo di ulteriore memoria e la quantità di memoria fisica disponibile scende al di sotto del valore di destinazione di SQL ServerSQL Server .As other applications are started on a computer running an instance of SQL ServerSQL Server, they consume memory and the amount of free physical memory drops below the SQL ServerSQL Server target. Nell'istanza di SQL ServerSQL Server l'utilizzo della memoria viene regolato automaticamente.The instance of SQL ServerSQL Server adjusts its memory consumption. Se un'altra applicazione viene arrestata e viene resa disponibile altra memoria, l'istanza di SQL ServerSQL Server aumenta le dimensioni della propria allocazione di memoria.If another application is stopped and more memory becomes available, the instance of SQL ServerSQL Server increases the size of its memory allocation. SQL ServerSQL Server può liberare e acquisire diversi megabyte di memoria al secondo, in modo che possa adattarsi rapidamente alle modifiche dell'allocazione della memoria. can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.

Effetti delle opzioni min server memory e max server memoryEffects of min and max server memory

Le opzioni di configurazione min server memory e max server memory stabiliscono il limite massimo e minimo della quantità di memoria usata dal pool di buffer e in altre cache del motore di database di SQL ServerSQL Server.The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool and other caches of the SQL ServerSQL Server Database Engine. Il pool di buffer non acquisisce immediatamente la quantità di memoria specificata in Memoria minima del server.The buffer pool does not immediately acquire the amount of memory specified in min server memory. ma solo la memoria necessaria per l'inizializzazione.The buffer pool starts with only the memory required to initialize. Con l'aumentare del carico di lavoro da gestire, Motore di database di SQL ServerSQL Server Database Engine continua ad acquisire la memoria necessaria per supportare il carico di lavoroAs the Motore di database di SQL ServerSQL Server Database Engine workload increases, it keeps acquiring the memory required to support the workload. Il pool di buffer non libera la memoria acquisita fino a quando non viene raggiunta la quantità specificata in Memoria minima del server.The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory. Quando viene raggiunto il limite impostato in Memoria minima del server, il pool di buffer usa quindi l'algoritmo standard per acquisire e liberare memoria nella misura necessaria.Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. L'unica differenza è rappresentata dal fatto che nel pool di buffer la quantità di memoria allocata non scende mai al di sotto del livello specificato in Memoria minima del server, né viene mai acquisita una quantità di memoria superiore al livello specificato Memoria massima del server.The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory.

Nota

SQL ServerSQL Server come processo acquisisce più memoria rispetto a quella specificata dall'opzione Memoria massima del server. as a process acquires more memory than specified by max server memory option. I componenti interni ed esterni possono allocare memoria al di fuori del pool di buffer, richiedendo ulteriore memoria, ma la memoria allocata al pool di buffer in genere rappresenta ancora la parte di memoria maggiore usata da SQL ServerSQL Server.Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually still represents the largest portion of memory consumed by SQL ServerSQL Server.

La quantità di memoria acquisita da Motore di database di SQL ServerSQL Server Database Engine dipende interamente dal carico di lavoro assegnato all'istanza.The amount of memory acquired by the Motore di database di SQL ServerSQL Server Database Engine is entirely dependent on the workload placed on the instance. È possibile che per un'istanza di SQL ServerSQL Server che elabora un numero ridotto di richieste, la quantità di memoria allocata rimanga sempre inferiore al valore di min server memory.A SQL ServerSQL Server instance that is not processing many requests may never reach min server memory.

Se si specifica lo stesso valore per min server memory e max server memory, quando la memoria allocata al Motore di database di SQL ServerSQL Server Database Engine raggiunge tale valore, il Motore di database di SQL ServerSQL Server Database Engine smette di liberare e acquisire dinamicamente memoria per il pool di buffer.If the same value is specified for both min server memory and max server memory, then once the memory allocated to the Motore di database di SQL ServerSQL Server Database Engine reaches that value, the Motore di database di SQL ServerSQL Server Database Engine stops dynamically freeing and acquiring memory for the buffer pool.

Se un'istanza di SQL ServerSQL Server è in esecuzione in un computer in cui viene spesso avviata o arrestata l'esecuzione di altre applicazioni, è possibile che l'allocazione e la deallocazione della memoria da parte dell'istanza di SQL ServerSQL Server rallenti l'avvio delle altre applicazioni.If an instance of SQL ServerSQL Server is running on a computer where other applications are frequently stopped or started, the allocation and deallocation of memory by the instance of SQL ServerSQL Server may slow the startup times of other applications. Se SQL ServerSQL Server è solo una delle numerose applicazioni server in esecuzione in un singolo computer, può inoltre essere necessario che l'amministratore di sistema controlli la quantità di memoria allocata a SQL ServerSQL Server.Also, if SQL ServerSQL Server is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to SQL ServerSQL Server. In questi casi, è possibile usare le opzioni Memoria minima del server e Memoria massima del server per controllare la quantità di memoria che può essere impiegata da SQL ServerSQL Server .In these cases, you can use the min server memory and max server memory options to control how much memory SQL ServerSQL Server can use. I valori delle opzioni min server memory e max server memory vengono specificati in megabyte.The min server memory and max server memory options are specified in megabytes. Per altre informazioni, vedere Opzioni di configurazione del server Server Memory.For more information, see Server Memory Configuration Options.

Memoria usata dalle specifiche degli oggetti di SQL ServerSQL ServerMemory used by SQL ServerSQL Server objects specifications

Nella seguente tabella sono indicate le quantità di memoria approssimative usate da diversi oggetti in SQL ServerSQL Server.The following list describes the approximate amount of memory used by different objects in SQL ServerSQL Server. I valori riportati sono stimati e possono variare a seconda dell'ambiente e delle modalità di creazione degli oggetti:The amounts listed are estimates and can vary depending on the environment and how objects are created:

  • Blocco (gestito da Gestione blocchi): 64 byte + 32 byte per proprietarioLock (as maintained by the Lock Manager): 64 bytes + 32 bytes per owner
  • Connessione utente: circa (3 * dimensioni_pacchetto_rete + 94 kb)User connection: Approximately (3 * network_packet_size + 94 kb)

dimensioni_pacchetto_rete indica le dimensioni dei pacchetti TDS (Tabular Data Scheme) usati per le comunicazioni tra le applicazioni e il motore di database di SQL ServerSQL Server.The network packet size is the size of the tabular data scheme (TDS) packets that are used to communicate between applications and the SQL ServerSQL Server Database Engine. La dimensione predefinita del pacchetto è 4 KB e viene controllata dall'opzione di configurazione delle dimensioni del pacchetto di rete.The default packet size is 4 KB, and is controlled by the network packet size configuration option.

Quando è abilitato MARS (Multiple Active Result Set), la connessione utente corrisponde a circa (3 + 3 * num_logical_connections) * network_packet_size + 94 KB.When multiple active result sets (MARS) are enabled, the user connection is approximately (3 + 3 * num_logical_connections) * network_packet_size + 94 KB

Gestione del bufferBuffer management

Lo scopo principale di un database di SQL ServerSQL Server è l'archiviazione e il recupero dei dati. L'esecuzione di una quantità elevata di operazioni di I/O su disco è pertanto una caratteristica fondamentale del motore di database.The primary purpose of a SQL ServerSQL Server database is to store and retrieve data, so intensive disk I/O is a core characteristic of the Database Engine. Poiché le operazioni di I/O nel disco possono utilizzare molte risorse e richiedere un tempo relativamente lungo per il completamento, in SQL ServerSQL Server viene data grande importanza all'efficienza dell'I/O.And because disk I/O operations can consume many resources and take a relatively long time to finish, SQL ServerSQL Server focuses on making I/O highly efficient. La gestione del buffer è un elemento chiave per il raggiungimento di tale efficienza.Buffer management is a key component in achieving this efficiency. Il componente di gestione del buffer è costituito da due meccanismi, ovvero Gestione buffer che consente di accedere alle pagine del database e aggiornarle, e la cache del buffer, detta anche pool di buffer, che consente di ridurre le operazioni di I/O del file di database.The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer cache (also called the buffer pool), to reduce database file I/O.

Funzionamento di Gestione bufferHow buffer management works

Un buffer è una pagina da 8 KB in memoria, ovvero delle stesse dimensioni di una pagina di dati o di indice.A buffer is an 8 KB page in memory, the same size as a data or index page. La cache del buffer è quindi suddivisa in pagine da 8 KB.Thus, the buffer cache is divided into 8 KB pages. Gestione buffer consente di gestire le funzioni per la lettura delle pagine di dati o di indice dai file su disco del database nella cache buffer e la riscrittura sul disco delle pagine modificate.The buffer manager manages the functions for reading data or index pages from the database disk files into the buffer cache and writing modified pages back to disk. Una pagina rimane nella cache del buffer fino a quando per Gestione buffer non è necessaria l'area del buffer per leggere un maggior numero di dati.A page remains in the buffer cache until the buffer manager needs the buffer area to read in more data. I dati vengono riscritti sul disco solo se vengono modificati.Data is written back to disk only if it is modified. I dati nella cache del buffer possono essere modificati più volte prima di venire riscritti sul disco.Data in the buffer cache can be modified multiple times before being written back to disk. Per altre informazioni, vedere Lettura di pagine e Scrittura di pagine.For more information, see Reading Pages and Writing Pages.

All'avvio di SQL ServerSQL Server , viene calcolata la dimensione dello spazio degli indirizzi virtuali per la cache del buffer in base a parametri come la quantità di memoria fisica nel sistema, il numero massimo di thread del server configurati e vari parametri di avvio.When SQL ServerSQL Server starts, it computes the size of virtual address space for the buffer cache based on a number of parameters such as the amount of physical memory on the system, the configured number of maximum server threads, and various startup parameters. In SQL ServerSQL Server viene riservata la quantità calcolata di spazio degli indirizzi virtuali del processo per la cache del buffer (chiamata destinazione di memoria), ma viene acquisita solo la quantità di memoria fisica necessaria (ovvero ne viene eseguito il commit) per il carico corrente. SQL ServerSQL Server reserves this computed amount of its process virtual address space (called the memory target) for the buffer cache, but it acquires (commits) only the required amount of physical memory for the current load. È possibile eseguire query sulle colonne bpool_commit_target e bpool_committed columns nella vista del catalogo sys.dm_os_sys_info per restituire il numero di pagine riservate come destinazione di memoria e il numero di pagine su cui attualmente viene eseguito il commit nella cache del buffer, rispettivamente.You can query the bpool_commit_target and bpool_committed columns in the sys.dm_os_sys_info catalog view to return the number of pages reserved as the memory target and the number of pages currently committed in the buffer cache, respectively.

L'intervallo tra l'avvio di SQL ServerSQL Server e il momento in cui alla cache del buffer viene allocata la memoria massima è detto processo di avvio.The interval between SQL ServerSQL Server startup and when the buffer cache obtains its memory target is called ramp-up. Durante questo intervallo le richieste di lettura riempiono i buffer in base alle esigenze.During this time, read requests fill the buffers as needed. Ad esempio, una richiesta di lettura di una singola pagina da 8 KB riempie una singola pagina del buffer.For example, a single 8 KB page read request fills a single buffer page. Questo significa che il processo di avvio dipende dal numero e dal tipo di richieste del client.This means the ramp-up depends on the number and type of client requests. Il processo di avvio viene reso più rapido mediante la trasformazione delle richieste di lettura di pagina singola in richieste di otto pagine allineate, ovvero un extent.Ramp-up is expedited by transforming single page read requests into aligned eight page requests (making up one extent). Ciò consente un completamento del processo di avvio molto più rapido, in particolare nei computer con molta memoria.This allows the ramp-up to finish much faster, especially on machines with a lot of memory. Per altre informazioni su pagine ed extent, vedere Guida all'architettura di pagine ed extent.For more information about pages and extents, refer to Pages and Extents Architecture Guide.

Gestione buffer utilizza la maggior parte della memoria nel processo di SQL ServerSQL Server e coopera pertanto con Gestione memoria per consentire agli altri componenti di utilizzare i relativi buffer.Because the buffer manager uses most of the memory in the SQL ServerSQL Server process, it cooperates with the memory manager to allow other components to use its buffers. Gestione buffer interagisce essenzialmente con i componenti seguenti:The buffer manager interacts primarily with the following components:

  • Strumento di gestione delle risorse, per controllare l'utilizzo globale della memoria e, nelle piattaforme a 32 bit, l'utilizzo dello spazio degli indirizzi.Resource manager to control overall memory usage and, in 32-bit platforms, to control address space usage.
  • Gestione database e il sistema operativo di SQL ServerSQL Server (SQLOS) per operazioni di I/O di file di basso livello.Database manager and the SQL ServerSQL Server Operating System (SQLOS) for low-level file I/O operations.
  • Strumento di gestione dei log per la registrazione write-ahead.Log manager for write-ahead logging.

Funzionalità supportateSupported Features

Gestione buffer supporta le caratteristiche seguenti:The buffer manager supports the following features:

  • Gestione buffer supporta NUMA (Non-Uniform Memory Access).The buffer manager is non-uniform memory access (NUMA) aware. Le pagine della cache buffer vengono distribuite tra i nodi hardware NUMA, consentendo a un thread di accedere a una pagina del buffer allocata nel nodo NUMA locale anziché dalla memoria esterna.Buffer cache pages are distributed across hardware NUMA nodes, which allows a thread to access a buffer page that is allocated on the local NUMA node rather than from foreign memory.
  • Gestione buffer supporta l'aggiunta di memoria a caldo, consentendo agli utenti di aggiungere memoria fisica senza riavviare il server.The buffer manager supports Hot Add Memory, which allows users to add physical memory without restarting the server.
  • Gestione buffer supporta pagine di grandi dimensioni su piattaforme a 64 bit.The buffer manager supports large pages on 64-bit platforms. Le dimensioni delle pagine sono specifiche della versione di Windows utilizzata.The page size is specific to the version of Windows.

    Nota

    Prima di SQL Server 2012SQL Server 2012, per abilitare le pagine di grandi dimensioni in SQL ServerSQL Server è richiesto il flag di traccia 834.Prior to SQL Server 2012SQL Server 2012, enabling large pages in SQL ServerSQL Server requires trace flag 834.

  • In Gestione buffer sono disponibili strumenti di diagnostica aggiuntivi esposti tramite DMV.The buffer manager provides additional diagnostics that are exposed through dynamic management views. È possibile utilizzare queste viste per monitorare varie risorse del sistema operativo specifiche di SQL ServerSQL Server.You can use these views to monitor a variety of operating system resources that are specific to SQL ServerSQL Server. Ad esempio, è possibile usare la vista sys.dm_os_buffer_descriptors per monitorare le pagine nella cache del buffer.For example, you can use the sys.dm_os_buffer_descriptors view to monitor the pages in the buffer cache.

I/O su discoDisk I/O

Gestione buffer esegue solo letture e scritture sul database.The buffer manager only performs reads and writes to the database. Altre operazioni su file e database, ad esempio apertura, chiusura, estensione e compattazione vengono eseguite dai componenti del gestore database e di File Manager.Other file and database operations such as open, close, extend, and shrink are performed by the database manager and file manager components.

Le operazioni di I/O su disco eseguite da Gestione buffer hanno le caratteristiche seguenti:Disk I/O operations by the buffer manager have the following characteristics:

  • Tutti gli I/O vengono eseguiti in modo asincrono. In questo modo, il thread che esegue la chiamata può continuare l'elaborazione mentre l'operazione di I/O viene eseguita in background.All I/Os are performed asynchronously, which allows the calling thread to continue processing while the I/O operation takes place in the background.
  • Tutti gli I/O vengono eseguiti nei thread che eseguono la chiamata a meno che non sia in uso l'opzione affinity I/O.All I/Os are issued in the calling threads unless the affinity I/O option is in use. L'opzione affinity I/O mask associa l'I/O su disco di SQL ServerSQL Server a un subset di CPU specificato.The affinity I/O mask option binds SQL ServerSQL Server disk I/O to a specified subset of CPUs. Negli ambienti SQL ServerSQL Server di fascia alta con elaborazione delle transazioni online (OLTP), questa estensione può migliorare le prestazioni dei thread di SQL ServerSQL Server che generano operazioni di I/O.In high-end SQL ServerSQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL ServerSQL Server threads issuing I/Os.
  • Gli I/O di più pagine vengono eseguiti con un I/O non sequenziale, che consente il trasferimento di dati da e verso aree di memoria non contigue.Multiple page I/Os are accomplished with scatter-gather I/O, which allows data to be transferred into or out of noncontiguous areas of memory. Questo significa che SQL ServerSQL Server può riempire o scaricare rapidamente la cache buffer evitando più richieste di I/O fisici.This means that SQL ServerSQL Server can quickly fill or flush the buffer cache while avoiding multiple physical I/O requests.

Richieste di I/O lunghiLong I/O requests

Gestione buffer segnala qualsiasi richiesta di I/O che rimane in attesa per almeno 15 secondi.The buffer manager reports on any I/O request that has been outstanding for at least 15 seconds. In questo modo, l'amministratore di sistema può distinguere tra problemi di SQL ServerSQL Server e problemi del sottosistema I/O.This helps the system administrator distinguish between SQL ServerSQL Server problems and I/O subsystem problems. Il messaggio di errore 833 viene restituito e visualizzato nel log degli errori di SQL ServerSQL Server nel modo seguente:Error message 833 is reported and appears in the SQL ServerSQL Server error log as follows:

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

Un I/O lungo può essere un'operazione di lettura o scrittura. Questa indicazione non viene attualmente inclusa nel messaggio.A long I/O may be either a read or a write; it is not currently indicated in the message. I messaggi relativi a I/O lunghi sono avvisi e non messaggi di errore.Long-I/O messages are warnings, not errors. Non indicano problemi con SQL ServerSQL Server ma con il sistema di I/O sottostante.They do not indicate problems with SQL ServerSQL Server but with the underlying I/O system. ma vengono restituiti per consentire all'amministratore di sistema di individuare in modo più rapido la causa dei tempi di risposta lenti di SQL ServerSQL Server , nonché distinguere i problemi esterni al controllo di SQL ServerSQL Server.The messages are reported to help the system administrator find the cause of poor SQL ServerSQL Server response times more quickly, and to distinguish problems that are outside the control of SQL ServerSQL Server. I messaggi relativi a I/O lunghi non richiedono quindi alcuna azione. È comunque consigliabile che l'amministratore di sistema verifichi il motivo per il quale per la richiesta di I/O è stato necessario un tempo così prolungato e se esso è giustificato.As such, they do not require any action, but the system administrator should investigate why the I/O request took so long, and whether the time is justifiable.

Cause delle richieste di I/O lunghiCauses of Long-I/O Requests

Un messaggio di I/O lungo indica che un I/O è bloccato definitivamente e non verrà mai completato (I/O perso) o semplicemente che non è stato ancora completato.A long-I/O message may indicate that an I/O is permanently blocked and will never complete (known as lost I/O), or merely that it just has not completed yet. Non è possibile capire dal messaggio di quale scenario si tratta, sebbene un I/O perso sia spesso causa di un timeout di latch.It is not possible to tell from the message which scenario is the case, although a lost I/O will often lead to a latch timeout.

Gli I/O lunghi indicano spesso un carico di lavoro di SQL ServerSQL Server troppo intenso per il sottosistema disco.Long I/Os often indicate a SQL ServerSQL Server workload that is too intense for the disk subsystem. Le situazioni seguenti possono indicare un sottosistema disco non adeguato:An inadequate disk subsystem may be indicated when:

  • Più messaggi di I/O lunghi vengono visualizzati nel log degli errori durante un carico di lavoro elevato di SQL ServerSQL Server .Multiple long I/O messages appear in the error log during a heavy SQL ServerSQL Server workload.
  • I contatori Perfmon indicano latenze prolungate del disco, lunghe code del disco o nessun tempo di inattività del disco.Perfmon counters show long disk latencies, long disk queues, or no disk idle time.

Gli I/O lunghi possono inoltre essere causati da un componente nel percorso di I/O (ad esempio, un driver, un controller o un firmware) che ritarda continuamente la risposta a una richiesta di I/O precedente privilegiando richieste più recenti, più vicine alla posizione corrente della testina.Long I/Os may also be caused by a component in the I/O path (for example, a driver, controller, or firmware) continually postponing servicing an old I/O request in favor of servicing newer requests that are closer to the current position of the disk head. La tecnica comune di elaborazione delle richieste che dà priorità alle richieste più vicine alla posizione corrente della testina di lettura/scrittura è detta "accodamento dei comandi nativi".The common technique of processing requests in priority based upon which ones are closest to the current position of the read/write head is known as "elevator seeking." Questo può essere difficile da verificare mediante lo strumento Monitor di sistema di Windows (PERFMON.EXE) in quanto la maggioranza degli I/O vengono gestiti immediatamente.This may be difficult to corroborate with the Windows System Monitor (PERFMON.EXE) tool because most I/Os are being serviced promptly. Le richieste di I/O lunghi possono essere ulteriormente complicate da carichi di lavoro che eseguono grandi quantità di I/O sequenziali, ad esempio backup e ripristino, analisi delle tabelle, ordinamento, creazione di indici, caricamenti bulk e azzeramento dei file.Long I/O requests can be aggravated by workloads that perform large amounts of sequential I/O, such as backup and restore, table scans, sorting, creating indexes, bulk loads, and zeroing out files.

Gli I/O lunghi isolati apparentemente non correlati a una delle condizioni precedenti possono essere causati da un problema hardware o di driver.Isolated long I/Os that do not appear related to any of the previous conditions may be caused by a hardware or driver problem. Il log eventi di sistema può contenere un evento correlato che consente di individuare il problema.The system event log may contain a related event that helps to diagnose the problem.

Rilevamento degli erroriError Detection

Per le pagine di database sono disponibili due meccanismi facoltativi, ovvero la protezione delle pagine incomplete e la protezione dei checksum, che consentono di assicurare l'integrità della pagina dal momento in cui viene scritta sul disco fino a quando viene letta di nuovo.Database pages can use one of two optional mechanisms that help insure the integrity of the page from the time it is written to disk until it is read again: torn page protection and checksum protection. Questi meccanismi offrono una modalità indipendente di verifica della correttezza non solo dell'archiviazione dei dati, ma anche di componenti hardware quali controller, driver, cavi e perfino del sistema operativo.These mechanisms allow an independent method of verifying the correctness of not only the data storage, but hardware components such as controllers, drivers, cables, and even the operating system. La protezione viene aggiunta alla pagina immediatamente prima della scrittura sul disco e viene verificata dopo la lettura della pagina dal disco.The protection is added to the page just before writing it to disk, and verified after it is read from disk.

SQL ServerSQL Server esegue quattro tentativi per qualsiasi operazione di lettura non riuscita a causa di un errore di checksum, di pagina incompleta o di I/O. will retry any read that fails with a checksum, torn page, or other I/O error four times. Se la lettura viene completata correttamente durante uno di questi tentativi, viene scritto un messaggio nel log degli errori e l'esecuzione del comando che ha attivato la lettura continua.If the read is successful in any one of the retry attempts, a message will be written to the error log and the command that triggered the read will continue. Se tutti i tentativi hanno esito negativo, il comando viene interrotto con il messaggio di errore 824.If the retry attempts fail, the command will fail with error message 824.

Il tipo di protezione di pagina utilizzato è un attributo del database che contiene la pagina.The kind of page protection used is an attribute of the database containing the page. La protezione dei checksum è la protezione predefinita per i database creati in SQL Server 2005SQL Server 2005 e versioni successive.Checksum protection is the default protection for databases created in SQL Server 2005SQL Server 2005 and later. Il meccanismo di protezione di pagina viene specificato al momento della creazione del database e può essere modificato usando ALTER DATABASE SET.The page protection mechanism is specified at database creation time, and may be altered by using ALTER DATABASE SET. È possibile determinare l'impostazione corrente per la protezione di pagina eseguendo una query nella colonna page_verify_option della vista del catalogo sys.databases o nella proprietà IsTornPageDetectionEnabled della funzione DATABASEPROPERTYEX.You can determine the current page protection setting by querying the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.

Nota

Se l'impostazione relativa alla protezione di pagina viene modificata, la nuova impostazione non viene immediatamente estesa all'intero database.If the page protection setting is changed, the new setting does not immediately affect the entire database. Il livello di protezione del database corrente viene infatti esteso alle pagine a ogni successiva scrittura.Instead, pages adopt the current protection level of the database whenever they are written next. Ciò significa che il database potrebbe essere costituito da pagine con tipi di protezione diversi.This means that the database may be composed of pages with different kinds of protection.

Protezione delle pagine incompleteTorn Page Protection

La protezione delle pagine incomplete, disponibile in SQL ServerSQL Server 2000, rappresenta essenzialmente un modo di rilevare i danneggiamenti della pagina dovuti a interruzioni dell'alimentazione.Torn page protection, introduced in SQL ServerSQL Server 2000, is primarily a way of detecting page corruptions due to power failures. Ad esempio, un'interruzione dell'alimentazione imprevista può fare sì che solo una parte di una pagina venga scritta su disco.For example, an unexpected power failure may leave only part of a page written to disk. Se si usa la protezione delle pagine incomplete, quando la pagina viene scritta su disco, per ogni settore da 512 byte nella pagina di database da 8 kilobyte (KB) uno schema di firma a 2 bit specifico viene archiviato nell'intestazione di pagina del database.When torn page protection is used, a specific 2-bit signature pattern for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk. In fase di lettura della pagina dal disco, i bit per il rilevamento di pagine incomplete archiviati nell'intestazione della pagina vengono confrontati con le informazioni effettive sui settori della pagina.When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. Lo scherma di firma alterna il binario 01 e 10 con ogni scrittura, pertanto è sempre possibile sapere quando solo una parte dei settori è stata scritta sul disco: se un bit si trova nello stato errato quando la pagina viene successivamente letta, la pagina è stata scritta in modo non corretto e viene rilevata una pagina incompleta.The signature pattern alternates between binary 01 and 10 with every write, so it is always possible to tell when only a portion of the sectors made it to disk: if a bit is in the wrong state when the page is later read, the page was written incorrectly and a torn page is detected. Per il rilevamento delle pagine incomplete vengono utilizzate risorse minime, tuttavia non vengono rilevati tutti gli errori causati da problemi hardware del disco.Torn page detection uses minimal resources; however, it does not detect all errors caused by disk hardware failures. Per informazioni sull'impostazione del rilevamento delle pagine incomplete, vedere Opzioni ALTER DATABASE SET ( Transact-SQL ).For information on setting torn page detection, see ALTER DATABASE SET Options (Transact-SQL).

Protezione dei checksumChecksum Protection

La protezione dei checksum, introdotta in SQL Server 2005SQL Server 2005, offre una più solida funzionalità di verifica dell'integrità dei dati.Checksum protection, introduced in SQL Server 2005SQL Server 2005, provides stronger data integrity checking. Un checksum viene calcolato per i dati in ogni pagina scritta e archiviato nell'intestazione di pagina.A checksum is calculated for the data in each page that is written, and stored in the page header. Ogni volta che una pagina con un checksum archiviato viene letta dal disco, il motore di database ricalcola il checksum per i dati della pagina e genera un errore 824 se il nuovo checksum è diverso da quello archiviato.Whenever a page with a stored checksum is read from disk, the database engine recalculates the checksum for the data in the page and raises error 824 if the new checksum is different from the stored checksum. La protezione dei checksum è in grado di rilevare più errori rispetto alla protezione delle pagine incomplete in quanto dipende da ogni byte della pagina. Tuttavia, si tratta di un'operazione a moderato consumo di risorse.Checksum protection can catch more errors than torn page protection because it is affected by every byte of the page, however, it is moderately resource intensive. Quando il checksum è abilitato, gli errori causati da interruzioni dell'alimentazione e da hardware o firmware difettosi possono essere rilevati ogni volta che Gestione buffer legge una pagina dal disco.When checksum is enabled, errors caused by power failures and flawed hardware or firmware can be detected any time the buffer manager reads a page from disk. Per informazioni sull'impostazione del checksum, vedere Opzioni ALTER DATABASE SET ( Transact-SQL ).For information on setting checksum, see ALTER DATABASE SET Options (Transact-SQL).

Importante

Quando un utente o un database di sistema viene aggiornato a SQL Server 2005SQL Server 2005 o versione successiva, il valore di PAGE_VERIFY, ovvero NONE o TORN_PAGE_DETECTION, rimane invariato.When a user or system database is upgraded to SQL Server 2005SQL Server 2005 or a later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. È consigliabile usare CHECKSUM.We recommend that you use CHECKSUM. TORN_PAGE_DETECTION può consentire l'utilizzo di un numero più limitato di risorse, ma offre una protezione minore rispetto all'opzione CHECKSUM.TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the CHECKSUM protection.

Informazioni sull'architettura NUMA (Non-Uniform Memory Access)Understanding Non-uniform Memory Access

SQL ServerSQL Server supporta l'architettura NUMA (Non-Uniform Memory Access) e offre buone prestazioni su hardware NUMA senza alcuna configurazione specifica. is non-uniform memory access (NUMA) aware, and performs well on NUMA hardware without special configuration. Con l'aumentare della velocità del clock e del numero di processori, diventa sempre più difficile ridurre la latenza di memoria necessaria per utilizzare questa ulteriore capacità di elaborazione.As clock speed and the number of processors increase, it becomes increasingly difficult to reduce the memory latency required to use this additional processing power. Per ovviare a questo problema, i fornitori di hardware offrono cache L3 di grandi dimensioni. Si tratta, tuttavia, di una soluzione soggetta a limiti.To circumvent this, hardware vendors provide large L3 caches, but this is only a limited solution. L'architettura NUMA offre una soluzione scalabile per questo problema.NUMA architecture provides a scalable solution to this problem. SQL ServerSQL Server è stato progettato per l'uso ottimale in computer basati su NUMA senza che sia necessario apportare alcuna modifica alle applicazioni. has been designed to take advantage of NUMA-based computers without requiring any application changes. Per altre informazioni, vedere Procedura: Configurazione di SQL Server per l'uso di Soft-NUMA.For more information, see How to: Configure SQL Server to Use Soft-NUMA.

Vedere ancheSee Also

Opzioni di configurazione del server Server Memory Server Memory Server Configuration Options
Lettura di pagine Reading Pages
Scrittura di pagine Writing Pages
Procedura: Configurazione di SQL Server per l'utilizzo di Soft-NUMA How to: Configure SQL Server to Use Soft-NUMA
Requisiti per l'uso di tabelle ottimizzate per la memoria Requirements for Using Memory-Optimized Tables
Risolvere i problemi di memoria insufficiente con le tabelle ottimizzate per la memoriaResolve Out Of Memory Issues Using Memory-Optimized Tables