Discussion :

[Krison]

Bonjour,

J'ai un comportement que je ne m explique pas avec un "IF EXISTS": celui-ci prend plusieurs minutes d'exécution... alors que "SELECT @Counter" + "IF @Counter..." ne prend que quelques millisecondes.
PS:Le script "SELECT ..." est exactement le même (copier/coller)

Code "IF EXISTS":

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
 
IF EXISTS (SELECT NULL
					FROM AVGV300 WITH (NOLOCK)
						INNER JOIN PRMD000 sk WITH (NOLOCK) ON sk.RECORDID = AVGV300.REF_RECID AND AVGV300.FELDNAME = 'ZeichnungGedruckt'
						INNER JOIN CTI_Trig_PRMD0x0 ae WITH (NOLOCK) ON ae.BDE_NR = sk.BDE_NR AND ae.TableName = 'PRMD000'
					WHERE ISNULL(AVGV300.PAR_VALUE, '0.0') <> '10.0'
						AND sk.Auftrag NOT LIKE '60%'
						AND LTRIM(RTRIM(ISNULL(sk.Zeichnung, ''))) <> ''
						AND (ISNULL(ae.Zeichnung_N, '') <> ISNULL(ae.Zeichnung_O, '')
									OR ISNULL(ae.Zeich_Idx_N, '') <> ISNULL(ae.Zeich_Idx_O, '')
							)
						AND ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU) IS NOT NULL
						AND ae.InsertDate IS NOT NULL
						AND ae.InsertDate > ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU)
					)
BEGIN
	PRINT 'Exists...'
END
ELSE
BEGIN
	PRINT 'Not exists...'
END

(> 9 minutes !)

Code "SELECT @Counter" + "IF @Counter...":

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
 
DECLARE @Counter INTEGER
SELECT @Counter = COUNT(*)
	FROM AVGV300 WITH (NOLOCK)
		INNER JOIN PRMD000 sk WITH (NOLOCK) ON sk.RECORDID = AVGV300.REF_RECID AND AVGV300.FELDNAME = 'ZeichnungGedruckt'
		INNER JOIN CTI_Trig_PRMD0x0 ae WITH (NOLOCK) ON ae.BDE_NR = sk.BDE_NR AND ae.TableName = 'PRMD000'
	WHERE ISNULL(AVGV300.PAR_VALUE, '0.0') <> '10.0'
		AND sk.Auftrag NOT LIKE '60%'
		AND LTRIM(RTRIM(ISNULL(sk.Zeichnung, ''))) <> ''
		AND (ISNULL(ae.Zeichnung_N, '') <> ISNULL(ae.Zeichnung_O, '')
					OR ISNULL(ae.Zeich_Idx_N, '') <> ISNULL(ae.Zeich_Idx_O, '')
			)
		AND ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU) IS NOT NULL
		AND ae.InsertDate IS NOT NULL
		AND ae.InsertDate > ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU)
SELECT @Counter
IF @Counter > 0
BEGIN
	PRINT 'Exists...'
END
ELSE
BEGIN
	PRINT 'Not exists...'
END

(< 1 seconde)

Je travaille sur SQL-Server 2008 (Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) )

Quelqu'un peut il m'expliquer ?

Merci d'avance


(AVGV300 -> 17780 rows ; CTI_Trig_PRMD0x0 -> 293.788 rows)

[morgand]

regarde les plans d'exécution de tes requêtes et regarde ce qui change

[Krison]

Lorsque je force les Index à utiliser, j arrive à "0 seconde"

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
 
IF EXISTS (SELECT NULL
		FROM AVGD250 AVGV300 WITH (INDEX(IdxAVGD250_RefrecVarParam))	--Cet index fait la différence!
			INNER JOIN PRMD000 sk WITH (INDEX(PRMD000_RECORDID)) ON sk.RECORDID = AVGV300.REF_RECID AND AVGV300.Varparam = 'ZeichnungGedruckt'
			INNER JOIN CTI_Trig_PRMD0x0 ae WITH (INDEX(CTI_Trig_PRMD0x0_Idx04)) ON ae.BDE_NR = sk.BDE_NR AND ae.TableName = 'PRMD000'
		WHERE ISNULL(AVGV300.PAR_VALUE, '0.0') <> '10.0'
			AND sk.Auftrag NOT LIKE '60%'
			AND LTRIM(RTRIM(ISNULL(sk.Zeichnung, ''))) <> ''
			AND (ISNULL(ae.Zeichnung_N, '') <> ISNULL(ae.Zeichnung_O, '')
						OR ISNULL(ae.Zeich_Idx_N, '') <> ISNULL(ae.Zeich_Idx_O, '')
				)
			AND ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU) IS NOT NULL
			AND ae.InsertDate IS NOT NULL
			AND ae.InsertDate > ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU)
			)
BEGIN
	PRINT 'Exists...'
END
ELSE
BEGIN
	PRINT 'Not exists...'
END

Mais je croyais que je pouvais compter sur SQL-Server pour utiliser les bons Index ?!?
Je n'ai pas envie de toujours spécifier les Index.

Mon problème n'est pas d'optimiser ce script mais de connaitre les causes afin d'appliquer la/les solution/s à tout mes scripts.

=> Pourquoi le comportement n'est pas le même entre le "IF EXISTS" et le "SELECT COUNT" ?

[SQLpro]

Vos statistiques sont-elles à jour ?

A +

[Krison]

J'ai fait un rebuild de tous les Index sur les 3 tables utilisées
=> le script initial ne prend plus que "0 secondes"!

Je n ai pas tout de suite vérifié cela car de mémoire c'était géré dans les plans de maintenance (?1x semaine?)
Je suis en train de vérifier celà...

