Ottimizzazioni del database di preconfigurazione
A causa del ruolo critico che SQL Server svolge in qualsiasi ambiente BizTalk Server, è fondamentale che SQL Server essere configurato/ottimizzato per ottenere prestazioni ottimali. Se SQL Server non è ottimizzato per ottenere prestazioni ottimali, i database usati da BizTalk Server diventeranno un collo di bottiglia e le prestazioni complessive dell'ambiente BizTalk Server subiranno. In questo argomento vengono descritte diverse ottimizzazioni delle prestazioni di SQL Server da seguire prima di installare BizTalk Server e configurare i database BizTalk Server.
Impostare l'unità di allocazione file NTFS
SQL Server archivia i dati in Extent, ovvero raccolte di otto pagine 8 KB fisicamente contigue o 64 KB. Pertanto, per ottimizzare le prestazioni del disco, impostare le dimensioni dell'unità di allocazione NTFS su 64 KB, come descritto in "Procedure consigliate per la configurazione del disco" in Procedure consigliate di I/O di pre-distribuzione.
Considerazioni sulla versione e sull'edizione di SQL Server
Diverse versioni ed edizioni di SQL Server offrono funzionalità diverse che possono influire sulle prestazioni dell'ambiente BizTalk Server. Ad esempio, in condizioni di carico elevato, è possibile che venga superato il numero di blocchi di database disponibili per la versione a 32 bit di SQL Server, che è dannoso per le prestazioni della soluzione BizTalk. Valutare la possibilità di ospitare il database MessageBox in una versione a 64 bit di SQL Server se si verificano errori di "blocco" nell'ambiente di test. Il numero di blocchi disponibili è significativamente più alto nella versione a 64 bit di SQL Server.
Si consideri la tabella seguente quando si decide sulle funzionalità del motore di database necessarie per l'ambiente BizTalk. Per soluzioni a livello aziendale su larga scala che richiedono supporto per il clustering, BizTalk Server supporto per il log shipping o il supporto di Analysis Services, è necessario SQL Server Enterprise Edition per ospitare i database SQL Server.
Per un elenco completo delle funzionalità supportate dalle edizioni SQL Server, vedere SQL Server Edizioni e funzionalità supportate.
Considerazioni sulla pianificazione del database
È consigliabile ospitare i database SQL Server nell'archiviazione veloce , ad esempio dischi SAN veloci o dischi SCSI veloci. È consigliabile RAID 10 (1+0) anziché RAID 5 perché raid 5 è più lento durante la scrittura. I dischi SAN più recenti hanno cache di memoria molto grandi, quindi in questi casi la selezione raid non è altrettanto importante. Per migliorare le prestazioni, i database e i relativi file di log possono risiedere in dischi fisici diversi.
Prendere in considerazione anche l'ottimizzazione della profondità della coda della scheda bus host (HBA) se si usa una rete di archiviazione (SAN). Ciò può influire in modo significativo sulla velocità effettiva di I/O e sui valori predefiniti può essere insufficiente per SQL Server. I test sono necessari per determinare un valore ottimale, anche se la profondità della coda di 64 è generalmente accettata come buon punto di partenza in assenza di raccomandazioni specifiche del fornitore
Installare il Service Pack più recente e gli aggiornamenti cumulativi per SQL Server
Installare i Service Pack più recenti e gli aggiornamenti cumulativi più recenti per SQL Server nonché i Service Pack più recenti di .NET Framework.
Installare i Service Pack di SQL e gli aggiornamenti cumulativi sia in BizTalk Server che in SQL Server
Quando si installano Service Pack o aggiornamenti cumulativi per SQL Server, installare anche il Service Pack o l'aggiornamento cumulativo nel computer BizTalk Server. BizTalk Server usa componenti di SQL Client aggiornati da SQL Server Service Pack e aggiornamenti cumulativi.
Prendere in considerazione l'uso di un'unità ssd veloce per ospitare il SQL Server tembdb
Prendere in considerazione l'uso di una o più unità SSD (Solid State Disk) per ospitare TempDB. Le unità SSD offrono vantaggi significativi in termini di prestazioni rispetto ai dischi rigidi tradizionali e vengono rapidamente abbassate di prezzo man mano che accedono ai mercati mainstream. Poiché le prestazioni di TempDB sono spesso un fattore chiave per le prestazioni generali di SQL Server, il costo iniziale aggiunto delle unità viene spesso restituito rapidamente dall'aumento complessivo delle prestazioni SQL Server, soprattutto quando si eseguono applicazioni aziendali per cui SQL Server prestazioni è fondamentale.
Valutare la possibilità di implementare l'agente di raccolta dati e la gestione di SQL Server 2008 R2 Data Warehouse
SQL Server 2008 R2 supporta l'uso del nuovo Data Warehouse di raccolta dati e gestione per raccogliere dati correlati alle prestazioni di ambiente/database per l'analisi dei test e delle tendenze. L'agente di raccolta dati mantiene tutti i dati raccolti nel Data Warehouse di gestione specificato. Anche se non si tratta di un'ottimizzazione delle prestazioni, questa operazione sarà utile per l'analisi di eventuali problemi di prestazioni.
Concedere l'account usato per SQL Server il privilegio Pagine di blocco di Windows in memoria
Concedere il privilegio Pagine di blocco di Windows in memoria all'account del servizio SQL Server. Questa operazione deve essere eseguita per impedire al sistema operativo Windows di eseguire il paging della memoria del pool di buffer del processo di SQL Server bloccando la memoria allocata per il pool di buffer in memoria fisica.
Nell'ambiente lab l'opzione Blocco pagine in memoria dei criteri di Windows è stata abilitata per impostazione predefinita. Vedere Abilitare l'opzione Blocca pagine in memoria.
Importante
Alcune limitazioni si applicano quando si concede all'account del servizio SQL Server il privilegio Pagine di blocco di Windows in memoria. Vedere la documentazione seguente:
Concedere il diritto di SE_MANAGE_VOLUME_NAME all'account del servizio SQL Server
Assicurarsi che l'account che esegue il servizio SQL Server disponga del privilegio di Windows "Esegui attività di manutenzione volume" o assicurarsi che appartenga a un gruppo che lo esegue. In questo modo, l'inizializzazione immediata dei file garantisce prestazioni ottimali se un database deve aumentare automaticamente.
Impostare min e max server memory
I computer che eseguono SQL Server che ospitano i database BizTalk Server devono essere dedicati all'esecuzione di SQL Server. Quando i computer che eseguono SQL Server che ospitano i database BizTalk Server sono dedicati all'esecuzione SQL Server, è consigliabile impostare le opzioni "min server memory" e "max server memory" in ogni istanza di SQL Server per specificare la quantità fissa di memoria da allocare a SQL Server. In questo caso, è necessario impostare "min server memory" e "max server memory" sullo stesso valore (uguale alla quantità massima di memoria fisica che verrà usata SQL Server). In questo modo si ridurrà il sovraccarico che altrimenti verrebbe usato da SQL Server la gestione dinamica di questi valori. Eseguire i comandi T-SQL seguenti in ogni computer che esegue SQL Server per specificare la quantità fissa di memoria da allocare a SQL Server:
sp_configure ‘Max Server memory (MB)’,(max size in MB)
sp_configure ‘Min Server memory (MB)’,(min size in MB)
Prima di impostare la quantità di memoria per SQL Server, determinare l'impostazione di memoria appropriata sottraendo la memoria necessaria per Windows Server dalla memoria fisica totale. Si tratta della quantità massima di memoria che è possibile assegnare a SQL Server.
Nota
Se i computer che eseguono SQL Server che ospitano i database BizTalk Server ospitano anche Enterprise Single Sign-On Master Secret Server, potrebbe essere necessario modificare questo valore per assicurarsi che sia disponibile memoria sufficiente per eseguire il servizio Enterprise Single Sign-On. Non è insolito eseguire un'istanza cluster del servizio Enterprise Single Sign-On in un cluster SQL Server per garantire la disponibilità elevata per il server master secret. Vedere Clustering del server master secret
Dividere il database tempdb in più file di dati di dimensioni uguali in ogni istanza di SQL Server usata da BizTalk Server
Assicurarsi che i file di dati usati per tempdb siano di dimensioni uguali è fondamentale perché l'algoritmo di riempimento proporzionale usato da SQL Server è basato sulle dimensioni dei file di dati. Se i file di dati vengono creati con dimensioni diverse, l'algoritmo di riempimento proporzionale userà il file più grande per le allocazioni GAM anziché distribuire le allocazioni tra tutti i file, sconfiggendo così lo scopo di creare più file di dati. Il numero ottimale di file di dati tempdb dipende dal grado di contesa di latch visualizzato in tempdb. Come regola generale, il numero di file di dati deve essere uguale al numero di core del processore/CPU in cui il numero di CPU è 8 o minore. Per i server con più di 8 CPU, creare file di dati per metà del numero di CPU (anche in questo caso, solo la contesa di latch).
Nell'ambiente lab è stato usato lo script seguente per creare 8 file di dati TempDB ognuno dei quali ha dimensioni di file pari a 1024 MB con crescita di 100 MB e un file di log di 512 MB con crescita di 100 MB. I file di dati vengono spostati nell'unità H: e il file di log viene spostato nell'unità I:.
Importante
Questo script viene fornito "così com'è", è destinato solo a scopi dimostrativi o didattici e deve essere usato a proprio rischio. L'uso di questo script non è supportato da Microsoft e Microsoft non garantisce l'idoneità di questo script.
--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--
--***Instructions***
-- 1. If running the script from a remote server, change the context in SSMS to target instance
-- 2. Enable SQLCMD mode (add & click toolbar button or toggle by clicking Query > SQLCMD Mode)
-- 3. Commence execution of scripts (recommend running statements discretely to more easily remedy potential problems)
-- 4. Examine servername & temp configuration
-- 5. If necessary, 1) Replace instance name in path to reflect target instance *all throughout script*
-- 2) Modify root drives to reflect drives designated for data & log (folder creation *and* ALTER DB statements)
-- 6. Resume script execution
-- 7. If necessary, create new folders
-- 8. Modify/Add data & log files
-- 9. Recycle SQL service using sqlservermanager10.msc
--10. Examine results & if appropriate, delete original tempdb data log files
--(if they were "moved", the original files aren't automatically deleted)
--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--
--1. If running the script from a remote server, change the context in SSMS to target instance
--2. Enable SQLCMD mode (add & click toolbar button or toggle by clicking Query > SQLCMD Mode)
--3. Commence execution of scripts (recommend running statements discretely to more easily remedy potential problems)
--4. Examine servername & temp configuration
SELECT @@SERVERNAME
EXEC dbo.sp_helpdb tempdb
--tempdev 1 C:\tempdb.mdf PRIMARY 8192 KB Unlimited 10% data only
--templog 2 C:\templog.ldf NULL 512 KB Unlimited 10% log only
GO
--5. If necessary, 1) Replace instance name in path to reflect target instance *all throughout script*
-- 2) Modify root drives to reflect drives designated for data & log (folder creation *and* ALTER DB statements)
--6. Resume script execution
--7. If necessary, create new folders
--!!md H:\MSSQL10.<instance>
--!!md H:\MSSQL10.<instance>\MSSQL
--!!md H:\MSSQL10.<instance>\MSSQL\DATA
GO
-- 8. Modify/Add data & log files
--note: even if the out-of-box mdf is already where it needs to be,
--the first command is necessary to modify size & filegrowth
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'H:\tempdb.mdf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat2 , FILENAME = 'H:\tempdat2.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat3 , FILENAME = 'H:\tempdat3.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat4 , FILENAME = 'H:\tempdat4.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat5 , FILENAME = 'H:\tempdat5.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat6 , FILENAME = 'H:\tempdat6.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat7 , FILENAME = 'H:\tempdat7.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat8 , FILENAME = 'H:\tempdat8.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'I:\templog.ldf', SIZE = 512MB , FILEGROWTH = 100MB)
GO
--8b. Modify log file: modify drive & instance name to reflect designated destination for tempdb log
--!!md I:\MSSQL10.<instance>
--!!md I:\MSSQL10.<instance>\MSSQL
--!!md I:\MSSQL10.<instance>\MSSQL\DATA
GO
-- 9. Recycle SQL service in SQL Server Services node of sqlservermanager10.msc
--note, if running script from a UNC share, SSMS will report an error,
--but SQL Server Configuration Manager will open if its location is in %path%
!!sqlservermanager10.msc
--10. Examine results & if appropriate, delete original tempdb data log files
--(if they were "moved", the original files aren't automatically deleted)
EXEC dbo.sp_helpdb tempdb
--!!del C:\tempdb.mdf
--!!del C:\templog.ldf
GO
Usare monitoraggio attività SQL Server 2008 o i report del dashboard prestazioni di SQL Server 2005 descritti in Monitoraggio delle prestazioni SQL Server prestazioni per identificare i problemi di contesa di latch.
Impostare manualmente l'affinità del processo di SQL Server
L'opzione Affinità processo può offrire miglioramenti delle prestazioni in ambienti di SQL Server di livello aziendale di fascia alta in esecuzione in computer non NUMA con 16 o più CPU. Ciò vale soprattutto negli ambienti BizTalk con velocità effettiva elevata in cui è presente una contesa nelle tabelle condivise nel database MessageBox. Poiché i computer SQL Server usati nell'ambiente lab non erano abilitati per NUMA e avevano 16 core, per ottimizzare le prestazioni, sono stati usati i comandi seguenti per impostare l'affinità di processo:
Per impostare manualmente l'affinità del processo di SQL Server da 0 a 15
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU = 0 to 15
Per altre informazioni, vedere ALTER SERVER CONFIGURATION (Transact-SQL).
Configurare MSDTC
Per facilitare le transazioni tra SQL Server e BizTalk Server, è necessario abilitare Microsoft Distributed Transaction Coordinator (MS DTC). Per configurare MSDTC in SQL Server, vedere l'argomento Linee guida generali per migliorare le prestazioni del sistema operativo.
Abilitare il flag di traccia T1118 come parametro di avvio per tutte le istanze di SQL Server
L'implementazione del flag di traccia –T1118 consente di ridurre la contesa tra le istanze di SQL Server rimuovendo quasi tutte le allocazioni di pagine singole. Per altre informazioni, vedere KB 328551: PRB: Miglioramenti della concorrenza per il database tempdb.
Non modificare le impostazioni di SQL Server predefinite per max degree of parallelism, SQL Server statistics o database index rebuilds and defragmentation
Se un'istanza di SQL Server ospita BizTalk Server database, esistono alcune impostazioni SQL Server che non devono essere modificate. In particolare, il SQL Server massimo grado di parallelismo, le statistiche SQL Server nel database MessageBox e le impostazioni per la ricompilazione e la deframmentazione dell'indice di database non devono essere modificate. Vedere SQL Server impostazioni che non devono essere modificate.