Optimiser son matériel

Objectif
Tout comme une plante ne peut atteindre sa taille normale et porter ses fruits à pleine maturité que lorsque la terre et les conditions météorologiques sont adaptées, SQL Server est totalement dépendant de la qualité du matériel sur lequel il est installé. Ce chapitre présente les éléments importants de ce matériel.

Choisir l’architecture matérielle

SQL Server est par nature grand consommateur de ressources physiques. Un système de gestion de bases de données a besoin de bonnes performances des sous-systèmes de stockage, avec lesquels il travaille beaucoup, non seulement pour écrire et lire des données parfois en gros volumes, mais aussi pour maintenir les journaux de transaction, et tempdb, la base de données qui recueille les tables temporaires, les tables de travail internes ainsi que les versions de lignes dans les fonctionnalités de row versioning. La mémoire vive est elle aussi sollicitée. SQL Server doit conserver un certain nombre de choses dans l’espace d’adressage virtuel (le virtual address space, ou VAS) du système d’exploitation. Dans le VAS, SQL Server pose la mémoire des sessions utilisateurs, des verrous, du cache de plans d’exécution, la mémoire d’exécution des requêtes et le cache de données. L’exécution des requêtes peut être très consommateur de RAM : le moteur d’exécution doit passer d’un opérateur de plan d’exécution à l’autre les lignes récupérées, et parfois tout un ensemble de lignes pour les traiter en une seule fois (pour réaliser un tri ou une table de hachage, par exemple). Ces opérations sont également consommatrices de processeur. Tous les éléments sont donc à prendre en considération. Dans un système traitant une volumétrie raisonnable de données et suffisamment dimensionné en RAM, la vitesse du sous-système disque n’est pas le critère le plus décisif, car la plupart des données lues le seront depuis le cache de données, en RAM. Dans tous les cas, plus la quantité de RAM est importante, meilleures seront les performances. En ce qui concerne les CPUs, un système multiprocesseurs est aujourd’hui incontournable. SQL Server utilise intensivement le multiprocessing, en répartissant ses requêtes sur des threads de travail, ou en parallélisant la même requête si l’activité de la machine est suffisamment légère pour le permettre au moment de l’exécution.

Le processeur

C’est une évidence, SQL Server utilise activement le multi-processing. N’hésitez pas à bâtir une machine de huit processeurs ou plus. L’édition standard ne supporte que quatre CPUs. Entendez quatre processeurs physiques (quatre sockets). Si vous utilisez des processeurs multi-core (dual ou quad), cela augmente le nombre de processeurs supportés par cette édition. De même, le mode de licence par processeurs de SQL Server est à entendre par socket. Pour quatre quad-core, donc seize processeurs logiques, vous vous acquittez d’une licence de quatre processeurs. Le multi-core est bien supporté, en revanche l’hyper-threading peut poser des problèmes, nous le détaillerons dans la section suivante.

Voir le nombre de processeurs

SELECT 
    cpu_count AS logical_cpus, 
    scheduler_count as schedulers, 
    hyperthread_ratio,
    cpu_count/hyperthread_ratio AS cores,
    affinity_type_desc AS affinity_type, 
    virtual_machine_type_desc AS virtual_machine_type
FROM sys.dm_os_sys_info WITH (READUNCOMMITTED) 
OPTION (RECOMPILE, MAXDOP 1);

Le parallélisme

Les opérations de bases de données, notamment la génération du plan d’exécution, et la restitution de données, sont habituellement gourmandes en temps processeur. Les serveurs sont aujourd’hui de plus en plus multiprocesseurs, et les processeurs (notamment Intel) modernes intègrent des architectures de multi-threading ou de multi-core, qui leur permettent de travailler en parallèle. Deux cas de figure sont possibles : l’exécution en parallèle de requêtes différentes, ou l’exécution en parallèle de différentes parties de la même requête. C’est ce deuxième cas de figure qu’on appelle la parallélisation d’une requête. La décision de paralléliser une requête est prise en temps réel par le moteur relationnel, selon l’état actuel du système : si l’activité est faible, et la requête est estimée coûteuse (longue), SQL Server sera plus enclin à paralléliser que si la requête est simple et que le système est chargé de demandes de petites requêtes. En d’autres termes, la parallélisation est plus intéressante sur un système analytique de type OLAP, que sur une base de données transactionnelle très active, de type site web. Le degré de parallélisme, ainsi que le seuil à partir duquel la durée estimée d’une requête la classe comme candidate à la parallélisation, sont des paramètres du système que vous pouvez modifier :

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'affinity mask'
EXEC sp_configure 'max degree of parallelism'
EXEC sp_configure 'cost threshold for parallelism'

L’option « affinity mask » vous permet de n’attribuer à SQL Server que certains processeurs. La valeur par défaut, 0, indique que tous les CPUs sont utilisés. Pour spécifier seulement certains processeurs, attribuez un entier correspondant au masque de bits représentant, du bit de poids faible au bit de poids fort, le ou les processeurs à utiliser. Par exemple, pour n’utiliser que les processeurs 0, 2 et 4, attribuez la valeur binaire 00010101, donc 21 en décimal.

