Combien de mémoire est nécessaire pour garder une table en cache?(SQL Server 2008)


5

Je lance des requêtes longues et complexes qui utilisent principalement une grande table - 8 Go, 40 millions de lignes.Autant que je sache, toutes/la plupart des lignes sont utilisées dans chaque requête.Je vois beaucoup d’IO dans le moniteur d’activité - pour la première requête et pour chaque requête suivante.Le serveur utilise actuellement 6,5 Go de mémoire et je souhaite effectuer une mise à niveau.La question est, combien de mémoire est nécessaire pour éviter toutes ces lectures de disque?Est-ce dans le stade approximatif de la taille de la table ou plus?

C'est la sortie SET STATISTICS IO.BigTable est celui dont je parle, SmallTable a une relation un-à-plusieurs avec BigTable.#entrance contient la sortie de la requête (plusieurs centaines de lignes de sortie).

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+2

Ce serveur/base de données ne fait-il rien d'autre que d'exécuter vos requêtes sur 1 table de 8 Go de données?Je me gratte aussi un peu au-dessus de la table, mais l’aspect complexe de cette question.Une chance de DDL ou un petit aperçu de la nature de cette complexité? 14 sept.. 112011-09-14 22:20:40

  0

Juste une vérification de base, quelle version du serveur SQL utilisez-vous?si ce n'est pas l'entreprise, vous ne pourrez pas utiliser toute votre mémoire actuelle pour le serveur SQL. 16 sept.. 112011-09-16 15:30:00

  0

J'ai finalement mis à niveau la RAM du serveur et fait un test.J'ai posté les résultats comme réponse ci-dessous. 09 févr.. 132013-02-09 08:50:11

3

Quelle est la nature de vos questions?SELECTs seulement?Ou un mélange de DUI (DELETE, UPDATE, INSERT)?et quel est votre modèle de récupération?utilisez-vous le niveau d'isolation de transaction par défaut?

Si vous voulez mettre toutes ces pages en mémoire, vous aurez besoin de plus de mémoire (mais vous le saviez déjà).Vous voulez savoir "combien", mais la vérité est que, selon la nature de vos requêtes, vous pouvez toujours voir certains problèmes d'E/S même avec suffisamment de mémoire pour contenir la table entière.

Si vous tenez compte de tout ce qui précède, comparez la taille de la table sur le disque à la quantité de mémoire.Ne soyez pas surpris si vous trouvez que vous avez encore des E/S de disque par la suite.

  0

J'ai mis à niveau la RAM du serveur et "la taille de la table sur le disque comme étant la quantité de mémoire" est tout à fait correcte.Voir ma réponse ci-dessous. 08 févr.. 132013-02-08 11:59:48


8

C'est une réponse en deux parties.

La première partie consiste à acheter autant de RAM que vous pouvez vous permettre (à condition que cela rentre dans le serveur).

La deuxième partie est que vous verrez toujours des lectures sur des données.L'astuce consiste à voir si ces lectures sont physiques ou logiques.Les lectures physiques vont sur le disque.Les lectures logiques sont lues à partir du pool de mémoire tampon en mémoire.Vous pouvez le voir en mettant "SET STATISTICS IO ON" en haut du script SQL, puis en l'exécutant dans SSMS.Dans l'onglet Messages, vous obtiendrez des informations d'E/S spécifiques pour chaque requête en cours d'exécution, en particulier le nombre d'opérations logiques et physiques (lectures dans votre cas) qui se déroulent table par table.Si les nombres de lectures physiques sont assez élevés (ces nombres sont essentiellement le nombre de blocs lus de manière multiple, divisez par 10 puis divisez par 1024 pour obtenir un nombre en mégas) et vous aurez alors besoin de plus de RAM.Si le nombre de lectures logiques est élevé, vous n'allez pas réellement sur disque, mais votre requête peut nécessiter un réglage, des index créés, etc.

(Remarque: pour les besoins de cette réponse, je suppose uniquement les instructions SELECT. Les opérations INSERT/UPDATE/DELETE sont un peu plus complexes, mais les mêmes bases s'appliquent.)

  0

Je vais essayer: SET STATISTICS IO ON et de faire rapport.Plus d'infos: Les requêtes sont 100% sélectionnées.Chaque requête prend plusieurs minutes.Le serveur exécute également la petite application qui effectue les requêtes et transfère les résultats dans Excel, mais je doute que cela affecte les performances du serveur. 15 sept.. 112011-09-15 05:00:37

  0

Si vous avez besoin d'aide pour lire le résultat, n'hésitez pas à publier le plan d'exécution et la sortie SET STATISTICS IO. 15 sept.. 112011-09-15 05:05:59


1

Pas sûr que plus de RAM va aider ici.Un calcul rapide suggère que votre requête est en train de traiter quelque part dans les environs de 125 Go de données, ce qui est assez impressionnant pour une table contenant 8 Go.

(2829948 + 13482115 + 1568) x 8/1048576 = 125 Go

Les 270 Mo de lectures physiques représenteront une partie de la lenteur du fonctionnement, mais rien d’autre que les surcoûts en mémoire.

En retravaillant la requête, vous obtiendrez de meilleurs résultats qu'en ajoutant de la mémoire supplémentaire.Publiez le plan DDL/requête/exécution dans une nouvelle question et quelqu'un sera certainement en mesure de vous aider.


3

Je suis toujours en retard à la fête.:) Cette requête peut très probablement être optimisée.Mais pour la question de combien de RAM mettre en cache une table de 8 Go ... une question préliminaire est de savoir combien de nœuds de mémoire SQL Server indique-t-il pour le serveur?Chaque nœud de mémoire (équivalent logique des nœuds NUMA physiques) dispose d'un cache de pool de mémoire tampon géré indépendamment.Les tâches exécutées sur les processeurs de ce nœud insèrent des blocs dans le pool de mémoire tampon de ce nœud.

