Requêtes imbriquées & Isolation Level

**Glouferu** · 08/08/2011, 14h47

Bonjour à tous,

J'ai un problème avec une requête SQL imbriquée qui ne s’exécute jamais !
Après avoir étudié le plan d’exécution en détails, rajouté 2 3 index et mis à jour les Statistiques, mon problème de blocage est toujours présent !

Comme la requête provient d'un univers Business Object, je n'ai pas forcément étudié le SQL au premier abord.
Néanmoins, il intéressant de voir que cette requête possède une sous-requête :

Requête principal (qui fonctionne parfaitement de manière indépendante) :

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(423221 row(s) affected)
Table 'SCHEDULE'. Scan count 0, logical reads 1623507, physical reads 0,
Table 'PARTNER'. Scan count 0, logical reads 1082338, physical reads 0,
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
Table 'DTMPFO_IMPRESSIONS'. Scan count 4, logical reads 41732, physical reads 0,
Table 'POLICY_STATUS_ACTUAL'. Scan count 1, logical reads 7476, physical reads 0,
Table 'POLICY_STATUS'. Scan count 2, logical reads 4, physical reads 0,
Table 'POLICY'. Scan count 1, logical reads 72329, physical reads 0,
Table 'CALENDAR'. Scan count 1, logical reads 44, physical reads 0,

SQL Server Execution Times:
CPU time = 31719 ms, elapsed time = 43090 ms.

Requête secondaire qui s’exécute aussi parfaitement :

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(6001 row(s) affected)
Table 'SCHEDULE'. Scan count 0, logical reads 18387, physical reads 0,
Table 'POLICY'. Scan count 0, logical reads 18387, physical reads 0,
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
Table 'CALENDAR'. Scan count 1, logical reads 44, physical reads 0,
Table 'DTMPFO_IMPRESSIONS'. Scan count 1, logical reads 234279, physical reads 0,

SQL Server Execution Times:
CPU time = 1375 ms, elapsed time = 1437 ms.

D'après mon expertise

, la requête imbriquée devrait fonctionner sans problème ! Je ne comprends pas d’où provient le problème !!!???

Quand j’exécute cette dernière, je ne constate pas de temps d'attente suspect ... Je me demande si ce n'est pas un problème d'isolation, pourtant je n'ai jamais eu de problème à ce niveau la !

Qu'en pensez-vous ?

Par avance,
Merci de vos réponses.

P.S.: Ce n'est pas la première fois que je constate des problèmes avec des sous-requêtes dont le SQL est généré par Deski. Il est certainement possible de revoir la logique de la requête (univers), mais cette requête devrait sortir ...

Config :
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Enterprise Edition (64-bit) on Windows NT 5.2 <X64>
(Build 3790: Service Pack 2).

Ressource Gouvernor configuré !

**aieeeuuuuu** · 08/08/2011, 16h56

Bonjour,

peut-on voir la requête en question ?

Avez regardé s'il ne manquait pas d'index pour cette requête ?

**Glouferu** · 08/08/2011, 17h29

Bonjour aieeeuuuuu,

Je suis entrain d'étudier un peu plus le plan d'execution, je constate beaucoup de NESTED LOOPS en fin de requête. C'est peut-être une piste.
Je cherche aussi à placer des HINTS dans ma requête voir si je peux améliorer les choses ...

--> Le plan d’exécution de la requête se trouve ici !

La requête SQL fait un peu peur ... :

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SELECT DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_TYPE,CASE
                                                     WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME='' THEN NULL
                                                     ELSE upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME)
                                                 END,CASE
                                                         WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL THEN CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME)
                                                         ELSE CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME)
                                                     END,CASE
                                                             WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE IS NULL AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY THEN POLICY.CD_SCHEDULE
                                                             ELSE SCHEDULE.CD_SCHEDULE
                                                         END,SUM(DTMPFO.dbo.DTMPFO_IMPRESSIONS.FG_RENEW),PARTNER.CD_PARTNER,PARTNER.LB_PARTNER