Ce masque d’affinité ne peut donc gérer que 32 processeurs, ce qui est la limitations de Windows Server 2003 32 bits de toute façon. Sur les architectures 64 bits, une option supplémentaire est disponible : affinity64 mask pour l’affinité sur 32 processeurs supplémentaires.

À moins que vous n’ayez à libérer des processeurs pour une autre application (par exemple une autre instance de SQL Server), il vaut mieux laisser SQL Server utiliser tous les processeurs à disposition. Dans le cas d’un système virtualisé, que nous verrons plus loin, ce sera de tout manière à la machine virtuelle de gérer les processeurs physiques qu’elle utilise, donc ceci ne concerne pas cette section.

L’option « max degree of parallelism » indique, sur le nombre de processeurs défini dans l’affinité, combien pourront être enrôlés dans la parallélisation d’une même requête. La valeur par défaut, 0, indique que tous les CPUs peuvent être utilisés. Cette option modifie le comportement de SQL Server pour l’exécution de toutes les requêtes. Exemple :

EXEC sp_configure 'max degree of parallelism', 2

pour permettre une parallélisation sur deux processeurs au maximum.

Lorsqu’une requête est parallélisée, elle doit utiliser le nombre de processeurs indiqué dans « max degree of parallelism », pas moins. En d’autres termes, si vous laissez SQL Server paralléliser sans limite, sur un système comportant beaucoup de processeurs, tous les processeurs devront prendre en charge la requête, et le coût afférant à la synchronisation entre les processeurs sera sûrement plus important que le gain de performance de l’exécution en parallèle, faisant donc plus de mal que de bien.

De plus, le parallélisme peut poser problème sur des processeurs utilisant la technologie d’hyper-threading. Cette technologie propriétaire d’Intel, intégrée dans les processeurs Pentium 4, est moins utilisée aujourd’hui mais pourrait faire son retour dans la future architecture « Nehalem ». Elle permet sous certaines conditions d’améliorer les performances d’applications multi-threadées. Un processeur hyper-threadé est vu par Windows comme deux processeurs[^7], et SQL Server peut tenter de paralléliser une requête sur ce qui représente en réalité le même processeur physique. On obtiendra ainsi un ralentissement au lieu d’une amélioration, en provoquant des attentes inter-threads. Ces ralentissements pourront se constater grâce à un type particulier d’attente (wait). Nous reviendrons en détail sur la détection des attentes dans le chapitre 7. Sachez pour l’instant que vous pouvez obtenir des statistiques d’attentes de processus SQL Server grâce à la vue de gestion dynamique sys.dm_os_wait_stats. La colonne wait_type indique pour quelle raison l’attente a eu lieu. Lors d’une exécution en parallèle, les threads ont besoin de s’échanger des données. Le type CXPACKET indique une attente de synchronisation d’échange de paquets. Ainsi, si vous constatez des ralentissements liés à des attentes de type CXPACKET, il ne vous reste plus qu’à jouer avec « max degree of parallelism ».

Hyper threading

Si vous constatez, sur des processeurs hyper-threadés, une augmentation de l’utilisation du CPU mais une baisse de performances, liées à des messages visibles dans le journal d’erreur de SQL Server (errorlog) indiquant qu’un thread n’a pas réussi à acquérir un spinlock (une structure légère de synchronisation de threads), vous vous retrouvez certainement dans la situation décrite par Slava Oks ici. La solution est de désactiver purement et simplement l’hyper-threading dans le bios de votre machine.
Comme remarque générale, considérant les problèmes potentiels que génère l’hyper-threading, faites des tests de charge hyper-threading désactivé puis activé, et si vous ne voyez pas de gain de performance notable avec l’hyper-threading, désactivez-le pour de bon.

NUMA

Comme nous allons le voir, sur une architecture NUMA, les processeurs se voient alloués des bus séparés pour accéder à la mémoire. La mémoire accédée sur le bus du processeur est nommée mémoire locale, et la mémoire accessible sur les autres bus est logiquement nommée mémoire distante. Vous vous doutez que l’accès à la mémoire distante est plus long et coûteux que l’accès à la mémoire locale. Il faut donc éviter de paralléliser une requête sur des processeurs qui accèdent à des bus différents. Vous le faites en limitant le « max degree of parallelism » au nombre de processeurs sur un nœud.

Les bonnes pratiques sont donc les suivantes :

• Sur des systèmes très sollicités en requêtes courtes, désactivez totalement la parallélisation (EXEC sp_configure 'max degree of parallelism', 1) : cela évite que quelques requêtes de reporting mobilisent plusieurs processeurs qui auraient été utiles pour d’autres lots en attente ;

• Sur des systèmes comportant plus de huit processeurs, limitez le « max degree of parallelism » à 8. Une parallélisation sur plus de 8 processeurs devient excessivement coûteuse en synchronisation »;

