Discussion :

[nookie67]

Bonjour à tous,

J'ai récemment mis en place une solution d'optimisation qui m'a semblée pertinente pour la BDD tempDB : multiplier les fichiers de données afin d'optimiser la parallélisation et éviter les goulots d'étranglement I/O
(http://mcherif.wordpress.com/2013/04...doptimisation/)

Je suis en environnement de production, le serveur de BDD est un serveur physique en windows server 2008 R2 avec un SQL Server 2008 R2. Il n'héberge qu'une seule instance de BDD.
Il dispose de 12 processeurs physiques, j'ai donc estimé d'après l'article mentionné plus haut que je pouvais créer 7 fichiers de données supplémentaires (donc 8 au total) pour tempDB.

J'ai suivi scrupuleusement les instructions, à savoir : créer les fichiers avec une même taille initiale, un meme pourcentage de croissance automatique, etc. + redémarrage de l'instance pour vider proprement le fichier initial.

J'ai donc 8 fichiers paramétrés ainsi :
- taille initiale : 6Go
- croissance auto : 10%
- croissance illimitée

MON PROBLEME : seul le premier fichier de données est utilisé...
Il fait environ 40 Go, et les autres fichiers font toujours leur taille initiale.

Quelqu'un aurait-il une explication à cela ?

Merci d'avance pour vos retour !!

[elsuket]

Bonjour,

Pouvez-vous réaliser une capturé d'écran du résultat de la requête suivante ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
;WITH
	CTE AS
	(
		SELECT		DB_ID() AS database_id
				, DS.name AS filegroup_name
				, DF.file_id
				, DF.name AS logical_name
				, DF.type_desc AS file_type_desc
				, CASE DF.is_percent_growth
						WHEN 0 THEN CASE DF.growth WHEN 0 THEN 'DISABLED' ELSE CAST(DF.growth / 128 AS varchar(20)) + ' MB' END
						ELSE CAST(DF.growth AS varchar(3)) + ' %'
				END AS growth
				, CAST(DF.size / 128.0 AS decimal(14, 2)) AS file_size_MB
				, CAST(FILEPROPERTY(DF.name, 'SpaceUsed') / 128.0 AS decimal(14, 2)) AS occupied_size_MB
				, CASE DF.max_size
					WHEN -1 THEN 'UNLIMITED'
					WHEN 0 THEN 'DISABLED'
					ELSE CAST(CAST(DF.max_size / 128.0 AS bigint) AS varchar(20)) 
				END AS max_size_MB
		FROM		sys.database_files AS DF
		LEFT JOIN	sys.data_spaces AS DS
					ON DF.data_space_id = DS.data_space_id
	)
SELECT		COALESCE(C.filegroup_name, 'TRANSACTION_LOG') AS filegroup_name
		, C.logical_name
		, C.file_type_desc
		, C.growth
		, C.max_size_MB
		, C.file_size_MB
		, C.occupied_size_MB
		, C.file_size_MB - C.occupied_size_MB AS free_space_MB
		, CAST((CAST(C.occupied_size_MB AS numeric(14, 2)) / C.file_size_MB) * 100 AS numeric(14, 2)) AS [%occupied]
		, CAST((CAST(C.file_size_MB - C.occupied_size_MB AS numeric(14, 2)) / C.file_size_MB) * 100 AS numeric(14, 2)) AS [%free]
FROM		CTE AS C
ORDER BY	logical_name

Par ailleurs, 48Go me semble excessivement élevé, même si vous avez une charge de travail qui fait beaucoup recours aux variables de type TABLE, ou aux tables temporaires. Utilisez vous le versionage de données ?

Par ailleurs, nous trouvons dans cet article le paragraphe suivant :

Si l’activité est critique et susceptible de beaucoup solliciter tempDB, vous pouvez créer un nombre de fichiers de données tempDB égal à de ¼ à ½ CPUs. Cela permet de notamment faire face à tout risque de contention durant l’allocation d’extents, en optimisant les opérations I/O.

Pour ma part j'administre des serveurs à 80 CPU, et seuls 4 fichiers de données pour TempDB ont été nécessaires pour éliminer toute contention, alors que l'instance traite en moyenne 10,000 transactions par seconde, et que les procédures stockées les plus exécutées font recours aux objets temporaires dont j'ai précédemment parlé.
Donc il n'y a pas de règle à ce sujet, et l'expérience me l'a prouvé à plusieurs reprises. Il est simplement important que le nombre de fichiers soit pair, et effectivement que tous soient de la même taille avec le même grossissement. Ici vous avez spécifié un grossissement en pourcentage, ce qui est une hérésie dans tous les cas : mettez plutôt des Mo.
Or ici, l'auteur oublie de mentionner qu'avant d'augmenter le nombre de fichiers de données de TempDB, il faut déjà être certain que l'on est sujet à une contention au niveau des pages d'allocation. Est-ce ce que vous avez observé ?

Quand je suis certain qu'il y a bien le type contention que je décris ci-dessus, et que je ne peux pas toucher le code qui en est responsable, je commence par ajouter un fichier (j'en ai donc deux). Ensuite je vérifie qu'on n'a plus de contention, et dans le cas contraire, j'ajoute deux fichiers de plus, et ainsi de suite.

@++

[nookie67]