FROM CALENDAR
RIGHT OUTER JOIN DTMPFO.dbo.DTMPFO_IMPRESSIONS ON (CALENDAR.LB_DAY=CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME))
LEFT OUTER JOIN(SELECT [ITEMCODE],[GROUPCODE],[DESCRIPTION]
                FROM dbo03.[ODS].[dbo].[ODS_ENS_DECODE]
                WHERE [GROUPCODE]='reqstat') DECODE_REQSTAT ON (upper(DECODE_REQSTAT.ITEMCODE)=upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.STATUS))
LEFT OUTER JOIN POLICY ON (DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY)
LEFT OUTER JOIN PARTNER ON (POLICY.CD_PARTNER=PARTNER.CD_PARTNER)
LEFT OUTER JOIN POLICY_STATUS_ACTUAL ON (POLICY_STATUS_ACTUAL.CD_POLICY=POLICY.CD_POLICY)
LEFT OUTER JOIN POLICY_STATUS ON (POLICY_STATUS.CD_POLICY_STATUS=POLICY_STATUS_ACTUAL.CD_POLICY_STATUS)
LEFT OUTER JOIN SCHEDULE ON (SCHEDULE.CD_SCHEDULE=DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE)
WHERE (DECODE_REQSTAT.DESCRIPTION IN('Imprimé','Terminé') AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_TYPE IN('CN1','RN1','RN2','RN3') AND CASE
                                                                                                                                            WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE IS NULL AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY THEN POLICY.CD_SCHEDULE
                                                                                                                                            ELSE SCHEDULE.CD_SCHEDULE
                                                                                                                                        END IN(SELECT CASE
                                                                                                                                                          WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE IS NULL AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY THEN POLICY.CD_SCHEDULE
                                                                                                                                                          ELSE SCHEDULE.CD_SCHEDULE
                                                                                                                                                      END
                                                                                                                                               FROM CALENDAR
                                                                                                                                               RIGHT OUTER JOIN DTMPFO.dbo.DTMPFO_IMPRESSIONS ON (CALENDAR.LB_DAY=CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME))
                                                                                                                                               LEFT OUTER JOIN(SELECT [ITEMCODE],[GROUPCODE],[DESCRIPTION]
                                                                                                                                                               FROM dbo03.[ODS].[dbo].[ODS_ENS_DECODE]
                                                                                                                                                               WHERE [GROUPCODE]='reqstat') DECODE_REQSTAT ON (upper(DECODE_REQSTAT.ITEMCODE)=upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.STATUS))
                                                                                                                                               LEFT OUTER JOIN POLICY ON (DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY)
                                                                                                                                               LEFT OUTER JOIN SCHEDULE ON (SCHEDULE.CD_SCHEDULE=DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE)
                                                                                                                                               WHERE ((CASE
                                                                                                                                                           WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME='' THEN NULL
                                                                                                                                                           ELSE upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME)
                                                                                                                                                       END IN('47_CNA_A4','47_RNA_A4','47_RNB_A4') OR CASE
                                                                                                                                                                                                          WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME='' THEN NULL
                                                                                                                                                                                                          ELSE upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME)
                                                                                                                                                                                                      END IN('47D_CNA_A4','47D_RNA_A4','47D_RNB_A4','47V_CNA_A4','47V_RNA_A4','47V_RNB_A4') AND CASE
                                                                                                                                                                                                                                                                                                    WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL THEN CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME)
                                                                                                                                                                                                                                                                                                    ELSE CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME)
                                                                                                                                                                                                                                                                                                END NOT BETWEEN '04/01/2010 00:0:0' AND '04/13/2010 00:0:0') AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_RENEW_THEO BETWEEN '2011-07-01' AND '2011-07-31' AND DECODE_REQSTAT.DESCRIPTION IN('Imprimé','Terminé'))) AND POLICY_STATUS.CD_POLICY_STATUS!='Z' AND CASE
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL THEN CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ELSE CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        END>='2011-02-01')
GROUP BY DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_TYPE,CASE
                                                       WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME='' THEN NULL
                                                       ELSE upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME)
                                                   END,CASE
                                                           WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL THEN CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME)
                                                           ELSE CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME)
                                                       END,CASE
                                                               WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE IS NULL AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY THEN POLICY.CD_SCHEDULE
                                                               ELSE SCHEDULE.CD_SCHEDULE
                                                           END,PARTNER.CD_PARTNER,PARTNER.LB_PARTNER