• Sur une architecture NUMA, le « max degree of parallelism » ne doit pas être supérieur au nombre de processeurs sur un nœud NUMA.

Il existe une option de requête, MAXDOP, qui produit le même effet de façon localisée. Exemple :

SELECT c.LastName, c.FirstName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
OPTION (MAXDOP 2);

Comme elle l’emporte sur l’option de l’instance, cette option de requête peut être utile pour augmenter le nombre de processeurs défini par « max degree of parallelism », pour une requête spécifique.

Pression sur les processeurs

Évidemment, la pression sur les processeurs est montrée par leur niveau d’utilisation, dans le moniteur de performances ou dans le gestionnaire de tâches de Windows (task manager).

Process Explorer : Nous vous conseillons cet utilitaire développé par Mark Russinovich, qui fait partie de la panoplie des outils « winternals » disponibles maintenant sur le site de Microsoft. Il vous offre une vision beaucoup plus complète que le gestionnaire de tâches. http://technet.microsoft.com/en-us/sysinternals/.

Mais une forte utilisation des processeurs ne veut pas dire pression (car ils peuvent travailler beaucoup sans être stressés), et encore moins que le problème vient des processeurs eux-mêmes. La pression est montrée par le compteur de performances System : Processor queue length, qui indique la file d’attente sur les processeurs.

Les compteurs de performances sont visibles dans le moniteur système, qui sera détaillé dans la section 5.3

Les vues de gestion dynamique sont aussi de bons indicateurs :

SELECT
SUM(signal_wait_time_ms) as signal_wait_time_ms,
CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms)
AS NUMERIC(20,2)) as [%signal (cpu) waits],
SUM(wait_time_ms - signal_wait_time_ms) as resource_wait_time_ms,
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) /
SUM (wait_time_ms) AS NUMERIC(20,2)) as [%resource waits]
FROM sys.dm_os_wait_stats;

vous donne la raison des attentes dans SQL Server, soit sur le processeur (%signal (cpu) waits), soit sur les ressources (%resource waits), comme le disque ou les verrous. Un pourcentage important d’attente processeur (en-dessus de 20 %) indique une pression sur les CPUs. Pour connaître les détails par type d’attente :

SELECT wait_type, (wait_time_ms * .001) as wait_time_seconds
FROM sys.dm_os_wait_stats
GROUP BY wait_type, wait_time_ms
ORDER BY wait_time_ms DESC;

Vous pouvez aussi voir le nombre de tâches gérées simultanément par un ordonnanceur :

SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

Ce qui vous donne une idée précise de la charge de travail que doit supporter le système. Un nombre élevé de runnable_tasks_count, par exemple 10 sur plusieurs processeurs, peut vous indiquer qu’une mise à jour matérielle (ou une simplification de votre code) est nécessaire. Voici quelques ressources utiles :

• Troubleshooting Performance Problems in SQL Server 2005
• measure CPU pressure

Identifier le nombre de processeurs nécessaires

Vous pouvez modifier à chaud l’affinité des processeurs afin de tester si votre serveur peut supporter sans problème moins de processeurs. Cela peut être utile pour décider du nombre de processeurs sur l’achat d’une future licence.

En sachant qu’une licence par coeur (core licensing) peut coûter assez cher. Cette licence est vendue par pack de deux processeurs (avec en général un minimum de 2X2 pack pour initialiser une commande).

Exemple de prix de licence en édition Standard sur pack de deux coeurs :

Identifier les temps de signal

SELECT *,
  CAST(wait_time_ms * 1.0 / waiting_tasks_count as decimal(18, 2)) AS avg_wait_ms,
  CAST(signal_wait_time_ms * 1.0 / waiting_tasks_count as decimal(18, 2)) AS avg_signal_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN (N'CXPACKET', N'SOS_SCHEDULER_YIELD');

Identifier les noeuds NUMA

La requête suivante indiquent combien de noeuds NUMA sont disponibles, et le nombre de coeurs attribués à chaque noeud.

En général un socket correspond à un noeud NUMA.

SELECT 
  parent_node_id as NumaNode, 
  COUNT(*) as CPU, 
  SUM(CAST(is_online as tinyint)) as NbOnline
FROM sys.dm_os_schedulers
WHERE status LIKE N'VISIBLE%'
AND STATUS NOT LIKE '%(DAC)'
GROUP BY parent_node_id
ORDER BY parent_node_id;

Tester avec une affinité plus basse

SELECT *
FROM sys.dm_os_schedulers
WHERE status LIKE N'VISIBLE%'
AND STATUS NOT LIKE '%(DAC)';

Par exemple, on définit une affinité sur le premier noeud NUMA uniquement.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0;

Analyse des schedulers

SELECT 
  cpu_id,
  is_idle,
  current_tasks_count,
  runnable_tasks_count,
  current_workers_count,
  active_workers_count,
  work_queue_count,
  SUM(CAST(is_idle as tinyint)) OVER () as nb_idle,
  SUM(current_tasks_count) OVER () as nb_tasks,
  SUM(active_workers_count) OVER () as nb_workers,
  SUM(runnable_tasks_count) OVER () as nb_runnable
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE'
OPTION (RECOMPILE, MAXDOP 1);

