Paramétrage serveur SQL Serveur

Version imprimable

10/02/2010, 11h33
Mothership

Paramétrage serveur SQL Serveur

Bonjour,

Nous avons l'occasion de réinstaller notre serveur SQLSERVER très prochainement, sur la machine suivante :
IBM x3850M2 avec 4 CPU quad coeur et 32 Go de RAM
L'installation sur le serveur précédent, qui lui avait 32 CPU utilisait les paramètres suivants :
12 bases TempDB de 512 Mo
mémoire maximale : 16384 Mo
Mémoire minimale par requête : 1024
Pour la partie processeur, l'affinité est coché et automatiquement le masque d'affinité d'E/S pour tous les processeurs est coché.
La case "Renforcer la priorité SQL server" est elle aussi coché.

Mes questions sont :
Avoir 12 TempDB pour 16 Processeurs me parait tout à fait convenable, j'ai lu qu'il était souvent conseillé d'avoir autant de tempdb que de coeur/cpu.
Dois je en placer 16 ou 12 c'est très bien ?

Au niveau de la taille des tempdb, y a t'il une règle ? lorsque je regarde l'état de la base tempdb en activité, il y a beaucoup d'espace libre. Elle est sollicitée à tous les instants, ou est ce par exemple lors de gros traitements comme une reconstruction d'indexes ?

Au niveau de la mémoire maximale, pourquoi utiliser la moitié de la RAM ? Ce serveur est exclusivement dédié à nos bases de données de PRD, pourquoi ne pas passer à 24 Go ou 30 ? Ce paramètre a je pense été défini automatiquement par SQL lors de son installation.

Pour la partie Processeur, affinité et "renforcer la priorité" même chose, les options automatiquement placées, sont elles les bonnes ?
10/02/2010, 12h11
SQLpro

Beaucoup de choses sont particulièrement inadéquate... Votre terminologie aussi. Vous n'avez pas plusieurs base tempdb, mais sans doute plusieurs fichiers de données pour la seule et unique base tempdb.

1) la bonne pratique en terme, de fichiers de données de la base tempdb est de faire un fichier par CPU actif.
2) le bon nombre de CPU dédié à SQL Server est 75% des proc à partir de 4 et en prenant les proc de poids faible (donc dans votre cas de 0 à 11)
3) d'où donc 12 fichiers de même taille pour votre tempdb (si possible sur 12 disques physiques différents) et surtout de prévoir un fichier du journal sur un disque RAID 10 ou 0+1 d'au moins 30% de la taille globale des données de la tempdb. Par exemple si vous faites 12 fichiers de 1 Go alors votre JT doit faire environ 4 Go
4) si votre serveur est seul il est absurde de ne pas lui donner toute la RAM. Reste à savoir si c'est du 32 ou 64 bits. En 64 bits pas de problème. En 32 bist, c'est différent. Il faut jouer sur AWE...
Liser l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p5...2-bits-et-awe/
5) enfin, les disques sont le plus important. La création des agrégats RAID et leur composition, ne doit pas être pris à la légère. C'est là que vous gagnerez le plus, ou perdrez le plus...
Lisez l'article que j'ai écrit à ce sujet :http://blog.developpez.com/sqlpro/p8...t-le-stocakge/

A +
10/02/2010, 12h19
dbaffaleuf

Hello,

Pour compléter le message de Frédéric,

Peux-tu nous dire quelle version de SQL Server, édition + 32 ou 64 bits, et quelle version de Windows Server, édition + 32 ou 64 bits ?

L'histoire de créer un fichier tempdb par cpu et de même taille date de SQL Server 2000. Sans rentrer trop dans les détails, la création massive de multiples petites tables temporaires appuyait sur deux pages particulières (SGAM et PFS, toujours les 2ième et 4ième pages d'un fichier de données) qui gèrent l'allocation dans le fichier tempdev.mdf. Le fait que ces deux pages soient impliquées systématiquement freinait la capacité à créer massivement de l'allocation pour des objets temporaires. Il y avait alors deux contournements possibles, soit utiliser le traceflag -T1118 qui modifiait la façon dont les premières pages étaient allouées pour chaque table tempo et qui allégeait un peu la charge sur ces deux pages d'allocation, ou alors créer un fichier de données par CPU de même taille pour multiplier le nombre de ces pages d'allocation.