HAVING (SUM(DTMPFO.dbo.DTMPFO_IMPRESSIONS.FG_RENEW)=1)

**aieeeuuuuu** · 08/08/2011, 18h21

Je ne pense pas que poser des hint soit une bonne idée, et cela risque probablement d’être pire que mieux !

je n'ai pas approfondi sur la requête, mais je pense que vous devriez commencer par (ce ne sont que des pistes...) :

- Vérifier que votre critères de recherche sont sargables, surtout en ce qui concerne les tables DTMPFO_IMPRESSIONS, POLICY, POLICY_STATUS et POLICY_STATUS_ACTUAL. rien que cette partie represente pres de 90% du cout de la requete. peut etre pouvez vous envisager des vues indexees...
- qu'il ne manque pas d'index
- remplacer vos IN par des jointures internes
- vérifier le type de vos colonnes. Vous effectuez un grand nombre de transtypage, y compris dans des jointures !
- il en va de même pour les uppers en grand nombre (dans des jointures aussi) qui pourraient surement être évités

quel est le but de ceci :

Code SQL :

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

1
2
 
WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL

**Glouferu** · 09/08/2011, 10h08

Bonjour,

Ce matin, la requête met 25 secondes et s’exécute normalement ! :

Je n'ai strictement rien fait... les plans de maintenance s’exécutent le dimanche ! Ma base a l'option "Auto Update Statistics" !
Il n'y avait aucune charge sur le serveur hier après midi...

Je ne comprends vraiment pas comment c'est possible !?

As-tu une idée Aiiieuuu ?

Merci

**aieeeuuuuu** · 09/08/2011, 10h23

Bonjour

Avez vous vérifié si le plan d’exécution avait changé ?

**Glouferu** · 09/08/2011, 10h41

Effectivement le plan de maintenance a changé. Le Server SQL me propose maintenant de rajouter un nouvel Index.

Nouveau plan d’exécution : ici

**elsuket** · 09/08/2011, 14h33

Bonjour,

Regardez quand (colonne last_update) ont été mises à jour les statistiques de la table à l'aide du script que j'ai publié ici.
Le script est sans risque et ne consomme que très peu de ressources.

Au vu de votre plan de requête, il doit manquer un index sur les colonnes CD_POLICY et CD_POLICY_STATUS de la table POLICY_STATUS_ACTUAL.

L'index suggéré par le moteur n'est pas mal non plus, mais vous devez tester si l'INCLUDE est requis.

La jointure suivante est horrible :

Code :

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