SELECT 
  SUM(CAST(is_idle as tinyint)) as nb_idle,
  SUM(current_tasks_count) as nb_tasks,
  SUM(active_workers_count) as nb_workers,
  SUM(runnable_tasks_count) as nb_runnable
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE'
OPTION (RECOMPILE, MAXDOP 1);

Mémoire vive

NUMA

NUMA est l’acronyme de Non-uniform Memory Access, une architecture matérielle spécifique destinée à optimiser l’accès à la mémoire des systèmes comportant un nombre important de processeurs.

Aujourd’hui, un processeur fonctionne beaucoup plus rapidement que la mémoire vive, et passe donc une partie de son temps à attendre la fin des opérations d’accès à la mémoire. Pour éviter ces attentes, les processeurs intègrent de la mémoire cache. Mais il ne s’agit que d’un pis-aller. L’attente est potentiellement plus importante sur un système comportant beaucoup de processeurs en architecture SMP (Symmetric Multi-Processing), car la mémoire est accédée par tous les processeurs à travers le même bus partagé. NUMA offre une architecture où la mémoire est séparée par processeurs, placée sur des bus de données différents, et est donc accessible en parallèle. Pour répondre aux cas où plusieurs processeurs demandent les mêmes données, NUMA intègre des solutions matérielles ou logicielles pour échanger les données entre les différentes mémoires, ce qui peut bien sûr ralentir les processeurs sollicités. Les gains de performance d’un système NUMA sont donc variables, dépendant en général du nombre de processeurs (NUMA devient intéressant à partir de 8 à 12 processeurs, où l’architecture SMP commence à devenir problématique). Pour obtenir un gain de cette architecture, les applications doivent reconnaître NUMA (être NUMA-aware) pour configurer l’exécution de leur threads par nœuds NUMA. Les applications qui ne reconnaissent pas NUMA peuvent être ralenties, à cause des différences de temps pour accéder à des parties de mémoires situées sur des bus différents.

Pourquoi AWE est-il intéressant avec NUMA ? Comme le PFN (stocké dans la PTE, comme nous l’avons vu) montre à quel nœud NUMA appartient la page de mémoire, la lecture de cette information (à l’aide de l’API QueryWorkingSetEx depuis le Service Pack 1 de Windows Server 2003) nécessite une attribution et une libération de verrou. De plus, cette lecture doit être réalisée périodiquement, afin de prendre en compte les changements d’attribution physique de la page, qui peuvent résulter d’une pagination sur le disque. AWE verrouille les pages en mémoire, ce qui garantit donc qu’elles ne seront jamais paginées. Ainsi, une application NUMA-aware n’a besoin de récupérer l’attribution physique d’une page qu’une seule fois, sur de la mémoire AWE.

Soft-NUMA

Pour tester l’implémentation de leur moteur sous NUMA, les équipes de développement de SQL Server 2005 ont bâti une version logicielle de l’architecture NUMA, qui en simule l’architecture matérielle. Ils se sont aperçus que cette implémentation logicielle améliorait les performances sur les systèmes multiprocesseurs SMP, et même NUMA. Cet outil fait à la base pour le test, est donc passé en production sous le nom de soft-NUMA. Si vous avez au moins 8 processeurs, essayez de configurer soft-NUMA pour SQL Server, et comparez les performances. Pour ce faire, vous créez des affinités de processeurs par nœuds soft-NUMA dans la base de registre, dans la clé HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration\. Pour les informations pratiques, reportez-vous aux BOL, section « How to: Configure SQL Server to Use Soft-NUMA »

Pression sur la mémoire

SQL Server réagit à la pression sur la mémoire, c’est-à-dire au manque de mémoire lorsque celle-ci est réclamée par des processus. Elle peut libérer des espaces mémoire qui étaient utilisés, par exemple par le cache de données ou le cache de procédures. En 32 bits, le cache de données peut résider dans la mémoire gérée par AWE, mais tout le reste doit être dans l’espace d’adressage virtuel, en-dessous de la limite des 4 Go. Vous pouvez déterminer s’il manque de la mémoire pour le cache de données en surveillant les compteurs de performances de l’objet SQL:Buffer Manager :

• Buffer cache hit ratio – le pourcentage de pages qui ont pu être servies du cache, sans être cherchées sur le disque ;

• Page life expectancy – le nombre de secondes durant lesquelles une page non utilisée reste dans le cache ;

• Stolen pages – nombre de pages « volées » du cache pour être utilisées ailleurs.

Nous détaillons ces compteurs dans la section 5.3.1. Le Buffer cache hit ratio doit être élevé (plus de 97 %). Plus le page life expectancy est élevé, meilleur c’est : SQL Server a assez de mémoire pour conserver ses pages dans le cache. Moins le nombre de pages volées est élevé, le meilleur.

Pour observer les caches, vous avez les objets de compteur SQL:Cache, SQL:Memory, SQL:Memory Manager et SQL:Plan Cache. Si vous voyez des fluctuations (à la baisse) du nombre d’objets dans le cache, vous avez affaire à une pression mémoire (sauf si vous avez exécuté des commandes qui vident le cache de plans, comme un attachement de base de données, ou une commande DBCC).

Vous pouvez aussi utiliser les vues de gestion dynamique pour surveiller la mémoire. Cette requête vous donne la taille des différents caches :

SELECT
    Name,
    Type,
    single_pages_kb,
    single_pages_kb / 1024 AS Single_Pages_MB,
    entries_count
FROM sys.dm_os_memory_cache_counters
ORDER BY single_pages_kb DESC;

Nous vous renvoyons à ces ressources pour entrer dans les détails :

• Troubleshooting Performance Problems in SQL Server 2005 – http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

• Vous trouverez des informations utiles dans cette entrée de blog : http://blogs.msdn.com/slavao/archive/2005/02/19/376714.aspx

Comment SQL server utilise la mémoire vive

Les administrateurs s’étonnent souvent de la manière dont SQL Server occupe la mémoire vive. Certains semblent s’inquiéter de l’augmentation progressive et sans retour de l’occupation en RAM du processus sqlservr.exe, et craignent une fuite de mémoire et une consommation exagérée de ressources. Il s’agit au contraire d’un comportement souhaitable. Il faut comprendre que SQL Server est conçu pour être seul, sans concurrence d’autres applicatifs, sur un serveur. Ainsi, par défaut (vous pouvez limiter la mémoire disponible, nous le verrons plus loin), il s’approprie toutes les ressources raisonnablement disponibles. Si vous lisez ce livre, vous cherchez sans doute à assurer un fonctionnement optimal de SQL Server. Vous ne pouvez le faire réellement qu’en lui dédiant une machine, et en lui permettant de s’attribuer la RAM dont il a besoin. Dans une configuration par défaut, une instance SQL Server ne se limite pas en RAM. Au démarrage, elle calcule la quantité de mémoire réservée pour ses différents éléments, et s’attribue ensuite physiquement (elle « valide » la mémoire réservée) selon ses besoins. Comme le comportement diffère selon les utilisations de la mémoire, nous allons détailler le comportement des éléments importants pour les performances.

SQL Server utilise la RAM pour différents caches, dont les plus importants concernent les pages de données, et le plan d’exécution des requêtes.

Le buffer

Le cache de données (appelé le buffer, ou buffer pool) maintient en mémoire vive autant qu’il est possible de pages de données et d’index. La lecture à partir de la RAM étant environ mille fois plus rapide que sur un disque dur, on en comprend aisément l’intérêt. Ce cache est géré par un composant de SQLOS nommé le Buffer Manager, dont le travail est la lecture des pages du disque vers le buffer, et l’écriture des pages modifiées du buffer vers le disque.

Lors du démarrage de l’instance, SQL Server calcule et réserve l’espace d’adressage virtuel du buffer (la cible de mémoire, ou memory target). Il s’agit d’espace réservé, mais la mémoire physique ne sera prise que selon les besoins. La mémoire réellement utilisée est donc inférieure à l’espace réservé tant que données demandées par les requêtes ne l’auront pas rempli (cette période avant remplissage est appelée le ramp-up). Vous pouvez voir la mémoire cible et la mémoire réellement utilisée (en pages de 8 Ko) dans les colonnes bpool_commit_target et bpool_committed de la vue de gestion dynamique sys.dm_os_sys_info :

SELECT bpool_commit_target, bpool_committed FROM sys.dm_os_sys_info;

Une commande DBCC vous permet de vider les pages du buffer : DBCC DROPCLEANBUFFERS. Comme son nom l’indique, seules les pages propres (non modifiées) sont supprimées, les autres attendent un checkpoint pour être écrites sur le disque. Vous pouvez donc vous assurer de vider le plus de pages possibles en lançant d’abord la commande CHECKPOINT :

CHECKPOINT;

DBCC DROPCLEANBUFFERS;

Lorsque SQL Server a « validé » de la mémoire physique pour le buffer, il ne la rend plus au système. Depuis le moniteur système ou le gestionnaire de tâche (colonnes Memory Usage et Virtual Memory Size), vous verrez le processus sqlserver.exe prendre de plus en plus de mémoire virtuelle au fur et à mesure de l’exécution des requêtes, et ne jamais la rendre. Faisons-en la démonstration de l’intérieur de SQL Server.

/* nous créons premièrement des instructions de
SELECT pour chaque table d'AdventureWorks */

SELECT 'SELECT * FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA
+ '].[' + TABLE_NAME + '];'
FROM AdventureWorks.INFORMATION_SCHEMA.TABLES;

/* pages validées du buffer */
SELECT bpool_committed, bpool_commit_target FROM sys.dm_os_sys_info;

-- nous avons copié ici le résultat de la génération de code
SELECT * 
FROM [AdventureWorks].[Production].[ProductProductPhoto];

-- [...]

SELECT * 
FROM [AdventureWorks].[Production].[ProductPhoto];
GO

-- pages validées du buffer
SELECT bpool_committed, bpool_commit_target 
FROM sys.dm_os_sys_info;
GO

-- vidons le buffer
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO

-- pages validées du buffer
SELECT bpool_committed, bpool_commit_target 
FROM sys.dm_os_sys_info;
GO

Les résultats (sur notre portable), sont :

                          bpool_committed   bpool_commit_target

avant toute requête : 3776 25600

après les SELECT : 0 0

après avoir vidé le 0 0 buffer :

SQL Server conserve donc la mémoire validée. Mais les pages sont-elles bien effacées du buffer ? Nous pouvons le vérifier à l’aide de la DMV sys.dm_os_buffer_descriptors qui détaille chaque page du buffer :

SELECT page_type, count(*) as page_count, SUM(row_count) as row_count
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID('AdventureWorks')
GROUP BY page_type
ORDER BY page_type;

Après le DBCC DROPCLEANBUFFERS, cette requête donne bien un résultat vide.

La DMV que nous venons d’utiliser a un intérêt purement informatif. Il n’est pas crucial pour l’amélioration des performances de détailler le contenu du buffer. SQL Server effectue un très bon travail par lui-même pour n’avoir pas besoin d’inspection. Mais la curiosité peut parfois vous amener à consulter le buffer. Voici une requête qui vous permet de détailler son contenu par tables :

USE AdventureWorks;
GO

SELECT object_name(p.object_id) AS ObjectName,
bd.page_type,
count(*) as page_count,
SUM(row_count) as row_count,
SUM(CAST(bd.is_modified as int)) as modified_pages_count
FROM sys.dm_os_buffer_descriptors bd
JOIN sys.Allocation_units a
ON bd.allocation_unit_id = a.allocation_unit_id
JOIN sys.partitions p
ON p.partition_id = a.container_id
WHERE bd.database_id = DB_ID('AdventureWorks') AND
object_name(p.object_id) NOT LIKE 'sys%'
GROUP BY object_name(p.object_id), bd.page_type
ORDER BY ObjectName, page_type;

Max server memory

On peut de demander : SQL Server ne va-t-il pas s’accorder trop de mémoire, au détriment du bon fonctionnement du système d’exploitation ? Évidemment non, la prise de mémoire est pensée au niveau de l’OS. Par défaut, SQL Server acquiert autant de mémoire possible sans mettre en danger le système. Il utilise pour ce faire l’API de notification mémoire (Memory Notification API) de Windows.

En réalité, SQL Server peut rendre au système la mémoire physique utilisée par le buffer dans un cas : lorsque, fonctionnant sur une platforme 32 bits de version au moins équivalente à Windows XP ou Windows 2003, et lorsque AWE est activé, SQL Server permet une allocation dynamique (dynamic memory allocation) du buffer.

L’instance continue à acquérir de la mémoire physique jusqu’à ce que la limite indiquée dans l’option de serveur max server memory[^9] soit atteinte, ou que Windows signale qu’il n’a plus de mémoire disponible pour les applications. Lorsque Windows signale un manque de mémoire vive, SQL Server restitue de la mémoire (si les conditions vues ci-dessus sont remplies) jusqu’à atteindre la limite inférieure indiquée dans l’option min server memory. Cette dernière option ne signifie pas que, au démarrage, SQL Server va immédiatement occuper cette quantité de mémoire. Dans tous les cas, SQL Server acquiert sa mémoire graduellement. Elle donne simplement une limite en-dessous de laquelle il n’y aura pas de restitution de mémoire à l’OS.

Faut-il fixer la mémoire : Un conseil de performance remontant aux précédentes versions de SQL Server était de fixer la mémoire, c’est-à-dire de donner une valeur identique au min server memory et max server memory. SQL Server 2005 et 2008 gèrent dynamiquement la mémoire dans SQLOS de façon optimale, il peut être contre-performant de fixer la mémoire, et c’est inutile.

Dans le cas d’une allocation dynamique de mémoire, SQL Server ajuste sa consommation selon les disponibilités de la RAM. Autrement dit, si d’autres processus acquièrent et libère de la mémoire, SQL Server en rend et en reprend en miroir. SQL Server peut ainsi s’ajuster au rythme de plusieurs Mo par seconde.

SQL Server 2005, édition Entreprise : Le ramp-up est optimisé pour être plus rapide sur les machines ayant beaucoup de mémoire, parce que les requêtes de pages uniques sont converties en requêtes de 8 pages contiguës. En 2008 cette fonctionnalité n’est plus limitée à l’édition entreprise.

Les disques

Le choix des sous-systèmes disque est bien sûr important. Il est probable que vous soyez de plus en plus dotés de SAN, où les performances sont gérées de façon moins directe que pour des disques locaux, chaque vendeur fournit son outil de gestion. Vous trouvez des conseils pour les SAN dans cette entrée de blog en trois parties : http://blogs.msdn.com/sqlcat/archive/2005/10/11/479887.aspx.

Pour le reste, les règles sont simples : les disques doivent être rapides et avec un taux de transfert élevé. C’est moins important pour les disques qui abriteront les fichiers de données, si la taille des bases est inférieure à la mémoire vive. À ce moment, la plupart des requêtes seront satisfaites par le cache de données (le buffer). Autant que possible, choisissez du RAID 10 (ou 01) plutôt que du RAID 5, car les performances en écriture du RAID 5 sont nettement plus faibles. Déplacez autant que possible vos journaux de transactions sur des disques dédiés, ce qui permettra une écriture séquentielle optimale. Dédiez le disque le plus rapide, de préférence un disque local, à tempdb. Tout ce qui est dans tempdb est jetable, vous n’avez donc pas besoin d’assurer une sécurité importante de ce disque. Un simple miroir pour éviter les interruptions de service suffit.

Vous avez quelques outils à disposition pour mesure la performance de vos disques :

IOMeter et SQLIO sont des outils de pur benchmarking de disque en ligne de commande. Ils sont libres et téléchargeable sur Sourceforge pour le premier, et sur le site de Microsoft pour le second. Ils vous donnent les performances de vos disques. Vous trouvez des conseils d’utilisation en consultant des entrées de blog et références :

• http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx

• http://sqlblog.com/blogs/linchi_shea/archive/2007/02/21/parse-the-sqlio-exe-output.aspx

• http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx

SQLIOSim est un outil de pur stress sur le disque, qui se base sur le même code d’accès au disque que SQL Server (qui n’a pas besoin d’être installé), pour mesure la solidité de votre stockage de masse. Plus d’information dans l’entrée de base de connaissance Microsoft 231619 : http://support.microsoft.com/kb/231619

Pression sur le disque

Le compteur de performance (voir section 5.3) à surveiller pour identifier une contention sur le disques est PhysicalDisk: Avg. Disk Queue Length. Il indique la taille de la file d’attente, c’est-à-dire les processus qui attendent la libération du disque déjà utilisé par un autre processus, pour pouvoir lire ou écrire. Si cette valeur est régulièrement en-dessus de 2 pour un certain temps, vous avez un problème.

PhysicalDisk: Avg. Disk Sec/Read et PhysicalDisk:Avg. Disk Sec/Write sont les temps moyens en secondes, pris par une lecture ou une écriture. On considère qu’une moyenne de moins de 10 millisecondes est très bonne, et qu’à partir de 20 millisecondes cela indique une lenteur. Dès 50 millisecondes, il y a une sérieuse contention.

Physical Disk: %Disk Time est le pourcentage de temps où le disque est en activité. Ce nombre devrait rester en-dessous de 50.

Ces nombres sont à ajuster sur des disques RAID. Le calcul d’ajustement est disponible dans ce document : Troubleshooting Performance Problems in SQL Server 2005 – http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx.

La requête suivante vous donne les demandes d’entrées/sorties en attente, par fichiers de bases de données :

SELECT
    vf.database_id,
    vf.file_id,
    vf.io_stall,
    pio.io_pending_ms_ticks
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vf
JOIN sys.dm_io_pending_io_requests pio
ON vf.file_handle = pio.io_handle

io_pending_ms_ticks est le temps d’attente en millisecondes.

On peut aussi obtenir des statistiques d’attente totales par fichier :

SELECT
    UPPER(LEFT(mf.physical_name, 2)) as disk,
    DB_NAME(vf.database_id) as db,
    mf.name as file_name,
    vf.io_stall,
    vf.io_stall_read_ms,
    vf.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vf
JOIN sys.master_files mf
ON vf.database_id = mf.database_id AND vf.file_id = mf.file_id
ORDER BY disk, db, file_name

La virtualisation

La virtualisation est une technique en plein essor, dont l’objectif est de séparer la couche logicielle de la couche matérielle : un environnement complet, c’est-à-dire le système d’exploitation et les applications qu’il héberge, s’exécute dans une machine virtuelle. Les ressources physiques que cette machine virtuelle utilise sont contrôlées par le logiciel de virtualisation.

Cette architecture permet de déplacer ou dupliquer un environnement, ou de monter en charge très rapidement, sans réinstallation et sans aucune modification de la configuration logicielle. Elle permet également de rationaliser le nombre de machines physiques, en multipliant leur capacité d’accueil. C’est un paradigme qui regroupe les notions de montée en charge verticale et horizontale (scale up et scale out) en une seule.

La virtualisation permet de mettre en place très rapidement des environnements de test ou de développement, mais elle est de plus en plus utilisée pour héberger de vrais serveurs de production, où une bonne performance doit être assurée. Pour ce type de besoin, le logiciel de virtualisation doit s’appuyer sur une architecture matérielle adaptée.

Parmi les quelques outils logiciels de virtualisation sur le marché, les deux plus répandus sont Microsoft Virtual Server et VMware Server (puis XenEnterprise et VirtualIron). Ils sont utiles pour les environnements de test et de développement. Pour obtenir les performances nécessaires à une machine de production, une solution plus puissante comme VMware ESX est requise. Son nom complet est VMware ESX Server, car il s’agit en réalité d’un système d’exploitation en tant que tel. Alors que les logiciels traditionnels de virtualisation s’installent sur un système d’exploitation tel que Windows ou un UNIX, ESX regroupe les deux couches : OS et virtualisation, ce qui permet un niveau de performance largement supérieur. Il comporte également des outils d’administration qui facilitent la gestion centralisée des machines virtuelles, leur clonage et leur migration, notamment.

L’allocation des ressources peut être réalisée dynamiquement. Un module comme VMware DRS (Distributed Resource Scheduler) permet d’attribuer les ressources entre les machines virtuelles selon leur besoin, par rapport à des règles prédéfinies.

L’usage de machines virtuelles facilite également la sauvegarde des données : une machine virtuelle peut être sauvegardée directement au niveau du système hôte (ESX lui-même dans ce cas), et un système de balancement automatique peut être configuré à l’aide de l’outil VMware HA (High Availability). Une machine virtuelle peut être automatiquement relancée sur un autre serveur faisant partie de la « ferme ».

Nous n’allons pas détailler l’optimisation de serveurs virtualisés, c’est un travail en soi, si vous vous y intéressez, nous vous conseillons l’ouvrage de Edward Haletky, VMware ESX Server in the Enterprise, que vous trouverez dans la bibliographie. Nous listons simplement quelques règles de base à suivre si vous voulez mettre en place ce type d’infrastructure.

• Vous trouvez sur le site de VMware un outil de benchmarking nommé VMmark

• Choisissez des processeurs Dual-Core (ou mieux, Quad-Core). Des tests effectués par Dell montrent un gain de performance d’environ 50 % sur des Intel Xeon Dual-Core 32 bits par rapport à des processeurs single-core, et d’autres tests sur des Xeon Dual-Core 64 bits vont jusqu’à un gain de performance de 81 % par rapport à des Xeon single-core.

• Une machine virtuelle peut être supervisée exactement comme un serveur physique, les outils et méthodes que nous présentons dans le chapitre 5 sont donc tout à fait valables pour les machines virtuelles, à la différence près qu’une machine virtuelle peut montrer des signes vitaux tout à fait normaux, mais des performances diminuées, parce que d’autres machines virtuelles s’exécutant sur le même matériel consomment trop de ressources.

• Processeurs, mémoire, réseau et disques peuvent être « partitionnés » pour permettre leur utilisation par plusieurs machines virtuelles.

Il est à noter qu’à l’heure actuelle Microsoft ne supporte officiellement qu’un seul produit de virtualisation pour un déploiement de SQL Server en cluster : le sien, Virtual Server. Vous pouvez consulter l’article de base de connaissance 897615 : Support policy for Microsoft software running in non-Microsoft hardware virtualization software pour vous tenir informé de l’évolution de cette politique.

Configuration du serveur

Microsoft essaie de faire en sorte que SQL Server s’autoconfigure autant que possible. Un certain nombre de règles et d’algorithmes sont implémentés pour lui permettre de s’ajuster aux conditions du système hôte. La configuration interviendra donc souvent lorsque c’est nécessaire, après un test de charge, plutôt que de façon systématique à l’installation.

Dans des cas où le besoin se fait sentir d’optimiser au maximum, dans les moindres détails, les performances du serveur, quelques possibilités de configuration fine existent :

• En utilisant l’option -x au lancement de SQL Server, vous pouvez désactiver le maintien par l’instance des statistiques d’utilisation des CPUs et de taux d’utilisation du cache. Ces statistiques ne sont pas particulièrement coûteuses, mais c’est une option dans les cas où toute optimisation est bonne à prendre. Vous pouvez ajouter cette option à l’aide du Configuration Manager, dans la configuration du service SQL, onglet Advanced, option Startup Parameters.

• Lors de chaque opération de lecture de données, SQL Server vérifie l’intégrité des pages lues. Par défaut, cette vérification se fait à l’aide d’un calcul de checksum sur le contenu de la page, qui est ensuite comparé au checksum stocké dans l’entête de la page à l’écriture des données. Cette opération est également légère et utile pour d’éventuelles (et fort rares) corruptions de données. Vous pouvez néanmoins la désactiver, ou la remplacer par la méthode « historique » de vérification d’intégrité physique, moins complète mais plus légère, nommée torn page detection (un bit est posé à l’écriture de chaque bloc de 512 octet, pour indiquer l’écriture correctement effectuée de ce bloc). Pour une base de données existante, utilisez l’instruction ALTER DATABASE :

-- pour vérifier le type de vérification de page :
SELECT name, page_verify_option_desc FROM sys.databases;

-- pour désactiver la vérification par CHECKSUM :
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY NONE;

-- pour remplacer par une vérification par TORN PAGE DETECTION :
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY TORN_PAGE_DETECTION;