Merci pour votre retour !

En effet, je ne suis pas convaincu qu'il soit nécessaire d'avoir autant de fichiers pour tempDB.

Le fait que la BDD grossisse jusqu'à 40Go provient principalement des traitements de vérification des BDD (DBCC CHECKDB). J'ai testé et vérifié, en partant de fichiers de taille initiale de 6 Go : une fois le ou les DBCC exécutés sur différentes BDD, la taille de la BDD "explose".

Si j'ai multiplié les fichiers de tempDB, c'est aussi parce que nous rencontrons régulièrement, mais néanmoins assez aléatoirement (du moins je n'ai pas trouvé la cause réelle) sur des traitements assez basiques, mais il y a beaucoup d'I/O.

Un même traitement va parfois prendre qq minutes, et à d'autres moments plusieurs heures, voire ne finit jamais... Le même traitement exécuté sur des environnements de test ne prend lui aussi que qq minutes.

Toute la difficulté réside en effet dans la localisation du problème (traitements qui se chevauchent, verrouillage d'index/de page/etc.).

Voici le résultat de la requête que vous m'avez demandé d'exécuter :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
filegroup_name	logical_name	file_type_desc	growth	max_size_MB	file_size_MB	occupied_size_MB	free_space_MB	%occupied	%free
PRIMARY	tempdb2	ROWS	10 %	UNLIMITED	6144.00	4.63	6139.37	0.08	99.92
PRIMARY	tempdb3	ROWS	10 %	UNLIMITED	6144.00	4.94	6139.06	0.08	99.92
PRIMARY	tempdb4	ROWS	10 %	UNLIMITED	6144.00	5.31	6138.69	0.09	99.91
PRIMARY	tempdb5	ROWS	10 %	UNLIMITED	6144.00	4.94	6139.06	0.08	99.92
PRIMARY	tempdb6	ROWS	10 %	UNLIMITED	6144.00	4.75	6139.25	0.08	99.92
PRIMARY	tempdb7	ROWS	10 %	UNLIMITED	6144.00	4.69	6139.31	0.08	99.92
PRIMARY	tempdb8	ROWS	10 %	UNLIMITED	6144.00	4.50	6139.50	0.07	99.93
PRIMARY	tempdev	ROWS	10 %	UNLIMITED	31948.81	21.63	31927.18	0.07	99.93
TRANSACTION_LOG	templog	LOG	10 %	UNLIMITED	3738.81	92.33	3646.48	2.47	97.53

Navré pour la mise en forme, mais je pense que ça reste lisible !!

[nookie67]

On voit bien que les fichiers sont tous utilisés à moins de 0.1% (y compris celui de 32000 Mo).

Outre le choix que j'ai fait avec la multiplication des fichiers, je ne comprends tout de même pas pourquoi seul un des fichiers grossit/est utilisé

Je vais déjà définir la croissance en Mo plutôt qu'en %

Faudrait-il également définir une taille limite des fichiers ?

[mikedavem]

C'est surtout que ton premier fichier fait 31948.81 MB alors que les autres ne font que 6144.00 MB.

Dans ce cas l'algorithme de répartition des données va d'abord utiliser le 1er fichier jusqu'à qu'il se remplisse suffisamment avant d'utiliser les autres.

Il faut que tu réduises la taille de ton premier fichier égale à celles des autres. Effectivement après tu pourras également modifier l'expansion des fichiers en MB pour un meilleur contrôle

++

[elsuket]

Si j'ai multiplié les fichiers de tempDB, c'est aussi parce que nous rencontrons régulièrement, mais néanmoins assez aléatoirement (du moins je n'ai pas trouvé la cause réelle) sur des traitements assez basiques, mais il y a beaucoup d'I/O.

Je le comprends tout à fait, cependant il faut vous assurer que la contention dans TempDB est bien la source de votre problème. Avez-vous requêté la DMV sys.dm_os_waiting_tasks en conjonction avec d'autres DMV pour observer le type d'attente ? En supposant que l'attente est bien du type que vous soupçonnez, est-ce bien de la contention au niveau des pages d'allocation ?

Par ailleurs il serait intéressant de voir ce que vous retourne la DMV sys.dm_os_wait_stats. Un approche d'audit régulier sur le long terme permet souvent de détecter des problèmes avant qu'ils soient ressentis par le serveur, par l'utilisateur, ou par les deux. Auditer cette DMV est donc très utile

@++

[nookie67]

C'est surtout que ton premier fichier fait 31948.81 MB alors que les autres ne font que 6144.00 MB.

Dans ce cas l'algorithme de répartition des données va d'abord utiliser le 1er fichier jusqu'à qu'il se remplisse suffisamment avant d'utiliser les autres.

Il faut que tu réduises la taille de ton premier fichier égale à celles des autres. Effectivement après tu pourras également modifier l'expansion des fichiers en MB pour un meilleur contrôle

++

Oui, c'est bien pour ça que j'ai posté ce message initialement. Au départ, tous les fichiers avaient la même taille, et seul le 1er a grossit.

L'algo de répartition va d'abord utiliser le 1er fichier, ok, mais jusqu'à quelle taille ?
C'est pour ça que je pose également la question de la taille max du fichier. Si effectivement il utilise ce fichier "à l'infini", ma configuration n'a aucun intérêt