En SQL Server 2005, l'algoritme d'allocation des tables tempo a été revu et à chaque fois qu'une nouvelle table tempo est nettoyée, SQL Server conserve deux pages pour pouvoir les réutiliser lors d'une nouvelle recréation. Donc le traceflag -T1118 et la règle du 1 fichier par CPU de même taille ne prévaut plus tellement. On compte plutôt 2 fichiers pour un quad core, toujours de même taille. Dans ton cas, ça dépend du nombre de création / suppression de tables tempo (compteurs perfmon MSSQL:general Statistics\Temp Tables Creation Rate & MSSQL:general Statistics\Temp Tables for Destruction), mais 8 ça me parait déjà bien raisonnable.

Pour la taille, tempdb est sollicité par les phases d'agrégation ou de tri dans les requêtes, mais aussi par les tables internes de triggers, quand tu va exécuter un DBCC CHECKDB, une reconstruction d'indexes en ligne, etc... Si tu exécute un DBCC CHECKDB(MaPlusGrosseBase) WITH ESIMATEONLY, ça donnera déjà une valeur plancher. Si tu mets une grosse valeur en totalité, il faut t'assurer que le compte de démarrage du service SQL Server dispose du privilège 'Perform Volume Maintenance Tasks', ça réduira le temps de recréation des fichiers de tempdb au redémarrage.

Pour la mémoire: si tu as 32 Gb de RAM, j'imagine que tu es en 64 bits OS + SQL Server. En 64 bits je te conseille de fixer la valeur de mémoire maximale que SQL Server peut prendre, en fonction de ce qui tourne autre que SQL Server sur la machine, et de la verrouiller en mémoire en donnant au compte de service le privilège 'Lock Pages in memory', et redémarrer l'instance. C'est difficile de dire combien tu peut te permettre d'allouer, ne sachant pas ce qui tourne d'autre.

Pour la priorité: Si ta machine est dédiée, je ne vois pas trop l'intérêt de booster la priorité. Elle permet juste lors d'une mise en concurrence de 2 processus de privilégier celui qui a la priorité la plus haute. S'il n'y a pas d'autre concurrent pour accéder aux ressources, booster la priorité ne servira à mon sens pas à grand chose.

