je suis confronté à de mauvaises performances d'une procédure stockée
et je soupçonne les index automatiques d'en être la cause. La procédure
charge 30 000 lignes dans une table, initialement vide, et retravaille
un peu les données. Exécutée partie par partie, la durée est de 3' 30".
En lot, 19' 30" ! Certaines requêtes très simples, d'une durée de 2
secondes exécutées isolément, dépassent 10 minutes en lot (!)
(mesures prises en lisant la date système avant et après). Je soupçonne
l'existence de traitements de fond et j'ai pensé à la mise à jour des
index.
Après un DROP TABLE et une recréation de la table, la procédure tombe à
3' 30", mais UNE SEULE FOIS. Il n'y a alors que 3 index, ceux que j'ai
créés. Dès les premières lectures de la table (par des SELECT), SQL
Server crée 33 index, visibles dans sp_help nom_table et sysindexes, soit
36 au total (la table possède 36 colonnes). En effet, toutes les colonnes
sont susceptibles d'être une clé de recherche. Dès lors, la procédure dure
19' et ce temps varie peu.
Or, la grande majorité des colonnes de la table sont de type TINYINT
(1 octet) ou parfois SMALLINT (2 octets). Si SQL Server crée un index par
colonne, un index étant constitué d'adresses d'enregistrement (8 octets ?),
ces index occupent environ 5 FOIS PLUS DE PLACE que la table
elle-même, ce qui explique que la durée de la procédure passe de 3' 30"
à 19' 30".
Cette hypothèse est-elle vraisemblable ? Ces index automatiques sont-
ils nécessaires au moteur SQL ? Peuvent-ils être supprimés ? Et
accessoirement, comment savoir quelle place ils occupent sur le disque ?
Merci de votre aide et bonne journée.
Yves Ducourneau
PS : j'ai essayé en supprimant les stats (sp_autostats OFF) et ça n'a rien
changé.
PS : je suis en version 7.0 émulant 6.5.
PS : le plan d'exécution est propre.
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Fred BROUARD
Bonjour,
YDuc a écrit:
Bonjour à tous,
je suis confronté à de mauvaises performances d'une procédure stockée et je soupçonne les index automatiques d'en être la cause. La procédure charge 30 000 lignes dans une table, initialement vide, et retravaille un peu les données. Exécutée partie par partie, la durée est de 3' 30". En lot, 19' 30" ! Certaines requêtes très simples, d'une durée de 2 secondes exécutées isolément, dépassent 10 minutes en lot (!) (mesures prises en lisant la date système avant et après). Je soupçonne l'existence de traitements de fond et j'ai pensé à la mise à jour des index.
effectivement, trop d'index tuent l'index.
Après un DROP TABLE et une recréation de la table, la procédure tombe à 3' 30", mais UNE SEULE FOIS. Il n'y a alors que 3 index, ceux que j'ai créés. Dès les premières lectures de la table (par des SELECT), SQL Server crée 33 index, visibles dans sp_help nom_table et sysindexes, soit 36 au total (la table possède 36 colonnes). En effet, toutes les colonnes sont susceptibles d'être une clé de recherche. Dès lors, la procédure dure 19' et ce temps varie peu.
Or, la grande majorité des colonnes de la table sont de type TINYINT (1 octet) ou parfois SMALLINT (2 octets). Si SQL Server crée un index par colonne, un index étant constitué d'adresses d'enregistrement (8 octets ?), ces index occupent environ 5 FOIS PLUS DE PLACE que la table elle-même, ce qui explique que la durée de la procédure passe de 3' 30" à 19' 30".
Cette hypothèse est-elle vraisemblable ?
Oui
Ces index automatiques sont- ils nécessaires au moteur SQL ?
Oui
Peuvent-ils être supprimés ?
Oui, mais après traitement
Et
accessoirement, comment savoir quelle place ils occupent sur le disque ?
DBCC SHOWCONTIG ('TS_MOT_MOT') WITH TABLERESULTS vous donnera le nombre de page et d'extension de chaque index. Une page = 8 K, une extension = 8 pages.
Le mieux est de recréer la table à chaque exécution du lot.
Merci de votre aide et bonne journée.
Yves Ducourneau
PS : j'ai essayé en supprimant les stats (sp_autostats OFF) et ça n'a rien changé. PS : je suis en version 7.0 émulant 6.5. PS : le plan d'exécution est propre.
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
Bonjour,
YDuc a écrit:
Bonjour à tous,
je suis confronté à de mauvaises performances d'une procédure stockée
et je soupçonne les index automatiques d'en être la cause. La procédure
charge 30 000 lignes dans une table, initialement vide, et retravaille
un peu les données. Exécutée partie par partie, la durée est de 3' 30".
En lot, 19' 30" ! Certaines requêtes très simples, d'une durée de 2
secondes exécutées isolément, dépassent 10 minutes en lot (!)
(mesures prises en lisant la date système avant et après). Je soupçonne
l'existence de traitements de fond et j'ai pensé à la mise à jour des
index.
effectivement, trop d'index tuent l'index.
Après un DROP TABLE et une recréation de la table, la procédure tombe à
3' 30", mais UNE SEULE FOIS. Il n'y a alors que 3 index, ceux que j'ai
créés. Dès les premières lectures de la table (par des SELECT), SQL
Server crée 33 index, visibles dans sp_help nom_table et sysindexes, soit
36 au total (la table possède 36 colonnes). En effet, toutes les colonnes
sont susceptibles d'être une clé de recherche. Dès lors, la procédure dure
19' et ce temps varie peu.
Or, la grande majorité des colonnes de la table sont de type TINYINT
(1 octet) ou parfois SMALLINT (2 octets). Si SQL Server crée un index par
colonne, un index étant constitué d'adresses d'enregistrement (8 octets ?),
ces index occupent environ 5 FOIS PLUS DE PLACE que la table
elle-même, ce qui explique que la durée de la procédure passe de 3' 30"
à 19' 30".
Cette hypothèse est-elle vraisemblable ?
Oui
Ces index automatiques sont-
ils nécessaires au moteur SQL ?
Oui
Peuvent-ils être supprimés ?
Oui, mais après traitement
Et
accessoirement, comment savoir quelle place ils occupent sur le disque ?
DBCC SHOWCONTIG ('TS_MOT_MOT') WITH TABLERESULTS
vous donnera le nombre de page et d'extension de chaque index.
Une page = 8 K, une extension = 8 pages.
Le mieux est de recréer la table à chaque exécution du lot.
Merci de votre aide et bonne journée.
Yves Ducourneau
PS : j'ai essayé en supprimant les stats (sp_autostats OFF) et ça n'a rien
changé.
PS : je suis en version 7.0 émulant 6.5.
PS : le plan d'exécution est propre.
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
je suis confronté à de mauvaises performances d'une procédure stockée et je soupçonne les index automatiques d'en être la cause. La procédure charge 30 000 lignes dans une table, initialement vide, et retravaille un peu les données. Exécutée partie par partie, la durée est de 3' 30". En lot, 19' 30" ! Certaines requêtes très simples, d'une durée de 2 secondes exécutées isolément, dépassent 10 minutes en lot (!) (mesures prises en lisant la date système avant et après). Je soupçonne l'existence de traitements de fond et j'ai pensé à la mise à jour des index.
effectivement, trop d'index tuent l'index.
Après un DROP TABLE et une recréation de la table, la procédure tombe à 3' 30", mais UNE SEULE FOIS. Il n'y a alors que 3 index, ceux que j'ai créés. Dès les premières lectures de la table (par des SELECT), SQL Server crée 33 index, visibles dans sp_help nom_table et sysindexes, soit 36 au total (la table possède 36 colonnes). En effet, toutes les colonnes sont susceptibles d'être une clé de recherche. Dès lors, la procédure dure 19' et ce temps varie peu.
Or, la grande majorité des colonnes de la table sont de type TINYINT (1 octet) ou parfois SMALLINT (2 octets). Si SQL Server crée un index par colonne, un index étant constitué d'adresses d'enregistrement (8 octets ?), ces index occupent environ 5 FOIS PLUS DE PLACE que la table elle-même, ce qui explique que la durée de la procédure passe de 3' 30" à 19' 30".
Cette hypothèse est-elle vraisemblable ?
Oui
Ces index automatiques sont- ils nécessaires au moteur SQL ?
Oui
Peuvent-ils être supprimés ?
Oui, mais après traitement
Et
accessoirement, comment savoir quelle place ils occupent sur le disque ?
DBCC SHOWCONTIG ('TS_MOT_MOT') WITH TABLERESULTS vous donnera le nombre de page et d'extension de chaque index. Une page = 8 K, une extension = 8 pages.
Le mieux est de recréer la table à chaque exécution du lot.
Merci de votre aide et bonne journée.
Yves Ducourneau
PS : j'ai essayé en supprimant les stats (sp_autostats OFF) et ça n'a rien changé. PS : je suis en version 7.0 émulant 6.5. PS : le plan d'exécution est propre.
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
YDuc
Merci M. Fred Brouard pour cette réponse rapide et précise !
> Peuvent-ils être supprimés ?
Oui, mais après traitement
Si je comprends bien, ils reviendront tôt ou tard dans la journée lorsque les utilisateurs seront connectés et interrogeront la base... L'intérêt est limité puisque se limitant à décaler dans le temps leur construction.
DBCC SHOWCONTIG ('TS_MOT_MOT') WITH TABLERESULTS vous donnera le nombre de page et d'extension de chaque index.
Résultat, la table occupe 10 Mo et chaque index au moins 0,5 Mo, soit 18 Mo au total. Soit table + index 28 Mo, amusant pour 7 Mo de données brutes si toutes les chaînes étaient pleines !
Re-merci et bonne soirée.
Yves Ducourneau
Merci M. Fred Brouard pour cette réponse rapide et précise !
> Peuvent-ils être supprimés ?
Oui, mais après traitement
Si je comprends bien, ils reviendront tôt ou tard dans la journée lorsque
les utilisateurs seront connectés et interrogeront la base... L'intérêt est
limité puisque se limitant à décaler dans le temps leur construction.
DBCC SHOWCONTIG ('TS_MOT_MOT') WITH TABLERESULTS
vous donnera le nombre de page et d'extension de chaque index.
Résultat, la table occupe 10 Mo et chaque index au moins 0,5 Mo, soit 18 Mo
au total. Soit table + index 28 Mo, amusant pour 7 Mo de données brutes si
toutes les chaînes étaient pleines !
Merci M. Fred Brouard pour cette réponse rapide et précise !
> Peuvent-ils être supprimés ?
Oui, mais après traitement
Si je comprends bien, ils reviendront tôt ou tard dans la journée lorsque les utilisateurs seront connectés et interrogeront la base... L'intérêt est limité puisque se limitant à décaler dans le temps leur construction.
DBCC SHOWCONTIG ('TS_MOT_MOT') WITH TABLERESULTS vous donnera le nombre de page et d'extension de chaque index.
Résultat, la table occupe 10 Mo et chaque index au moins 0,5 Mo, soit 18 Mo au total. Soit table + index 28 Mo, amusant pour 7 Mo de données brutes si toutes les chaînes étaient pleines !
Re-merci et bonne soirée.
Yves Ducourneau
Fred BROUARD
sans doute devriez vous vous même créer des index après chargement de la table... Tout cela s'étudie avec les outils de MS SQL Server.
A +
YDuc a écrit:
Merci M. Fred Brouard pour cette réponse rapide et précise !
Peuvent-ils être supprimés ?
Oui, mais après traitement
Si je comprends bien, ils reviendront tôt ou tard dans la journée lorsque les utilisateurs seront connectés et interrogeront la base... L'intérêt est limité puisque se limitant à décaler dans le temps leur construction.
DBCC SHOWCONTIG ('TS_MOT_MOT') WITH TABLERESULTS vous donnera le nombre de page et d'extension de chaque index.
Résultat, la table occupe 10 Mo et chaque index au moins 0,5 Mo, soit 18 Mo au total. Soit table + index 28 Mo, amusant pour 7 Mo de données brutes si toutes les chaînes étaient pleines !
Re-merci et bonne soirée.
Yves Ducourneau
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
sans doute devriez vous vous même créer des index après chargement de la table...
Tout cela s'étudie avec les outils de MS SQL Server.
A +
YDuc a écrit:
Merci M. Fred Brouard pour cette réponse rapide et précise !
Peuvent-ils être supprimés ?
Oui, mais après traitement
Si je comprends bien, ils reviendront tôt ou tard dans la journée lorsque
les utilisateurs seront connectés et interrogeront la base... L'intérêt est
limité puisque se limitant à décaler dans le temps leur construction.
DBCC SHOWCONTIG ('TS_MOT_MOT') WITH TABLERESULTS
vous donnera le nombre de page et d'extension de chaque index.
Résultat, la table occupe 10 Mo et chaque index au moins 0,5 Mo, soit 18 Mo
au total. Soit table + index 28 Mo, amusant pour 7 Mo de données brutes si
toutes les chaînes étaient pleines !
Re-merci et bonne soirée.
Yves Ducourneau
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
sans doute devriez vous vous même créer des index après chargement de la table... Tout cela s'étudie avec les outils de MS SQL Server.
A +
YDuc a écrit:
Merci M. Fred Brouard pour cette réponse rapide et précise !
Peuvent-ils être supprimés ?
Oui, mais après traitement
Si je comprends bien, ils reviendront tôt ou tard dans la journée lorsque les utilisateurs seront connectés et interrogeront la base... L'intérêt est limité puisque se limitant à décaler dans le temps leur construction.
DBCC SHOWCONTIG ('TS_MOT_MOT') WITH TABLERESULTS vous donnera le nombre de page et d'extension de chaque index.
Résultat, la table occupe 10 Mo et chaque index au moins 0,5 Mo, soit 18 Mo au total. Soit table + index 28 Mo, amusant pour 7 Mo de données brutes si toutes les chaînes étaient pleines !
Re-merci et bonne soirée.
Yves Ducourneau
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