Mais ça ne m explique toujours pas la différence de comportement...

[Krison]

Vos statistiques sont-elles à jour ?

A +

De mémoire, elles devraient... via le système de Maintenance Plan (il y a un plan de maintenance le samedi soir qui fait toute une serie de maintenances dont notamment sur les index...)... en cours de vérification...

De plus, la base de donnée est configurée avec Auto Update Statistics ON
(par contre Auto Update Statistics ASync OFF!)


Edit/ Après vérification:
Le plan de maintenance a juste une tâche "Reorganize Index" (qu il a bien exécutée samedi...durant 04h51).
Une autre tâche "Update Statistics" était prévue...mais désactivée (depuis quand, par qui, pourquoi... ).
Dans les deux cas, il s agit de tâches générées via l'interface de "Ms Sql Server Management Studio".

Je vais essayer de trouver du temps pour revoir la stratégie au niveau des Rebuild Index/Reorg/Update Statistics...
Je compte m'inspirer de ceci <http://sqlpro.developpez.com/optimis...eIndexVLDB.pdf> (/SQLpro)

Je pense qu'il y aura le samedi : Rebuild Index sur certaines tables + Rebuild aléatoire + Update Statistic sur le reste
Et la nuit du mercredi un Update Statistics sur certaines tables
... ou alors la configuration Auto Update Statistics ASync devrait me garantir de bonnes statistiques ???

[Krison]

Enfin, il vaut mieux :
défragmenter les index si la fragmentation est de plus de 10% et moins de 30,
reconstruire les index si la fragmentation est de plus de 30%,

J'ai lancé un test la nuit passée qui me montre que la reconstruction des index est plus rapide que la défragmentation. => pourquoi ne pas toujours reconstruire les Index ?

Auto Update Statistics ASync : l'aide directe dans MsSQLServerManagementStudio ne spécifie pas que ce n'est valable que pour la version Enterprise. L'option reste accessible... Y a t il un risque d'erreur ?
PS:Au niveau de la reconstruction des index, utiliser l'option "Keep index online while reindexing" sur une version Standard génère une erreur! (écrit clairement dans la documentation que ce n est pas valable pour l'édition Standard)

[elsuket]

Bonjour,

Je viens de voir que la recommandation de Microsoft a changé, le seuil de défragmentation est passé à 5%.
Mais attention, c'est une recommandation

J'ai lancé un test la nuit passée qui me montre que la reconstruction des index est plus rapide que la défragmentation. => pourquoi ne pas toujours reconstruire les Index ?

Comme vous l'a dit SQLPro, si la fragmentation de l'index est élevée (+ de 30%), alors une reconstruction (ALTER INDEX ... REBUILD) sera plus rapide qu'une défragmentation (ALTER INDEX ... REORGANIZE).

Vous devez faire la différence entre les options REBUILD et REORGANIZE :

- REBUILD est une opération atomique, alors que REORGANIZE ne l'est pas.
Cela signifie que si vous arrêtez la requête de REBUILD en cours d'exécution, tout le travail du moteur de base de données est perdu. Ce n'est pas le cas de REORGANIZE.

- REORGANIZE ne fait que réorganiser le niveau feuille de l'index, et n'alloue ou ne désalloue aucune page. REBUILD reconstruit tous les niveaux de l'index, et se sert au besoin de nouvelles pages, de façon à réduire la fragmentation interne et externe de l'index. REORGANIZE ne fait que réduire la fragmentation logique de l'index, de sorte à minimiser si ce n'est éviter les aller-retours lors de la lecture de l'index.

- REBUILD peut être exécutée hors-ligne, et dans ce cas, on ne peut pas mettre à jour la table s'il s'agit d'un index cluster, et on ne peut pas changer les colonnes qui participent à la définition d'un index non-cluster tant que le REBUILD est en cours d'exécution.

REORGANIZE ne maintient pas ces verrous et vous permet de continuer à effectuer des mises à jour sur la table cible du REORGANIZE.

Si on exécute REBUILD en ligne (option ONLINE, quelques restrictions, notamment pour les colonnes LOB, spatiales, et calculées, et seulement dans l'édition Enterprise), deux index sont maintenus, et l'ancien est supprimé et remplacé par le nouveau à la fin de l'opération. Cela permet l'accès en écriture sur la table, mais l'opération est plus longue et plus coûteuse.
C'est nécessaire lorsque l'on a une base de données dont la disponibilité doit être 24 x 7 x 366.

PS:Au niveau de la reconstruction des index, utiliser l'option "Keep index online while reindexing" sur une version Standard génère une erreur! (écrit clairement dans la documentation que ce n est pas valable pour l'édition Standard)

C'est exact, comme je l'ai dit, cette fonctionnalité n'est disponible que sous les éditions Enterprise.

Y a t il un risque d'erreur ?

Oui, comme avec de nombreuses interfaces graphiques que SSMS propose.
Ou alors, elles sont parfois incomplètes ... comme les plans de maintenance !

Auto Update Statistics ASync : l'aide directe dans MsSQLServerManagementStudio ne spécifie pas que ce n'est valable que pour la version Enterprise.

C'est valable pour toute édition. Rien ne spécifie que cela ne s'applique qu'aux éditions Enterprise

Vous devriez faire un UPDATE STATISTICS avec l'option WITH COLUMNS une fois que vous avez effectué la maintenance des index.
Sans cela vous écrasez la qualité des statistiques des index qui sont construites sans échantillonnage lors de leur reconstruction.

Pour faciliter la lecture des plans de requête, vous ne deviez vous fier qu'aux plans d'exécution réels, et à un bon outil

@++

[Krison]

Ok et merci beaucoup!

...affaire classée!