1
2
FROM
  CALENDAR RIGHT OUTER JOIN DTMPFO.dbo.DTMPFO_IMPRESSIONS ON (CALENDAR.LB_DAY=cast(convert(varchar(10), DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST, 112) as datetime)

De même que le CASE qui suit ... probablement une conception de base de données non faite ou fausse

@++

**Glouferu** · 09/08/2011, 15h22

Bonjour elsuket,

J'étais justement sur ton blog lol ! Le monde est petit ...

Je viens d’exécuter ton script et voici ce qu'il me donne :

table_or_view_name - last_update - density - stat_column_list
POLICY_STATUS_ACTUAL - 2011-02-28 18:05:00.000 - 1.000 - CD_POLICY
POLICY_STATUS_ACTUAL - 2011-06-01 07:22:00.000 - 0.000 - CD_POLICY_STATUS
POLICY_STATUS_ACTUAL - 2011-06-01 09:40:00.000 - 0.009 - DT_STATUS

Je pense donc que je rentre dans le fameux cas ou l'option "Auto update Statistics" ne fonctionne pas ... ou pas totalement puisque l'option est activée sur ma base

-> Je vais modifier mes plans de maintenance pour mettre à jour les statistiques de ces bases !

Merci de votre aide à tous, je pense que sur ce coup, j'ai pu vraiment capitaliser de mon erreur

P.S.: Malheureusement je ne suis pas à l'origine de la requête mais je ne manquerais pas de remonter les infos

**elsuket** · 10/08/2011, 08h01

Je pense donc que je rentre dans le fameux cas ou l'option "Auto update Statistics" ne fonctionne pas ... ou pas totalement puisque l'option est activée sur ma base

Attention : je n'ai pas écrit qu'elle ne fonctionne pas.
La règle pour le ré-échantillonnage des statistiques de colonnes est la suivante :

- pour une table de 6 lignes au plus, recalcul des statistiques toutes les 6 modifications
- pour une table de 7 à 500 lignes recalcul des statistiques toutes les 500 modifications
- pour une table de plus de 500 lignes recalcul des statistiques toutes les 500 modifications + 20% du nombre de lignes de la table.

Pour ce dernier élément, vous comprenez que plus la table contient de lignes, moins les statistiques sont ré-échantillonnées fréquemment.
C'est normal puisque cela consomme du CPU et la lecture d'un paquet de pages de la table.
Mais les volumes de données ayant explosé entre le moment où cet algorithme a été écrit et maintenant, je suis convaincu qu'il est obsolète.
C'est ce qui nous oblige à forcer le ré-échantillonnage des statistiques régulièrement et aux heures de faible trafic à l'aide de l'instruction UPDATE STATISTICS.

Il vous faut donc savoir à quelle date vous avez exécuté la requête et qu'elle s'est terminée en quelques secondes, et même chose dans le cas contraire.
Si les dates que vous donnez ci-dessous ne correspondent pas à la date à laquelle la requête s'est exécutée en quelques secondes, c'est qu'un autre table a subi une mise à jour de ses statistiques...

Pour le reste, si le rowmodctr (row modification counter) est plus grand que le SRT, il faut ré-échantillonner les statistiques

@++

**Glouferu** · 10/08/2011, 14h24

Envoyé par elsuket

si le rowmodctr (row modification counter) est plus grand que le SRT, il faut ré-échantillonner les statistiques

Je viens de vérifier mes valeurs et j'ai de gros problèmes de statistiques sur mes tables les plus grosses :

exemple d'une table qui a 2.787.602.435 lignes
350Giga de Data Space
7.3 Giga d'Index Space

Sur des tables si grosses, vous me conseillez un pourcentage d'échantillonnage à appliquer en particulier ??
Car visiblement l’échantillonnage automatique sur 20% des données ne donne pas de bon résultat !

Merci à tous pour votre aide

**elsuket** · 10/08/2011, 14h43

Effectivement une table contenant 2.787.602.435 lignes est assez volumineuse.
Généralement l’échantillonnage par défaut convient ...
Parfois FULLSCAN est nécessaire ...
Cette table est-elle partitionnée ? Et si oui, les index sont-ils alignés sur le schéma de partition ?

@++

**Glouferu** · 10/08/2011, 15h03

Effectivement, ces tables commencent à être assez volumineuses. C'est un sacré challenge (en tout cas pour moi

).

Dans le TOP 3 des plus grosses tables :

1. Pas de partitionnement
2 et 3 sont partitionnées selon 10 Filegroups ventilés par année fiscal

Le fullscan avait duré 25h la dernière fois, j'ai du mal à trouver une plage horaire pour l'execution de ce plan de maintenance

**elsuket** · 10/08/2011, 15h11

Comme je vous l'ai dit le FULLSCAN est loin d'être obligatoire.
Dans la plupart des cas, l’échantillonnage par défaut suffit.
Le FULLSCAN doit être fait au cas par cas, c'est à dire quand on a constaté que c'est la seule façon qu'il y a de faire s’exécuter une requête correctement.

@++

**Glouferu** · 10/08/2011, 15h21

Envoyé par elsuket

Cette table est-elle partitionnée ? Et si oui, les index sont-ils alignés sur le schéma de partition ?

Si les index sont antérieur au partitionnement, il faudrait alors les recréer pour qu'ils se ré-alignent avec le partitionnement?

Peux-tu m'en dire plus? Je ne comprends pas le sens de ta phrase

P.S.: Désolé de t'embêter avec toutes mes questions, je n'ai jamais eu la chance de rencontrer un vrai DBA lol

edit : je suis entrain de lire les consignes spéciales pour les index partitionnés.

**elsuket** · 10/08/2011, 15h37

P.S.: Désolé de t'embêter avec toutes mes questions, je n'ai jamais eu la chance de rencontrer un vrai DBA lol

