TempDB : Confirmation du nombre de fichiers

Bonjour à tous,

Alors je lis donc qu'il faut un fichier par CPU, parfois si 16, en avoir 12 c'est déjà bien assez. Faire une "évaluation" de la taille qu'un seul fichier prendrait et donc diviser cette taille par le nombre de fichier.

Je lis aussi ici à la page 18, de Paul Randal ce n'est pas si "simple" : http://sqlskills.com/blogs/paul/comm...ervermyths.pdf

Bref, avec cette requête, je compte le nombre de CPU (http://blog.sqlauthority.com/2012/02...al-processors/)

Code:

---

1 2	SELECT cpu_count FROM sys.dm_os_sys_info;

---

Voici le résultat sur mes 12 instances en PROD

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

cpu_count 16 1 8 8 4 8 16 16 8 4 8 2

---

J'ai besoin d'une précision svp. J'en ai une avec 16 CPU et uniquement 2 disques. Est-ce vraiment utile de créer, même 7 ndf, sur 2 disques ? Soit 4 fichiers sur un même disque ?

Si oui, petite précision pour l'autogrowth. Si 4 fichiers (1 mdf et 3 ndf), si j'active l'autogrowth sur les 4. Est-ce que les 4 vont grandir "ensemble" ou uniquement 1 seul et les autres ne grandiront "jamais" ?

Merci
Jean-Luc

mais nooooon....

avec l’augmentation du nombre de cœurs, on a laissé tomber. La recommandation de Microsoft est maintenant, un fichier par socket.
je ne crois pas que tu puisses savoir combien il y a de coeurs sur ta machine avec une requête SQL. Il y a de nouvelles DMV en 2012 pour voir plus d'info sur l'OS, mais si tu es virtualisé en plus, ça n'aide pas.

donc : un fichier par socket, et surtout des fichiers de même taille. ça se fait tout seul apparemment à l'installation de SQL Server 2016.

Sorrryyyy :-)

Mais si j'arrive à connaitre le nombre de coeur. Imaginons un serveur avec 4 coeurs, sur lequel il y a 4 instances, 8 disques.

je devrais en théorie faire 4 fichiers par tempDB par instance donc ?

Citation:

---

Envoyé par janlouk

Mais si j'arrive à connaitre le nombre de coeur. Imaginons un serveur avec 4 coeurs, sur lequel il y a 4 instances, 8 disques.

---

Coeurs ? Instances ? disques ? Que viennent-ils faire ici ?

Tu crées 4 fichiers si tu as quatre cœurs. Si tu as 32 cœurs sur 4 sockets (donc quatre processeurs à huit coeurs), tu crées quatre fichiers.

Ne tiens pas compte de la notion de disques. Tu peux créer ces quatre fichiers sur le même disque. Ce n'est pas pour optimiser les I/O physiques, c'est pour optimiser les threads qui vont prendre en charge ces I/O.

Si tu as plusieurs instances de SQL Server sur la même machine :
1/ tu te dis que tu es dans la misère
2/ tu fais la même chose sur chaque instance, il y a un tempdb par instance.

Citation:

---

Envoyé par rudib

Tu crées 4 fichiers si tu as quatre cœurs. Si tu as 32 cœurs sur 4 sockets (donc quatre processeurs à huit coeurs), tu crées quatre fichiers.

---

Ok, je vais faire cela, j'ai reçu la liste des config de nos serveurs.

Dernière précision stp. Si je crée 4 fichiers de 1 GB et que les transactions auront besoin de 8 GB de place. Si j'active l'autogrowth sur les 4 fichiers, il va répartir les 8 GB sur les 4 fichiers, soit 2 GB par fichier ? Ou remplir plus un que l'autre ?

Je demande car s'il remplit un fichier au max, alors autant que j'adapte la taille à 2GB pour tous et que j'active l'autogrowth sur un seul fichier.

Citation:

---

Envoyé par janlouk

Dernière précision stp. Si je crée 4 fichiers de 1 GB et que les transactions auront besoin de 8 GB de place. Si j'active l'autogrowth sur les 4 fichiers, il va répartir les 8 GB sur les 4 fichiers, soit 2 GB par fichier ? Ou remplir plus un que l'autre ?

---

:nono: Attention confusion. Les fichier à créer par socket sont les fichiers de données. Là tu parles des fichiers de journaux de transactions. Un seul fichier de journal

Euh oui, le mot "transaction" était mal choisi. Mais je parlais bien de .ndf à créer.