Ainsi, s'il existe une chance que les tâches qui liront la table de 8 Go dans son intégralité s'exécutent sur un seul nœud NUMA, le pool de mémoire tampon de ce nœud doit pouvoir contenir la table de 8 Go.Commencez donc avec 8 Go de mémoire serveur maximum par nœud NUMA.Alors commençons à l'augmenter.La mémoire de requête peut consommer jusqu'à 70% du pool de mémoire tampon, ne laissant que 30% pour le contenu du bloc de base de données.Ainsi, la table de 8 Go ne doit pas dépasser 30% de la mémoire maximale du serveur sur un seul nœud NUMA pour le serveur.26 2/3 Go pour la mémoire maximale du serveur.Par nœud NUMA.Si c'est SQL Server 2012, le matériel pour lequel SQL Server 2008 R2 et les versions antérieures utilisaient l'allocateur multi-pages (qui était donc en dehors de l'étendue de mémoire maximale du serveur) se trouve maintenant dans l'étendue maximale de la mémoire du serveur et doit être comptabilisé.De toute façon, il doit être inclus dans la mémoire totale du serveur, que ce soit ou non une partie de la limite de mémoire du serveur SQL Server.

Oh, oui ... toute utilisation de tables temporaires ou d'une autre utilisation de tempdb doit également être intégrée dans la mémoire maximale du serveur, afin de ne pas laisser la table être expulsée du cache.

Ajoutez suffisamment de mémoire pour le système d'exploitation Windows (4 Go au moins).Ajoutez suffisamment de threads de travail SQL Server, du processus principal SQL Server, des travaux d'agent SQL, ainsi que de tout autre besoin en mémoire d'application.

Sur un seul serveur à nœud NUMA, je disposais d’environ 36 Go de RAM et d’une mémoire serveur maximale d’au moins 27 Go pour mettre en cache de manière fiable le tableau de 8 Go.Et s'il y a plusieurs nœuds NUMA, cela augmentera d'au moins 26 2/3 Go de mémoire serveur maximale par nœud NUMA, ainsi que des threads de travail supplémentaires pour accompagner les cœurs supplémentaires des nœuds NUMA supplémentaires.

Bien que cela puisse sembler excessif, cela est dû en partie à la façon dont SQL Server gère NUMA.Mais vous pourriez avoir de la chance!Si un seul rédacteur paresseux suffit, vous pouvez utiliser l'indicateur de trace de démarrage 8015 pour ignorer NUMA et gérer un seul pool de mémoire tampon.De cette manière, environ 36 à 40 Go devraient suffire à conserver la mémoire de la table de 8 Go en mémoire, quel que soit le nombre de nœuds NUMA présents sur le serveur.discutez-en un peu plus dans le post suivant.Si vous décidez d'utiliser l'indicateur de trace 8015, je vous recommande d'évaluer l'indicateur de trace 8048 en même temps, afin d'éviter une escalade des conflits de verrouillage spinlock et des attentes CMEMTHREAD associées à l'allocation de mémoire.http://sqlsasquatch.blogspot.com/2013/02/sql-server-cache-database-working-set.html


2

J'ai mis à niveau le serveur RAM de 8 Go à 64 Go et fait un test.La taille de la table est maintenant de 16,05 Go et l’espace d’index est de 187 Mo (1 index clusterisé et 1 unique index non clusterisé).J'ai vérifié l'utilisation totale de la RAM du serveur avant et après l'exécution d'une requête qui effectue la moyenne de chacun des 137 champs de la table.La mémoire totale est passée de 4,22 Go à 20,4 Go = 16,18 Go, ce qui correspond à 100,8% de la taille du tableau.SQLRockstar avait parfaitement raison de prédire que la quantité d’utilisation de RAM était un indicateur approximatif de la taille de la table.

Plus d'informations:

  • Le nombre de pages de l'index clusterisé est de 98,1%
  • Les requêtes ultérieures sur la table n'ont causé aucune entrée/sortie disque.
  • Développeur SQL Server 2008R2
  • Le plan d'exécution montre que l'index clusterisé a été utilisé