Tu n'as pas à t'excuser : ce forum est justement fait pour poser des questions et que chacun partage ce qu'il sait comme les problèmes qu'il rencontre.

Si les index sont antérieur au partitionnement, il faudrait alors les recréer pour qu'ils se ré-alignent avec le partitionnement?

Non, surtout ne pas les toucher.
On dit que les index sont alignés sur le schéma de partition lorsque ceux-ci suivent le schéma de partition.
Supposons que j'ai une table qui est partitionnée par année, et que ma clé primaire soit justement sur la date + heure de cette table.
L'ajout de la contrainte de clé primaire s'écrit alors :

Code :

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

1
2
3
4
ALTER TABLE dbo.maTable
ADD CONSTRAINT PK_constraint PRIMARY KEY CLUSTERED (colonnes)
ON schemaDePartition(colonneCléDePartition)
GO

Si en revanche on ne spécifie de partitionnement pour les index (absence de la clause ON schemaDePartition ci-dessus), on dit qu'ils ne sont pas alignés sur le schéma de partition.

@++

**Glouferu** · 10/08/2011, 17h38

Je me bats encore avec le tuning d'une requête imbriquée utilisant un NOT IN cette fois ...
Visiblement cette instruction n'utilise pas les Index. Il en résulte un :

Nested Loops - Left Anti Semi Join : Cost 69% ...

Effectivement, j'avais partitionné ces tables assez volumineuses par année fiscale et j'avais utilisé une Date (converti en Integer dans un flux ETL) comme fonction de partition.

Du coup, j'ai lu pas mal de choses sur les index alignés ou non.
Apparemment dans certain cas, ils sont contre productif ...

Dans tous les cas, je dois t'avouer que j'ai au premier abord pensé que mes index n'étaient pas alignés. Quand je faisais CREATE TO > Script sur mes Index, je ne trouvais pas l'instruction

ON Schema_Partoche(ChampDePartition)

Du coup j'ai supprimé un Index Non Clustered et recréé avec l'alignement qui va bien. SSMS me générait encore un script sans l'alignement !
En cherchant un peu, j'ai pu trouvé une option Storage cachée dans les propriétés de mes Index qui me confime que mes index sont bien alignés avec mon schéma de partition

Que de péripéties

**elsuket** · 11/08/2011, 11h07

Bien. En revanche ne te fie pas toujours au pourcentage associé à un opérateur du plan de requête : il donne le coût relatif en CPU de chaque opérateur, mais pas le coup en opérations disque.
Or, au vu de tes statistiques IO (ton premier post), il est clair que c'est là le problème.

Peux-tu nous donner :

- le script complet de la table, avec ses index et son partitionnement
- le nouveau plan de requête réel (CTRL+M avant d'exécuter la requête sous SQL Server Management Studio)

@++

**Glouferu** · 11/08/2011, 17h52

Salut elsuket,

J'avais effectivement remarqué que le pourcentage de coût était globale sur la requête, puisqu'en dissociant la sous-requête d'une requête, on voit bien les chiffres varier sans explications apparente. Par contre chez moi le CTRL+M ne fonctionne apparemment pas. Le CTRL+L m'affiche le plan d’exécution.

Bon sinon j'ai pas mal avancé sur mon problème... j'ai découvert des choses fantasmagoriques sur ma deuxième requête imbriquée avec le NOT IN

Imagine 2 bases de données :

A
A_VUE

Dans la base A, tu as une table qui s’appelle : DTMPFO_MVT_PORTFOLIO (structure, partitionnnée selon 10 filegroups)
Dans la base A_VUE, tu as une vue qui s'appelle : DTMPFO_MVT_PORTFOLIO (Il n'est pas possible d'utiliser l'instruction "WITH SCHEMABINDING" car la vue est composée de plusieurs bases de données différentes)

Par contre, le plan d’exécution de la vue m'a indiqué qu'il manquait un index sur la table DTMPFO_MVT_PORTFOLIO ! Je l'ai donc rajouté ...
Cette table possède donc :

1 index Clustered (il y a trop de clé à mon gout mais je n'y peux pas grand chose)
2 index Non Clustered (Un index pour optimiser la vue et un autre pour une requête bien précise)

Voici les stats avec la requête quand je supprime le NOT du NOT IN :

Table 'DTMPFO_MVT_PORTFOLIO'. Scan count 111216, logical reads 1262569, physical reads 10479, read-ahead reads 665267,

Ah c'est moche ... ohhh oui c'est bien moche !

Surtout que la requete fait des physical reads et des read-ahead reads

Structure de la table ici avec les Index !
Vue de la table ici --> Y a trop CAST et de CONVERT ... c'est le Dawa

Je pense que demain, je vais récupérer le contenu de la vue et calculer les différents champs avec notre ETL pour voir si cela simplifie et allège l'utilisation CPU ... etc

**elsuket** · 12/08/2011, 08h55

Par contre chez moi le CTRL+M ne fonctionne apparemment pas. Le CTRL+L m'affiche le plan d’exécution.

Attention :

- CTRL + L donne le plan d'exécution estimé, c'est-à dire que SQL Server se base sur les statistiques de colonne des tables pour tenter de prédire ce que va faire SQL Server quand il exécutera la requête.
Donc la requête n'est pas exécutée !

- CTRL + M donné le plan d'exécution réel, c'est donc à dire que le plan de requête ne peut apparaître qu'après la fin de l'exécution de celle-ci.

Si on constate une différence importante dans les cardinalités estimées et réelles dans le plan d'exécution réel, il est possible que le problème se résolve simplement en effectuant une mise à jour des statistiques, sur la table ou vue indexée qui précède l'erreur de cardinalité, puis sur la table qui a le problème de cardinalité.

Par contre, le plan d’exécution de la vue m'a indiqué qu'il manquait un index sur la table DTMPFO_MVT_PORTFOLIO ! Je l'ai donc rajouté ...

Ta démarche n'est pas critiquable, mais il est malheureusement bien connu que les index recommandés par l'optimiseur de requêtes ne sont souvent pas les meilleurs.
Regarde ce que j'ai écrit ici en conclusion

La création d'un index doit toujours être testée sur une base de données qui correspond à une copie récente de la base de données de production

Surtout que la requete fait des physical reads et des read-ahead reads

Si cette requête est exécutée une fois par jour et que la quantité de RAM disponible sur le serveur est plus petite que la base de données, c'est peut-être normal : les pages ne sont pas dans le cache, donc il faut aller les chercher sur le disque.
Cela dit, au vu du nombre de pages que ta requête lit, c'est effectivement anormal.

Ici c'est le scan count qui est inquiétant : il indique qu'un index de la table DTMPFO_MVT_PORTFOLIO a été traversé 111216 fois.
On peut donc lourdement suspecter un key lookup : le résultat précédent procure 111216 lignes, pour lesquelles il faut aller chercher pour chacune d'entre-elles une valeur dans la table, dans une colonne qui ne fait pas partie de l'index avec lequel les 111216 lignes sont ramenées initialement.

Structure de la table ici avec les Index !

Le premier problème c'est qu'il y a à priori un trop grand nombre de colonnes pour cette table ... ce qui implique que la conception a été négligée, et tu en paies les frais ...

Vue de la table ici --> Y a trop CAST et de CONVERT ... c'est le Dawa

Je dirai que tant qu'un CAST() ou un CONVERT() ne se trouve ni dans un prédicat de jointure, ni dans une clause de filtrage (WHERE/AND et HAVING), ce n'est pas un problème.
Ceci parce que SQL Server maintient des statistiques sur les valeurs qui sont dans les colonnes et qu'il ne peut pas deviner que l'utilisateur va les transformer : comme il lui est impossible de prédire les valeurs qui vont "sortir" du transtypage ou du formatage de la donnée, il est obligé de lire toute la table (ou si toutes les colonnes de la table sont dans un index, de lire tout l'index).
En revanche, si la transformation apparaît entre le SELECT et le FROM et pas dans une sous-requête comme je l'ai écrit plus haut, seul du temps CPU sera consommé.

Je vais regarder tes tables et leur index avec le plan dans un moment

Mais ici ce n'est clairement pas la consommation de CPU qui est en cause.
T'es-t-il possible de collecter le plan d'exécution réel ? Je ne peux pas de donner de pistes précises sans celui-ci ...

@++

Requêtes imbriquées & Isolation Level

Développement SQL Server

Discussions similaires

Partager

Partager