Affinité CPU: Quand tu dis le masque d'affinité est coché, il est sélectionné ou 'grisé' ? Le risque c'est Que SQL Server décide de forcer le parallélisme dans de nombreux cas, ce qui peut avoir deux inconvénients:
- Monopoliser plusieurs cores pour une seule requête à un instant donné.
- Partir en Parrallel deadlock. (quand tu observes pour une session un temps d'attente important sur l'évènement CXPACKET, c'est lui).

Il vaut peut être mieux limiter le nombre de CPU que ton instance peut prendre. Mais ca dépend des observations que tu fera par rapport aux dérives du parrallélisme. (Compteur perfmon: SQL Server: Workload Group Stats \ Active Parrallel threads)

A+ David B.
10/02/2010, 13h04
Mothership

Je suis bien sur un serveur Windows 2003 ent 64bits, le PAE est donc désactivé.
Pour la valeure des dbcc checkdb :
Estimated tempdb space needed for checkalloc (kb) : 28170
Estimated tempdb space needed for checktables (kb) : 4703389
ou encore pour la seconde base (plus importante mais peu utilisée (archivage) :
Estimated tempdb space needed for checkalloc (kb) : 18531
Estimated tempdb space needed for checktables (kb) : 6977437

Je remarque que les disques sont souvent un sujet de discussion concernant les bases de tout type d'ailleurs et cela me parait logique.
A l'heure actuelle et où chez nous, on privilégie le SAN et non les disques en interne sauf pour l'OS et le swap généralement.
Utiliser 12 disques (1 par tempdb) peut être possible, mais la gestion des différents RAID en fonction du type de données stockées (DBF; TRAN etc.) me parait un peu dure à mettre en place.
Aujourd'hui dans notre configuration, on ne peut pas gérer aussi finement le raid, l'ensemble des disques étant agrégé dans des luns, eux mêmes agrégés dans des ensembles... Bref c'est le bordel, mais ça marche bien :)
10/02/2010, 13h46
dbaffaleuf

Citation:

Envoyé par Mothership

Estimated tempdb space needed for checktables (kb) : 6977437

Donc au minimum 7 Gb pour tempdb. Par curiosité, quelle taille fait la base d'archivage que tu as passée en argument à DBCC CHECKDB ?

A+ David B.
10/02/2010, 13h49
mikedavem

Citation:

On compte plutôt 2 fichiers pour un quad core, toujours de même taille.

> dbaffaleuf
Personnellement je n'ai jamais essayé cela. A tester ... :D .
Puisque vous êtes dans les précisions, je précise également que dans certains cas les tables temporaires et variables de table peuvent être mises en cache ... ce qui peut réduire considérablement certains traitements.

Citation:

Aujourd'hui dans notre configuration, on ne peut pas gérer aussi finement le raid, l'ensemble des disques étant agrégé dans des luns, eux mêmes agrégés dans des ensembles.

> Mothership
Puisque vous êtes sur un SAN, la configuration des aggrégats de disque n'est qu'un des éléments que vous pouvez optimiser sur le SAN. Vous pouvez également regarder du côté de vos cartes HBA (file d'attente), des paths sur vos fabriques (multipath actif / passif ou actif / actif), des chuncks sur vos contrôleurs RAID etc .... Le cache SAN peut également être pris en compte dans vos optimisations. Bien sûr si les résultats paraissent correctes pour le moment ... attardez vous sur vos réels problèmes ...

++
10/02/2010, 14h44
Mothership

> dbaffaleuf
La base de données fait d'épuration fait : 500 Go
La base de données de production en fait 300.

D'ailleurs sur ce type de taille (que je considère volumineuse même si notre couple oracle/sap est de loin plus important en terme d'espace), y a t'il des préconisations sur la gestion des espaces ?
Aujourd'hui la base d'épuration est un énorme fichier ... de 500 Go et croit irrémédiablement tous les samedis lorsque les épurations sont lancées. Le fichier hébergeant les TRAN lui est insignifiant.

La base de PRD elle, est répartie sur 9 datafiles de taille différentes (entre 20Go et 60go avec croissance illimitée) + deux fichiers de journaux de transaction d'une taille maximum de 25 Go chacun. Sans opération de notre part les fichiers de transactions sont pleins sous 48 heures environ (en fonction des maintenances que nous effectuons sur la base (rebuild indexes par exemple).

> mikedavem
Effectivement aujourd'hui nos voyants sur les disques sont tous au vert, donc RAS de ce coté là. Je m'inquiétais juste de voir toujours les conseils sur du raid 1, 10, 1+0, 5 etc pour tel ou tel type de partition.

>> question supplémentaire
Le fait que la même instance gère ces deux bases de données, faut il une tempdb de 7 + 5 soit 12Go ?
10/02/2010, 14h55
mikedavem

Citation:

Sans opération de notre part les fichiers de transactions sont pleins sous 48 heures environ (en fonction des maintenances que nous effectuons sur la base (rebuild indexes par exemple).

Est ce l'activité transactionnelle qui remplit votre journal ou est ce l'activité + opérations de maintenance qui remplissent votre journal ? Avez vous des sauvegardes de journal planifiées dans le cadre de votre stratégie de sauvegarde ?

++
10/02/2010, 14h57
SQLpro

Citation:

A l'heure actuelle et où chez nous, on privilégie le SAN et non les disques en interne sauf pour l'OS et le swap généralement.
Utiliser 12 disques (1 par tempdb) peut être possible, mais la gestion des différents RAID en fonction du type de données stockées (DBF; TRAN etc.) me parait un peu dure à mettre en place.
Aujourd'hui dans notre configuration, on ne peut pas gérer aussi finement le raid, l'ensemble des disques étant agrégé dans des luns, eux mêmes agrégés dans des ensembles... Bref c'est le bordel, mais ça marche bien

C'est là ou vous aurez le plus à gagner comme vous l'a fait remarqué mikedavem !

A +
10/02/2010, 15h11
Mothership

Quand je dis que c'est le bordel, c'est surtout long à expliquer :)
Mais créer 12 partitions, c'est lourd en gestion je trouve notamment dans un cluster.
L'étude des temps d'accès sur les disques n'ayant pas vraiment été poussée, mais depuis la mise en place de notre architecture SAN, les temps sur l'ensemble des applications/DB n'ont jamais été aussi bon.
Effectivement, cela ne veut pas dire qu'il n'y a pas encore quelques plus à gagner, mais aujourd'hui j'avoue ne pas avoir le temps pour le moment de faire une réelle étude des performances sur l'ensemble de nos bases.

SAP propose avec les "early watch" des outils réellement satisfaisant avec des seuils pré établis qui permettent sans trop gratter (hormis la mise en place des early watch et du mandant associé), d'avoir un réel visu sur l'activité et les temps de traitements.
Le produit qui utilise notre base le fait aussi et les temps sont très bons sur l'ensemble des requêtes (même en mode curseur je sais c'est mal on y peut rien). Les requêtes "dégueulasses" sont souvent liés à un index manquant ou des requêtes "sales", ou encore la gestion de la base par le produit qui créé des trous constamment dans les tables, entrainant une dégradation des temps liés à la fragmentation.
10/02/2010, 15h23
Mothership

> mikedavem

Citation:

Est ce l'activité transactionnelle qui remplit votre journal ou est ce l'activité + opérations de maintenance qui remplissent votre journal ?

L'activité transactionnelle a elle seule remplit nos TRAN en environ 48H. Lors de notre reconstruction des indexes, cela va beaucoup, beaucoup plus vite.

Aujourd'hui, chaque soir, lors de la défragmentation des indexes, nous passons la base en mode simple, ce qui nous permet de réduire la taille des fichiers TRAN lors de la prochaine sauvegarde des Tran.

Citation:

Avez vous des sauvegardes de journal planifiées dans le cadre de votre stratégie de sauvegarde ?

Oui la sauvegarde des TRAN est lancée toutes les 30 minutes en PRD.
Une sauvegarde full chaque nuit et une différentielle tous les midi.
10/02/2010, 15h37
dbaffaleuf

Re-,

Pour la base d'archivage, il faut essayer de préallouer de l'espace plutôt que de laisser SQL Server augmenter régulièrement, pour assurer un minimum de contigüité sur disque, et éviter les nouvelles allocations d'espace. Si le compte de service de SQL Server n'a pas de privilège 'Perform Volume Maintenance Tasks', chaque nouvelle allocation sera réinitialisée avec des zéros, et si c'est un pourcentage sur 500Gb ça peut durer un certain temps à chaque fois. Tu dois avoir des fichiers de trace log_*.trc dans le répertoire ~Log de ton instance, tu peux les charger dans un Profiler et regarder le nombre de fois où ton fichier de données a dû s'augmenter depuis le dernier redémarrage (évènement Data File Auto Grow).

Combien de rétention dois-tu garder dans cette base ? Tu ne peux pas purger un peu d'historique s'il est obsolète ? Si c'est le cas, une bonne purge, un shrink de fichier et un alter index rebuild derrière peuvent te permettre de récupérer pas mal d'espace disque.

A+ David B.
10/02/2010, 15h40
mikedavem

Citation:

Aujourd'hui, chaque soir, lors de la défragmentation des indexes, nous passons la base en mode simple, ce qui nous permet de réduire la taille des fichiers TRAN lors de la prochaine sauvegarde des Tran.

Vous êtes donc obligé de refaire une sauvegarde FULL par la suite ... C'est dommage .. Vous êtes vous orientez vers le mode BULK LOGGED ou à la rigueur l'implémentation d'une alerte qui déclencherait un job de backup qui pourrait limiter le taux de remplissage de votre journal ?

++
10/02/2010, 16h26
Mothership

Y a t'il un paramètre spécial à spécifier dans les sauvegardes des journaux de transaction ?
Je m'explique, il y a quelques temps, nous avons eu un problème notre serveur SQL dans notre cluster MSFT. Par des pirouettes nous avons pu relancer le service SQL Server mais pas l'agent, les plans de maintenances ne se sont donc plus effectués.
Nous avons mis en place via des scripts VBs et des tâches planifiées une sauvegarde des journaux de transaction. Ces dernières n'ont pas empêchées aux fichiers de transaction d'arriver à saturation et donc un blocage de la DB.
10/02/2010, 16h38
mikedavem

Il n'y a pas de paramètre spécial pour cela.

En fait je vous dis cela parce que j'ai eu le même souci que par rapport aux journaux qui étaient "saturées" suite aux opérations de réindexation d'index volumineux.

Nous avons implémenter des alertes qui se déclenchent à 50% de remplissage du journal et qui lancent un job de sauvegarde du journal. (Dans notre cas un journal rempli à 50% correspond forcement à un déclenchement d'uine opération de maintenance .. le reste du temps ce journal n'est rempli qu'à 10% .. la sauvegarde planifiée des journaux toutes les heures permet de stabiliser ce taux de remplissage). C'est une solution temporaire bien sûr ... Il est possible égalment de passer en MODE BULK LOGGED au lieu du mode simple .. ce qui vous permet de ne pas à avoir implémenter un backup full par la suite (La chaîne des LSN nétant pas rompue).

Une autre solution aussi est de prévoir et dimensionner de la place disque pour votre journal ...

++
10/02/2010, 17h36
Mothership

Quelle est la différence entre :
Backup TRAN
et backup log

Je me rends compte que le job VBS utilisait cette commande (TRAN) plutôt que celle indiquée pour 2005/2008 ...
11/02/2010, 17h49
SQLpro

BACKUP TRAN n'existe plus depuis belle lurette !

A +
12/02/2010, 07h51
Mothership

Merci pour ces réponses !