Discussion :

[davy.g]

Oui je l'ai formatté aussi à 64K pour les données.

[dbaffaleuf]

OK, tu connais la taille du stripe unit (taille de la bandelette par disque) et le nombre de disques dans le RAID5 ?

[davy.g]

Non, je n'ai pas ces informations.
Tout ce que je peux vous dire c'est qu'il y a 2 diskgroup : 1 FCAL et 1 FATA.
FCAL pour les data, logs, ... et FATA pour les backups.
Il y a aussi 2 autres décisionnels utilisant la même baie que celui sur SQL Server 2008 R2.

[dbaffaleuf]

OK pas grave. Mike avait demandé des infos sur certains compteurs disques:

% disk read
% disk write
Avg Queue Length
Avg. disk sec / read
Avg. disk sec / writes
Avg. disk sec / transfer

Ce serait peut être intéressant d'historiser ces métriques avant de réinstaller pour avoir un étalon. Bon courage pour la suite.

[davy.g]

Il semble que les plans d'exécution estimé entre ma Prod SQL 2005 et ma future Prod SQL 2008 soit différent ce qui pourrait expliquer les lenteurs.
Un traitement exécute une requête avec 2 autojointures sur 1 même table.
Cette table fait environ 130 millions de lignes.
Il y a des tables scan en 2008 qui ne sont pas en 2005.

[mikedavem]

Il serait possible d'avoir le détail de la requête et avoir également les 2 plans d'exécution ?

Effectivement selon le volume de données que vous traitez cela peut être le jour et la nuit.

++

[davy.g]

Voici la requête en question :

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
39
40
41
42
43
 
UPDATE P1 
	set MPA_NB_AGE_BALANCE = datediff(d,convert(datetime,cast(D as varchar(8)),112),convert(datetime,cast(P1.MPA_FK_JOUR as varchar(8)),112))
	FROM SBI_DWH.dbo.TM_PARC P1
	inner join
	(
		select P.MPA_FK_JOUR, P.[MPA_FK_CLIENT] C,Max(P2.MPA_FK_JOUR) D
		from 
		SBI_DWh.dbo.TM_PARC P
		inner join 
		(
			select MPA_FK_CLIENT,MPA_FK_JOUR
			FROM SBI_DWH.dbo.TM_PARC
			Where ISNULL([MPA_MT_BALANCE_AGEE],0) = 0
		) P2
		on P.MPA_FK_CLIENT = P2.MPA_FK_CLIENT 
		and P2.MPA_FK_JOUR <= P.MPA_FK_JOUR
		GROUP BY
		P.[MPA_FK_JOUR]
		,P.[MPA_FK_CLIENT]
		,P.[MPA_FK_CLIENT_HISTO]
		,P.[MPA_FK_BU]
		,P.[MPA_FK_CONTRAT]
		,P.[MPA_FK_CONTRAT_HISTO]
		,P.[MPA_FK_ANCIENNETE]
		,P.[MPA_FK_ENGAGEMENT_RESTANT]
		,P.[MPA_FK_TERMINAL]
		,P.[MPA_FK_OPERATEUR_DONNEUR]
		,P.[MPA_FK_STATUT_PORTAGE_IN]
		,P.[MPA_FK_OPERATEUR_RECEVEUR]
		,P.[MPA_FK_STATUT_PORTAGE_OUT]
		,P.[MPA_FK_CONTRAT_ACTIF]
		,P.[MPA_FK_CONTRAT_ACTIF_ARCEP]
		,P.[MPA_FK_OFFRE]
		,P.[MPA_FK_CANAL_VENTE]
		,P.MPA_FK_JOUR_RELANCE
		,p.[MPA_MT_BALANCE_AGEE]
		,P.[MPA_NB_PLAN_RELANCE]
	) P3
	on P1.[MPA_FK_CLIENT]=  P3.C
	AND P1.MPA_FK_JOUR = P3.MPA_FK_JOUR
	--MODIF POUR OPTIM
	AND P1.MPA_FK_JOUR > @max_dt

La table TM_PARC comprend 120 millions de lignes environ.
En 2005, il y a 3 index seek et en 2008 2 index seek et 1 table scan.

Comment puis-je vous donner les plans d'exécution? au format xml?

Comment peut-on expliquer un différence de plan sur des environnements différents ? Nous sommes censés traiter exactement le même volume de donnée.

[mikedavem]