Alors, autogrowth sur tous ? Sur 1 ? :P

Citation:

---

Envoyé par janlouk

Alors, autogrowth sur tous ? Sur 1 ? :P

---

Sur tous, même taille d'autogrowth. Par exemple, pour 1 Go, autogrowth de 300 ou 500 Mo sur chaque, selon ton expérience du fonctionnement de ton serveur. Dans ce cas, pense bien à t'assurer que le compte de service de SQL Server a la permission locale "effectuer des tâches de maintenance de volume" pour une initialisation instantanée des fichiers.
https://technet.microsoft.com/fr-fr/...ql.105%29.aspx

Citation:

---

Envoyé par rudib

Pense bien à t'assurer que le compte de service de SQL Server a la permission locale "effectuer des tâches de maintenance de volume" pour une initialisation instantanée des fichiers.

---

Ca je l'ai fait pour mes 43 instances, mais merci pour la précision. Je vais pouvoir m'y mettre.

J'ai une petite précision.

Rudi, tu dis "fichiers de même taille". Mais j'ai un petit soucis, l'espace disque est souvent juste. Et la tempDB est capricisieuse pour donner sa taille.

Souvent, la taille par défaut n'avait pas changer, soit 8 MB, mais bien sûr elle était plus grosse. Parfois 6.5 GB. Pour créer un second fichier de même taille, je dois donc mettre la "bonne" taille pour le .mdf. Je mets dans cet exemple donc, 6.5 GB et là je n'ai plus de message comme quoi je ne peux pas attribuer une taille qui est inférieur à la taille réelle.

Alors, je crée un second de 6.5 GB également. Mais là, je me retrouve avec une tempDB beaucoup trop grosse. Je décide de diminuer la taille à 3GB des 2 fichiers et là ça fonctionne pour le .ndf. Je vois l'espace disque diminuer mais quand je retourne dans les propriétés de la DB, je vois que la taille initiale des 2 fichiers est toujours à 6500 MB. Pourtant sur le disque, c'est tempdb.mdf = 6456320 KB et tempdb2.ndf = 3072000 KB.

Alors mon soucis, si un jour mon instance redémarre, il va réattribuer 2 fichiers de 6.5 GB et ça c'est trop. Et là je me retrouve actuellement avec 2 fichiers qui ont la même taille initiale dans SSMS mais pas sur mon disque.

Est-ce un soucis ?

Les autogrowth sont les mêmes.

Voici par image ce que j'essaye d'expliquer :

Là je crée le second fichier :

Pièce jointe 193038

Ici, j'ai déjà diminué la taille à 3 GB (si mes souvenirs sont bons) :

Pièce jointe 193039

Et là, l'espace disque que ça prend :

Pièce jointe 193040

Hello,

Il faut que tu prennes en compte la taille globale de ta tempdb au départ pour ton calcul.
Si celle-ci doit faire 6.5GB au total et que tu dois allouer 4 fichiers, la taille initiale de chaque fichier doit être 6.5 / 4 par exemple.

Lorsque SQL Server redémarre et crée la base de données tempdb c'est la taille initiale qui est prise en compte.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

USE tempdb; GO   -- taille initiale select size / 128 as size_init_mb, physical_name from sys.master_files where database_id = 2 and type_desc = 'ROWS'   -- taille actuelle sur disque select size / 128 as actual_size_MB, physical_name from sys.database_files where type_desc = 'ROWS'

---

++

Merci David, mais en fait, mon "problème" c'est surtout que maintenant, sur certaines instances je me retrouve avec une taille initiale différente de la taille réelle qui se trouve sur le disque.

Et le soucis aussi, c'est qu'on a toujours eu qu'un fichier tempdb. Donc certains étaient gros, mais comment pouvais-je faire si je devais en créer un second qui a la même taille que le premier ? Et que pour ensuite, ces 2 fichiers fasses la même taille au total que le premier fichier ? c'est clair ? :mouarf:

Est-ce que cela a un impact ? Un risque ? Car si non, alors je laisse comme ça pour le moment et je surveillerai. Ou alors j'interprête mal ce que m'a dit Rudi et je me "tracasse" pour rien.

Ps : Merci pour ta query

Citation:

---

Merci David, mais en fait, mon "problème" c'est surtout que maintenant, sur certaines instances je me retrouve avec une taille initiale différente de la taille réelle qui se trouve sur le disque.

---

