IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration SQL Server Discussion :

Etrange problème de performances [2016]


Sujet :

Administration SQL Server

  1. #1
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 144
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 144
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut Etrange problème de performances
    Bonjour,

    Je suis face à un problème que je n'arrive pas à comprendre.

    Voici une requête :
    Code sql : 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
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    248
    249
    250
     
    SELECT top 11 
    	TE_ISI_UP.Artikelbez,
    	TE_ISI_UP.Menge,
    	TE_ISI_UP.ID,
    	TE_ISI_UP.ID_FI,
    	TE_ISI_UP.ID_AU,
    	TE_ISI_UP.F7001,
    	TE_ISI_UP.F7024,
    	TE_ISI_AU.AufDat,
    	TE_ISI_AU.ID,
    	TE_ISI_FI.Firma,
    	TE_ISI_FI.Ort,
    	TE_ISI_FI.FTZ,
    	TE_ISI_FI.Ftz1,
    	TE_ISI_FI.Ftz2,
    	TE_ISI_FI.ID,
    	TE_ISI_FI.F5031 
    FROM TE_ISI_UP 
    LEFT OUTER JOIN TE_ISI_AU ON 
    (
    		TE_ISI_UP.ID_AU<>0 
    	AND TE_ISI_AU.ID=TE_ISI_UP.ID_AU 
    	AND (TE_ISI_AU.LosKZ=0)
    )
    LEFT OUTER JOIN TE_ISI_FI ON 
    (
    		TE_ISI_UP.ID_FI=TE_ISI_FI.ID 
    	AND (TE_ISI_FI.LosKZ=0)
    ) 
    WHERE TE_ISI_UP.LosKZ=0 
    AND (TE_ISI_UP.MNO IN(0,8003,8100,9000)) 
    AND 
    (
    	(
    		TE_ISI_UP.ID_FI=0 
    		OR 
    		(
    			TE_ISI_UP.ID_FI<>0 
    			AND EXISTS
    			(
    				SELECT ID 
    				FROM TE_ISI_FI 
    				WHERE TE_ISI_UP.ID_FI=ID 
    				AND 
    				(
    						TE_ISI_FI.LosKZ=0 
    					AND	(TE_ISI_FI.MandNr IN(0,8003,8100,9000)) 
    					AND 
    					(
    						(
    							(
    									((TE_ISI_FI.Fityp=2 OR TE_ISI_FI.Fityp2=2 OR TE_ISI_FI.Fityp3=2 OR TE_ISI_FI.Fityp4=2 OR TE_ISI_FI.Fityp5=2)) 
    								 OR (TE_ISI_FI.Fityp2=2) 
    								 OR (TE_ISI_FI.Fityp3=2) 
    								 OR (TE_ISI_FI.Fityp4=2) 
    								 OR (TE_ISI_FI.Fityp5=2)
    							)
    						) 
    						AND 
    						(
    							EXISTS
    							(
    								SELECT ID 
    								FROM TE_ISI_SB 
    								WHERE (TE_ISI_FI.ID=TE_ISI_SB.ID_FI) 
    								AND TE_ISI_SB.LosKZ=0 
    								AND 
    								(
    									EXISTS
    									(
    										SELECT ID 
    										FROM TE_ISI_ID 
    										WHERE TE_ISI_SB.BearbeiterId<>0 
    										AND TE_ISI_ID.ID=TE_ISI_SB.BearbeiterId 
    										AND 
    										(
    												TE_ISI_ID.Oeffentlich=1
    											 OR TE_ISI_ID.MandNr IN(0,8003,8100,9000)
    										)
    										AND TE_ISI_ID.LosKZ=0 
    										AND 
    										(
    											(
    												(
    														TE_ISI_ID.HierarchyCode LIKE '292.2.%' 
    													 OR TE_ISI_ID.HierarchyCode='-1'
    												)
    											)
    										)
    									)
    								)
    							)
    						)
    						OR
    						(
    							(
    								(
    									(
    											TE_ISI_FI.Fityp!=2 
    										AND TE_ISI_FI.Fityp2!=2 
    										AND TE_ISI_FI.Fityp3!=2 
    										AND TE_ISI_FI.Fityp4!=2 
    										AND TE_ISI_FI.Fityp5!=2
    									)
    								)
    								AND
    								(
    									(
    											TE_ISI_FI.Fityp!=0
    										 OR TE_ISI_FI.Fityp2!=0 
    										 OR TE_ISI_FI.Fityp3!=0 
    										 OR TE_ISI_FI.Fityp4!=0 
    										 OR TE_ISI_FI.Fityp5!=0
    									)
    								)
    							)
    							OR
    							(
    									(TE_ISI_FI.Fityp2!=2) 
    								AND (TE_ISI_FI.Fityp2!=0)
    							)
    							OR 
    							(
    									(TE_ISI_FI.Fityp3!=2) 
    								AND (TE_ISI_FI.Fityp3!=0)
    							) 
    							OR 
    							(
    									(TE_ISI_FI.Fityp4!=2) 
    								AND (TE_ISI_FI.Fityp4!=0)
    							)
    							OR 
    							(
    									(TE_ISI_FI.Fityp5!=2) 
    								AND (TE_ISI_FI.Fityp5!=0)
    							)
    						)
    					)
    					AND
    					(
    						(
    							(
    								(
    									(
    											TE_ISI_FI.ID>=4294967296
    										AND TE_ISI_FI.ID<8589934592
    									)
    								)
    								AND
    								(
    									(TE_ISI_FI.ID % CAST(4294967296 AS BIGINT))=1794
    								)
    							)
    							OR (TE_ISI_FI.F6013=1) 
    							OR 
    							(
    								(
    									(
    										(
    												TE_ISI_FI.Fityp!=3 
    											AND TE_ISI_FI.Fityp2!=3 
    											AND TE_ISI_FI.Fityp3!=3 
    											AND TE_ISI_FI.Fityp4!=3 
    											AND TE_ISI_FI.Fityp5!=3
    										)
    									)
    									AND
    									(
    										(
    												TE_ISI_FI.Fityp!=0
    											 OR TE_ISI_FI.Fityp2!=0 
    											 OR TE_ISI_FI.Fityp3!=0 
    											 OR TE_ISI_FI.Fityp4!=0 
    											 OR TE_ISI_FI.Fityp5!=0
    										)
    									)
    								)
    								OR
    								(
    										(TE_ISI_FI.Fityp2!=3)
    									AND (TE_ISI_FI.Fityp2!=0)
    								)
    								OR
    								(
    										(TE_ISI_FI.Fityp3!=3) 
    									AND (TE_ISI_FI.Fityp3!=0)
    								)
    								OR
    								(
    										(TE_ISI_FI.Fityp4!=3) 
    									AND (TE_ISI_FI.Fityp4!=0)
    								)
    								OR
    								(
    										(TE_ISI_FI.Fityp5!=3)
    									AND (TE_ISI_FI.Fityp5!=0)
    								)
    							)
    						)
    						OR
    						(
    							(
    								(
    										TE_ISI_FI.Fityp=3
    									 OR TE_ISI_FI.Fityp2=3 
    									 OR TE_ISI_FI.Fityp3=3 
    									 OR TE_ISI_FI.Fityp4=3 
    									 OR TE_ISI_FI.Fityp5=3
    								)
    							)
    							OR (TE_ISI_FI.Fityp2=3) 
    							OR (TE_ISI_FI.Fityp3=3) 
    							OR (TE_ISI_FI.Fityp4=3) 
    							OR (TE_ISI_FI.Fityp5=3)
    						)
    						AND
    						(
    							EXISTS
    							(
    								SELECT ID 
    								FROM TE_ISI_C027 
    								WHERE TE_ISI_C027.ID_FI=TE_ISI_FI.ID 
    								AND (TE_ISI_C027.MNO IN(0,8003,8100,9000)) 
    								AND TE_ISI_C027.DEL=0 
    								AND ((TE_ISI_C027.MNO=8003))
    							)
    						)
    					)
    				)
    			)
    		)
    	)
    	AND
    	(
    		EXISTS
    		(
    			SELECT ID 
    			FROM TE_ISI_AU 
    			WHERE TE_ISI_UP.ID_AU<>0 
    			AND TE_ISI_AU.ID=TE_ISI_UP.ID_AU 
    			AND 
    			(
    					TE_ISI_AU.LosKZ=0 
    				AND (TE_ISI_AU.MNO IN(0,8003,8100,9000))
    			)
    		)
    	)
    )
    ORDER BY TE_ISI_AU.AufDat DESC

    Je vous la montre pour la postérité, malheureusement je n'ai pas la main sur elle, puisqu'elle est générée automatiquement par un logiciel.

    Historiquement, cette requête tournait sur un SQL Server 2014 Standard.
    Elle était particulièrement lente (environ 40 secondes pour charger les 11 premières lignes, 60 secondes si je la relançait en demandant les 21 premières lignes)
    Lorsque je la lançais dans Management Studio, ce dernier me proposait la création d'un index.
    J'ai créé l'index en question, et miracle de la vie, la requête est devenue instantanée (moins de 5 secondes).

    Ensuite, on a migré le programme de serveur.
    J'ai backupé la base telle quelle, puis je l'ai restaurée sur un serveur moindrement dimensionné SQL Server 2016 Developpeur.
    Je n'ai rien tuné/optimisé.
    La requête lancée sur le nouveau serveur tournait dans le même ordre de temps.

    Enfin, j'ai migré la base sur le nouveau serveur de PROD, de ma même manière : restauration du backup de 2014 un nouveua serveur 2016 Standard, dimensionné à l'identique du 2014.

    La requête est aussi lente qu'à l'origine, avant création de l'index.

    L'index est bien toujours là.

    J'ai lancé un script proposé par SQL Pro pour recalculer/reconstruire les index pollués :
    Code sql : 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
    59
    60
    61
    62
    63
    64
    65
    go
    SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON;
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @partitioncount bigint;
    DECLARE @schemaname nvarchar(130); 
    DECLARE @objectname nvarchar(130); 
    DECLARE @indexname nvarchar(130); 
    DECLARE @partitionnum bigint;
    DECLARE @partitions bigint;
    DECLARE @frag float;
    DECLARE @command nvarchar(4000); 
    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
    -- and convert object and index IDs to names.
    SELECT
        object_id AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag
    INTO #work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 and page_count > 1000 and alloc_unit_type_desc <> 'LOB';
     
    -- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
     
    -- Open the cursor.
    OPEN partitions;
     
    -- Loop through the partitions.
    WHILE (1=1)
        BEGIN;
            FETCH NEXT
               FROM partitions
               INTO @objectid, @indexid, @partitionnum, @frag;
            IF @@FETCH_STATUS < 0 BREAK;
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM sys.objects AS o
            JOIN sys.schemas as s ON s.schema_id = o.schema_id
            WHERE o.object_id = @objectid;
            SELECT @indexname = QUOTENAME(name)
            FROM sys.indexes
            WHERE  object_id = @objectid AND index_id = @indexid;
            SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;
     
    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
            IF @frag < 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
            IF @frag >= 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
            IF @partitioncount > 1
                SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
            EXEC (@command);
            PRINT N'Executed: ' + @command;
        END;
     
    -- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;
     
    -- Drop the temporary table.
    DROP TABLE #work_to_do;
    GO

    Rien n'y fait. C'est ultra lent.

    Dans Management Studio, je n'ai aucune proposition d'index.

    J'ai tenté de faire un tuning advisor sur la requête, idem, il ne me propose absolument aucune optimisation.

    Comment c'est possible ?
    Comment résoudre mon problème ?

    Le serveur est dimensionné tout à fait "largement" : 24 Go de mémoire pour une base de 27 Go (dont les 3/4 sont des données "mortes").
    Lorsque la requête tourne, 2 coeurs sont utilisés sur les 4.

    Je viens de tenter un REBUILD de tous les index de la base, sans aucun effet.
    Je suis en train de mettre à jour les statistiques de toutes les tables... je doute que ça change grand chose

    -- Edit : Indentation de la requête (mais bon, elle reste toujours aussi imbittable)
    -- Edit : La mise à jour des stats change rien, comme prévu
    On ne jouit bien que de ce qu’on partage.

  2. #2
    Membre habitué Avatar de olivtone
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Octobre 2010
    Messages
    242
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Eure et Loir (Centre)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2010
    Messages : 242
    Points : 153
    Points
    153
    Par défaut
    Hello

    j'ai eu le meme souci sur un serveur migré sur SQL 2016, je me suis arraché les cheveux dessus.
    Les requetes qui étaient sur un ancien serveur sous SQL 2012 était plus performante que le SQL 2016
    A chaque requete lancé, je m'etais apercu que tous les CPU montaient en fléche etc...

    J'ai activé le traceflag 9481 :

    9481 Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version)
    of the cardinality estimator when creating the query plan.

    Les performances se sont nettement améliorés par la suite

    Voila j'espere que ca t'aidera

  3. #3
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 716
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    Autre solution un optimize for unknown voire un recompile....

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  4. #4
    Expert confirmé
    Avatar de rudib
    Homme Profil pro
    Fakir SQL Server & NoSQL
    Inscrit en
    Mai 2006
    Messages
    2 573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Fakir SQL Server & NoSQL

    Informations forums :
    Inscription : Mai 2006
    Messages : 2 573
    Points : 4 043
    Points
    4 043
    Par défaut
    Bonjour,

    Si les suggestions proposées n'améliorent pas la situation, peux-tu poster en pièce jointe le plan d'exécution REALISE en xml, extension .sqlplan. Tu peux le récupérer en faisant un clic droit sur le plan graphique dans SSMS (plan REALISE, je répète, après exécution de la requête) et en affichant le plan en xml, en le sauvegardant ensuite.
    Rudi Bruchez
    Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
    LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
    LIVRES : Optimiser SQL Server -
    Microsoft SQL Server 2012 Security Cookbook
    - les bases de données NoSQL

    e-learning : LinkedIn Learning - Pluralsight

  5. #5
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 144
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 144
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Bon, c'est bon, laissez tomber, je vous fais perdre votre temps...

    Il y avait un bug dans le logiciel et la requête était différente... Désolé
    On ne jouit bien que de ce qu’on partage.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Problème de performance avec LEFT OUTER JOIN
    Par jgfa9 dans le forum Requêtes
    Réponses: 6
    Dernier message: 17/07/2005, 14h17
  2. [jeu]problème de performance d'un algo
    Par le Daoud dans le forum Algorithmes et structures de données
    Réponses: 12
    Dernier message: 30/05/2005, 17h07
  3. [C#] Probléme de performance avec IsDbNull
    Par jab dans le forum Windows Forms
    Réponses: 8
    Dernier message: 04/04/2005, 12h39
  4. [oracle 9i][Workbench]Problème de performance
    Par nuke_y dans le forum Oracle
    Réponses: 6
    Dernier message: 03/02/2005, 18h38
  5. [ POSTGRESQL ] Problème de performance
    Par Djouls64 dans le forum PostgreSQL
    Réponses: 6
    Dernier message: 26/05/2003, 17h18

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo