Contexte :
Je ne parlerai ici que de la mémoire gérée par le processus du moteur relationnel (sqlservr.exe), mais bien d'autres consommations mémoire peuvent se trouver avec les programmes SQL Server :
- Integration Services
- Reporting Services
- Analysis Services
- Agent SQL
- Etc..
Outils de surveillance :
J'utiliserai essentiellement le gestionnaire de tâches :
Et le moniteur de performances pour surveiller les compteurs de SQLServer :
Premières questions :
Comment SQLServer alloue t-il de la mémoire ?
Essentiellement pour les buffers de données lors de leur 1ère utilisation ; en effet un processus de gestion de cache de mémoire va privilégier les données en RAM au détriment du disque (qaund on compare les temps d'accès RAM et disque on comprend pourquoi)
Lors des utilisations suivantes, les données étant déjà en cache, les temps d'accès seront très bon.
Voyons cela :
Ici la mémoire initiale de mon serveur SQL :
Passons à quelques requêtes :
use
SuiviPerformances
go
select
*
from
snapshots.performance_counter_values
where
collection_time
between
'2009-10-04 01:45:40.0000000 +02:00'
and
'2009-10-04 01:45:40.0000000 +02:00'
Visiblement, on consomme !
Et, enfin, on arrive au régime de croisière :
Les requêtes suivantes sur les mêmes données ne demanderont pas de mémoire supplémentaire :
select
*
from
snapshots.performance_counter_values
where
collection_time
between
'2009-10-04 01:45:40.0000000 +02:00'
and
'2009-10-04 01:45:40.0000000 +02:00'
Si , bien sûr, le serveur (dans mon domaine = une instance) n'est pas sollicité pour autre chose…
Quand SQLServer libère t-il cette mémoire pour le système d'exploitation ?
Jamais ! En fait, il gère son buffer de manière circulaire, donnant aux process qui en ont besoin de la mémoire parmi celle déjà allouée.
Une 1ère conclusion s'impose :
Il faudra limiter la mémoire de SQLServer dans plusieurs cas :
- En multi instance : en effet le procédé ci-dessus se passe pour chaque instance et donc Somme (raminstance) < Mémoire totale
- Sur un serveur multi rôle : par exemple IIS + SQLServer, Analysis + relationnel, etc…
Jusqu'où peut on aller comme cela ?
Cela dépend de :
La version de SQLServer :
- Express 1Go Maxi
- Workgroup 4 Go Maxi
- Standard 32 bits dépend de l'OS
- Enterprise 32 bits dépend de l'OS
- Standard 64 bits dépend de l'OS
- Enterprise 64 bits dépend de l'OS
La version de l'OS : par exemple
- Windows Server 2003 Standard Edition 4 Go maxi
- Windows Server 2003 Enterprise Edition 32 Go maxi
Cas particulier du 32 bits :
A cause de l'adressage, les capacités pratiques sont en fait limitées à 1.6Go, sauf à utiliser l'extension de mémoire AWE Address Windowing Extension.
Voyons cette limite sur une grosse table :
use
SuiviPerformances
go
select
*
from
snapshots.performance_counter_values
Il est en train de souffrir …
Il souffre de plus en plus …
Il est arrivé à bloc de ses capacités.
D'autres requêtes, telles que celle-ci :
select
*
from
snapshots.os_wait_stats
n'auront pas d'autre recours que de 'pousser' de la mémoire pour trouver de la place !
Que se passe-t-il si on ne peut pas pousser ?
Bonne question ! C'est une famine de RAM et on peut arriver à un blocage du serveur SQL.
Dans d'autres cas, SQLServer va tuer les requêtes avec une erreur de mémoire insuffisante… expérience que j'ai vécue sur les VLDB , notamment lors de grosses réindéxations.
Comment tout bloquer ?
Bien sûr, je décris ici ce qu'il ne FAUT PAS faire …
- Faire des select * sur des grosses tables (ce que je fais dans ce post)
- Faire des requêtes très longues par rapport au commit : ces données modifiées sont inamovibles
- Ne pas indexer ses tables : ceci oblige SQLServer à faire des scan
- Faire beaucoup de mises à jour sur une seule transaction : au-delà d'un certain nombre de locks il y aura escalade des verrous
- Limiter de manière trop importante la mémoire accordée au serveur SQL
- Utiliser des dizaines d'instances sur une seule machine
- Etc…
Comment passer la barre des 1.6 Go en 32 bits ?
On peut voir cette configuration :
Dans les propriétés du serveur :
Ou par script :
sp_configure
'show advanced options', 1
reconfigure
GO
sp_configure
'awe enabled'
Activation :
sp_configure
'awe enabled', 1
reconfigure
go
Effectivement, un paramétrage de l'OS est à faire en plus :
Il faut ajouter un utilisateur/groupe :
Cet utilisateur est celui qui correspond au compte de service de sqlservr.exe.
Après redémarrage du service :
sp_configure
'awe enabled'
Microsoft recommande fortement de fixer les valeurs maxi de mémoire (comme en 64 bits),
Aussi :
sp_configure
'min server memory', 1024
reconfigure
GO
sp_configure
'max server memory', 2048
reconfigure
GO
C'est reparti pour nos requêtes de chargement du buffer :
On monte ….mais le gestionnaire de tâches ne le voit plus :
On est arrivé au nouveau maximum
Alors AWE , la solution à tous nos problèmes ?
Pas vraiment, j'ai pu constater avec mes VLDB que certains processus 'tournent' encore dans l'adressage du 32 bits avec, à la clé des échecs d'allocation mémoire…
Je pense donc que la seule voie possible pour les VLDB est de passer en 64 bits.