On est d'accord que la taille initiale est de toute façon plus petite que la taille sur disque maintenant ?

Citation:

---

Et le soucis aussi, c'est qu'on a toujours eu qu'un fichier tempdb. Donc certains étaient gros, mais comment pouvais-je faire si je devais en créer un second qui a la même taille que le premier ? Et que pour ensuite, ces 2 fichiers fasses la même taille au total que le premier fichier ? c'est clair ?

---

En ajoutant à tempdb un fichier dont la taille correspond à la taille initiale que tu veux donner.

Admettons que tu aies un seul fichier tempdb dont la taille sur disque fait 907MB avec une taille initiale de 10MB.
Tu veux préparer le terrain et ajouter un 2ème fichier. Tu sais que la taille initiale de chaque fichier sera de 256MB.
Il suffit de changer la taille initiale du 1er fichier à 256MB
Faire la même chose en ajoutant le 2ème fichier.

En redémarrant la taille sur disque de tes 2 fichiers feront 256MB.

Bon j'espère avoir compris ton problème :P

++

Non la taille n'est pas forcement plus petite depuis mes changements car c'est justement ce que je cherche à savoir. Y a-t-il un impact si la taille initiale de mes 2 fichiers sont identiques dans les proriétés de la DB mais sont différent l'un de l'autre sur le disque dur.

En fait, quand je te lis j'ai l'impression que j'ai travaillé à l'envers. Et peut-être que je cherche (encore) à trop comprendre 8-)

Lorsque la taille sur le disque du mdf d'une autre DB fait par exemple 1 GB, la taille initiale est la même, elle change toute seule j'ai l'impression. Même si au début, elle était à 8 MB (en tout cas, moi c'est ce que je vois).

Alors que pour la TempDB, la taille initiale peut-être de 8 MB et reste à 8MB alors que sur le disque elle fait 2 GB (en tout cas c'est ce que j'ai remarqué). Et curieusement, si je fais un shrink dessus (Je fais ce test en Non-Prod bien sûr, il ne se passe pas grand chose, ça dure quelques secondes) j'ai remarqué que la taille initiale changeait et devenait la même que sur le disque.

Lorsque la tempDB fait par exemple 500 MB sur le disque, que la taille initiale est de 8MB, si j'essaye de changer la taille initiale à 250 MB, il me met un message d'erreur comme quoi je ne peux pas attribuer une taille plus petite que la taille réelle (je ne sais plus de tête le message en Anglais).

Et je pense que c'est là que j'ai "mal" fait.

Scénario : :mouarf:

Je regarde la taille sur le disque et dans les propriétés de la DB (ex: 500MB), j'essaye de passer de 8 MB à 250 MB. Là je reçois le message d'erreur comme quoi je ne peux pas... Je mets alors 500 MB et ça passe. Je crée le .ndf et je lui donne la même taille de 500 MB et même autogrowth. Parfais, j'ai mes 2 fichiers mais tout ça c'est trop gros vu que la maintenant ma tempDB fait le double de ce qu'il faut.
J'attends 1 minute et là, je décide de passer mes 2 fichiers de 500 MB à 250 MB chacun. Ca fonctionne cette fois, mais si je vais voir sur le disque, les tailles sont différentes (difficile de donner un ratio) mais le .mdf est "logiquement" plus grand que le .ndf.

Et c'est donc là que je me demande si c'est grave ou pas grave.

C'est facile à comprendre non ? :mouarf:

Citation:

---

Envoyé par janlouk

Dernière précision stp. Si je crée 4 fichiers de 1 GB et que les transactions auront besoin de 8 GB de place. Si j'active l'autogrowth sur les 4 fichiers, il va répartir les 8 GB sur les 4 fichiers, soit 2 GB par fichier ? Ou remplir plus un que l'autre ?

---

Entre nous si vous savez à l'avance que ceci va produire 8 Go il serait masochiste d'imposer à SQL Server de faire de l'autogrowth qui pénalise fortement les performances en allongeant artificiellement la durée de certaines requêtes, provoque des blocages, de la contention, des attentes et peut être des verrous mortels !

C'est un peu comme sis vous disiez, je vais gonfler mes pneus à moitié, afin d'éviter de gâcher de l'air et que vous partiez sur l'autoroute !!!

A +

Oui oui, c'était juste pour savoir comment SQL Server remplissait quand il y avait plusieurs fichiers. Je sais que la règle est de mettre la bonne taille tout de suite et avoir le minimum possible d'autogrowth.