Vous pouvez enregistrer les plans au format XML (avec l'extension .sqlplan)

Est ce que les statistiques sont à jour sur votre serveur en 2008 avant le lancement de la procédure en question ?

++

[davy.g]

oui, j'avais lancé un job de reclacul des stats au préalable une fois avoir vu que les perfs étaient catastrophique.
Chose que je n'avais pas fait sur les environnements de tests et pour lesquels il n'y a pas de problème.

[SQLpro]

Il semble que les plans d'exécution estimé entre ma Prod SQL 2005 et ma future Prod SQL 2008 soit différent ce qui pourrait expliquer les lenteurs.
Un traitement exécute une requête avec 2 autojointures sur 1 même table.
Cette table fait environ 130 millions de lignes.
Il y a des tables scan en 2008 qui ne sont pas en 2005.

Il est normale que les plans de requêtes soient différents. Les moteurs SQL sont différents entre 2005 et 2008.

A +

[davy.g]

oui, evidemment mais entre des environnements 2008 avec les mêmes données sur des serveurs différents?

[SQLpro]

Cela dépend aussi du paramétrage serveur et du paramétrage de la session.

Lisez l'article de Erland Sommarskog sur le sujet : http://www.sommarskog.se/query-plan-mysteries.html

A +

[davy.g]

Bonsoir,

Pourquoi quand je lance la commande afficher le plan d'exécution estimé et quand je l'affiche quand la requête s'exécute, les plans sont différents ?

Merci.

[SQLpro]

Sans doute à cause des statistiques. Le premier est souvent estimé par rapport à ceux en cache. Si les stats diffère alors le plan estimé peut être remis en cause et un nouveau plan généré !

A +

[mikedavem]

Vous pouvez tjrs pour tester forcer lutilisation de votre index et voir le résultat en exécution estime et en exécution réel par rapport au plan propose par le moteur.

++

[Waldar]

De mon côté je pencherai pour réécrire la requête.
Que souhaitez-vous faire fonctionnellement ?

De ce que j'en ai compris, vous voulez mettre dans la colonne MPA_NB_AGE_BALANCE la différence en nombre de jours entre la date la plus récente où MPA_MT_BALANCE_AGEE vaut zéro ou rien.

Pour être bien sûr, un petit jeu de données représentatif nous permettrait très probablement d'écrire une requête, probablement plus performante.

[davy.g]

Bonjour,

Je viens de voir que sur mon serveur de test, le plan d'exécution de la requête posant problème est similaire (pas terrible non plus) et cette requête est passée.
Sur mon serveur de future Prod (ou la requête met un temps infini), quand j'exécute 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
 
WITH Waits AS  (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     )
SELECT
     W1.wait_type AS WaitType,
     W1.WaitS,
     W1.ResourceS,
     W1.SignalS,
     W1.WaitCount,
     W1.Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
     ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95 -- percentage threshold
ORDER BY W1.Percentage DESC
GO

J'ai une majeure partie de CXPACKET :

CXPACKET 5872.169000 4050.946000 1821.223000 2230110 74.186205047700407
PAGEIOLATCH_SH 1243.330000 1242.627000 0.703000 72990 15.707643005839468
SOS_SCHEDULER_YIELD 281.014000 0.211000 280.803000 344478 3.550197929466008
PAGEIOLATCH_EX 167.676000 167.463000 0.213000 18694 2.118339257194098

Est-ce que la parallélisation excéssive peut m'engendrer des temps hallucinant comme cela?
Si c'est la cas, dois-je découper ma base utilisateur en plusieurs fichiers ?
Ma base tempdb est déjà découpée en 4 fichiers sur 4 Lun différents.
Ma base utilisateur est découpée en 2 fichiers (1 data et 1 index) sur 2 Lun différents: 3 à 4 tables très volumineuses occupent 90% de cette base.

Si le découpage peut réduire ces attentes CXPACKET, pouvez-vous m'éclairer sur le moyen de découper cette base (nombre de datafiles...)?

Le serveur comprend 4 procs x 6 coeurs.

Merci.

[SQLpro]

Bonjour,...
Est-ce que la parallélisation excéssive peut m'engendrer des temps hallucinant comme cela?

Oui, si toute l'architecture n'est pas fortement //. Exemple :axes physique pour les fichiers sur les disques, accès mémoire.
En particulier NUMA pour la RAM.

Si c'est la cas, dois-je découper ma base utilisateur en plusieurs fichiers ?

OUI ! ou limiter le MAXDOP, déjà global (sp_configure) et ensuite pour certaines requêtes.

En sus si vos requêtes font des opérartions non //lélisable, exemple du XML, alors c'est catastrophique !

Ma base tempdb est déjà découpée en 4 fichiers sur 4 Lun différents.
Ma base utilisateur est découpée en 2 fichiers (1 data et 1 index) sur 2 Lun différents: 3 à 4 tables très volumineuses occupent 90% de cette base.

Partitionnez !

Si le découpage peut réduire ces attentes CXPACKET, pouvez-vous m'éclairer sur le moyen de découper cette base (nombre de datafiles...)?

Le serveur comprend 4 procs x 6 coeurs.

Merci.

En sus prévoyez de n'utiliser que 75% des proc en donnant à SQL Server les CPU de 0 à n - (0.75 * nbr CPU).
Exemple si 24 CPU, attribuer à SQL Server les CPU 0 a 17 ! (affinity mask dans sp_configure)
Et je mettrait un maxDop = 4 dans le sp_configure !

A +

[davy.g]

Merci pour vos réponses.
Concernant ma base utilisateur, en combien de fichiers dois je la découper?
Dois-je découper de façon globale ou sortir mes tables volumineuses sur des datafiles (donc des Lun) différents ?

Merci.

[dbaffaleuf]

J'ai une majeure partie de CXPACKET :

Donc voilà où je voulais en venir avec les waits...

Cela dit, avant de baisser le MAXDOP au niveau de l'instance (ou même au niveau de la requête), je commencerais par regarder pourquoi le coût déclenche la compilation d'un plan parrallèle. Tu nous parle d'un table scan sur 130 milllions de lignes sur une des TM_PARC, ça peut être une piste à creuser. Si tu arrives à faire baisser le coût de la requête de telle manière à ce que l'optimiseur n'ait pas besoin de calculer un plan parallèle, ou qu'il ne parte pas dans des opérateurs à risque comme le merge exchange (il faudrait regarder les types de jointures dans ton plan), ça peut être plus intéressant plutôt que de limiter au niveau de toute l'instance car globalement MAXDOP favorise la perf en général.

Malgré tout et là où je rejoins Fred, c'est que plus le MAXDOP est élevé plus il y a de risques de dépendances entre workers. Rappelons que le MAXDOP est appliqué en entrée et en sortie de chaque opérateur //, ça peut finir avec des dizaines et des dizaines de workers en attente les uns des autres, c'est la plaie du SQL Server en environnement SMP. C'est pas pour rien que CXPACKET est le premier wait remonté dans les sondages.