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

Développement SQL Server Discussion :

Extractions gourmandes et performances [2008R2]


Sujet :

Développement SQL Server

  1. #1
    Membre habitué Avatar de Vonziz
    Inscrit en
    Décembre 2006
    Messages
    215
    Détails du profil
    Informations forums :
    Inscription : Décembre 2006
    Messages : 215
    Points : 131
    Points
    131
    Par défaut Extractions gourmandes et performances
    Bonjour à tous,

    Je travaille sur une application ASP.net / SQL Server utilisée par quelques milliers de personnes à travers le monde.

    Il s'avère que nous avons des problèmes de requêtes qui partent en timeout en cascade depuis quelques temps. De nombreuses sessions utilisateurs s'en trouvent impactées.

    Après analyse, nous avons identifié le problème.
    Des extractions Excel des objets principaux de la base semblent locker la plupart du schéma de données et durent plusieurs minutes.

    Du coup, toutes les autres sessions utilisateurs attendent la libération du verrou sur ces tables et partent en timeout au bout d'un moment.

    Ma questions : comment peut-on résoudre ces problèmes de timeout afin de faire cohabiter les requêtes "classiques" des grosses extractions Excel?

    Modifier des paramètres au niveau SQL Server pour la gestion des transactions?
    Créer des tables parallèles pour les extractions? Via des triggers? Via des jobs SSIS?

    Bref, comment gérez-vous ce genre de problématiques sur vos projets... :-)

    Merci d'avance!

  2. #2
    Membre éprouvé
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2009
    Messages
    623
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

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

    Informations forums :
    Inscription : Août 2009
    Messages : 623
    Points : 1 049
    Points
    1 049
    Par défaut
    Dans l'idéal, les extractions devraient se faire depuis un datawarehouse et non pas depuis tes bases OLTP.
    Mais bon, parfois on ne peut pas faire autrement en fonction du client. Les requêtes que tu as identifiées sont optimisées (traitement ensembliste + indexés) ? En effet, si ces transactions sont longues car trop de pages lues ou ligne à ligne, tu augmentes les chances de blocage des autres transactions.
    Quel est le niveau d'isolation de tes requêtes depuis Excel ?
    Blog Perso | Kankuru (logiciel gratuit pour SQL Server)

  3. #3
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    Juillet 2006
    Messages
    3 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : Juillet 2006
    Messages : 3 932
    Points : 4 239
    Points
    4 239
    Par défaut
    Je n'ai encore jamais fait d'extraction vers Excel directement depuis sql server donc je vais p-e dire de la merde mais en .NET, écrire dans Excel, c'est assez lent.

    Alors je pose la question de savoir si c'est le volume des données exportées est important ou non (me semble avoir lu que oui). Car si la requête est optimisée, c'est p-e l'export en lui-même qui prend du temps.

    Du coup, du haut du peu d'expérience que j'ai dans le domaine, pourquoi ne pas copier temporairement les données à exporter vers excel dans une table de travail avec une colonne identifiant l'utilisateur en plus et de faire l'export vers excel depuis cette table de travail pour les supprimer ensuite ?

    Ainsi, les tables de production devraient être verrouillées moins longtemps et au revoir les time-out.

    Non ?
    Kropernic

  4. #4
    Membre habitué Avatar de Vonziz
    Inscrit en
    Décembre 2006
    Messages
    215
    Détails du profil
    Informations forums :
    Inscription : Décembre 2006
    Messages : 215
    Points : 131
    Points
    131
    Par défaut
    Merci de vos retours. :-)

    Alors pour le datawarehouse, notre architecture sur ce projet ne le prévoit pas pour le moment.

    Pour répondre à vos questions :

    Les requêtes que tu as identifiées sont optimisées (traitement ensembliste + indexés) ?
    => Nous avons déjà fait une passe d'optimisation mais certaines requêtes peuvent retournées jusqu'à 100 000 lignes de données et concernent plusieurs tables en bases donc difficile d'optimiser plus à partir d'un moment...

    Quel est le niveau d'isolation de tes requêtes depuis Excel ?
    => On reste sur de l'isolation standard, pas de WITH NO LOCK ou autre, as-tu quelque chose à me proposer à ce niveau?

    Je n'ai encore jamais fait d'extraction vers Excel directement depuis sql server donc je vais p-e dire de la merde mais en .NET, écrire dans Excel, c'est assez lent.
    => Ca ne pose de problème au niveau SGBD car on fait déjà la requête en base puis on génère le fichier Excel à partir du DataSet donc la charge de la génération du fichier est encaissée par le IIS et non par le SQL Server.

    Du coup, du haut du peu d'expérience que j'ai dans le domaine, pourquoi ne pas copier temporairement les données à exporter vers excel dans une table de travail avec une colonne identifiant l'utilisateur en plus et de faire l'export vers excel depuis cette table de travail pour les supprimer ensuite ?
    => OK mais comment faire pour que la copie de ces données dans une autres tables ne soit pas couteuse en temps également...?

  5. #5
    Modérateur
    Avatar de DotNetMatt
    Homme Profil pro
    CTO
    Inscrit en
    Février 2010
    Messages
    3 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 36
    Localisation : Etats-Unis

    Informations professionnelles :
    Activité : CTO
    Secteur : Finance

    Informations forums :
    Inscription : Février 2010
    Messages : 3 611
    Points : 9 743
    Points
    9 743
    Billets dans le blog
    3
    Par défaut
    Citation Envoyé par darkelend Voir le message
    Dans l'idéal, les extractions devraient se faire depuis un datawarehouse et non pas depuis tes bases OLTP.
    Mais bon, parfois on ne peut pas faire autrement en fonction du client.
    Je te rejoins tout à fait sur ce point ! Il y a un moment où un simple système OLTP ne suffit plus, et il faut commencer à mettre en place de l'OLAP (quelle que soit la forme choisie).

    A force de tirer sur la corde côté OLTP, on peut finir par se retrouver face à un goulet d'étranglement qui pénalise non seulement les applications opérationnelles, mais aussi les applications de reporting. Sans anticipation, c'est le bazar assuré, car la contention sera de plus en plus présente.

    Une modification du niveau d'isolation serait une bonne idée, mais il faut insister sur le fait que c'est temporaire et que cela peut nuire à l'intégrité des données selon les scénarios.
    Less Is More
    Pensez à utiliser les boutons , et les balises code
    Desole pour l'absence d'accents, clavier US oblige
    Celui qui pense qu'un professionnel coute cher n'a aucune idee de ce que peut lui couter un incompetent.

  6. #6
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    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 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par Vonziz Voir le message
    Quel est le niveau d'isolation de tes requêtes depuis Excel ?
    => On reste sur de l'isolation standard, pas de WITH NO LOCK ou autre, as-tu quelque chose à me proposer à ce niveau?
    Utilisez le niveau d'isolation SNAPSHOT. Ceci enlève tout verrou de lecture et vos problèmes seront résolus.

    Je n'ai encore jamais fait d'extraction vers Excel directement depuis sql server donc je vais p-e dire de la merde mais en .NET, écrire dans Excel, c'est assez lent.
    => Ca ne pose de problème au niveau SGBD car on fait déjà la requête en base puis on génère le fichier Excel à partir du DataSet donc la charge de la génération du fichier est encaissée par le IIS et non par le SQL Server.
    Un dataset ouvre un curseur en live sur les données. Donc vérrouille. Mettez les paramètres nécessaires pour que votre dataset soit ouvert en READ ONLY, FORWARD ONLY, STATIC...

    Du coup, du haut du peu d'expérience que j'ai dans le domaine, pourquoi ne pas copier temporairement les données à exporter vers excel dans une table de travail avec une colonne identifiant l'utilisateur en plus et de faire l'export vers excel depuis cette table de travail pour les supprimer ensuite ?
    => OK mais comment faire pour que la copie de ces données dans une autres tables ne soit pas couteuse en temps également...?
    Non, cela pourrirait la RAM de 2 fois les données en cache ! Inutile.... Fausse bonne solution.

    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/ * * * * *

  7. #7
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    Juillet 2006
    Messages
    3 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : Juillet 2006
    Messages : 3 932
    Points : 4 239
    Points
    4 239
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Non, cela pourrirait la RAM de 2 fois les données en cache ! Inutile.... Fausse bonne solution.
    A ma décharge, j'avais prévenu que j'allais probablement dire une connerie
    Kropernic

  8. #8
    Membre habitué Avatar de Vonziz
    Inscrit en
    Décembre 2006
    Messages
    215
    Détails du profil
    Informations forums :
    Inscription : Décembre 2006
    Messages : 215
    Points : 131
    Points
    131
    Par défaut
    SQLPro, merci de votre retour.

    J'ai exécuté l'instruction suivante sur ma base :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER DATABASE <ma base> SET ALLOW_SNAPSHOT_ISOLATION ON;
    J'ai fais des tests tout de suite après mais j'ai toujours des timeouts en local.

    Les requêtes SQL s'empilent et se mettent en SUSPENDED tant que mon extraction n'a pas finie.

    Faut-il redémarrer SQL Server pour la prise en compte de tel paramètre?
    Peut-être faut-il vérifier d'autre paramétrage?

    Kropernic, toute proposition est bonne à prendre, on en discute, j'apprécie le geste.

  9. #9
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    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 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Il ne suffit pas d'autoriser le niveau d'isolation SNAPSHOT... Il faut en sus le mettre au niveau de la session.
    Commencer votre requête en plaçant votre session en SNAPSHOT :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    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/ * * * * *

  10. #10
    Membre habitué Avatar de Vonziz
    Inscrit en
    Décembre 2006
    Messages
    215
    Détails du profil
    Informations forums :
    Inscription : Décembre 2006
    Messages : 215
    Points : 131
    Points
    131
    Par défaut
    Merci SQLPro.

    J'essaie dès demain.

    Deux questions :

    - Faut-il positionner le niveau SNAPSHOT uniquement sur la transaction ou sur la transaction et la base de données?

    - Quel(s) sont les effets de bord possibles sur cette transaction au niveau corruption de données ?

    Merci de votre retour!

  11. #11
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    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 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par Vonziz Voir le message
    Merci SQLPro.

    J'essaie dès demain.

    Deux questions :

    - Faut-il positionner le niveau SNAPSHOT uniquement sur la transaction ou sur la transaction et la base de données?
    Il faut :
    1° autoriser la base à utiliser le niveau SNAPSHOT (donc ALTER DATABASE....)
    2° lancer la commande SET TRANSACTION ISOLATION LEVEL SNAPSHOT; avant la requête mais dans la même session (donc connexion)
    - Quel(s) sont les effets de bord possibles sur cette transaction au niveau corruption de données ?

    Merci de votre retour!
    Aucun puisque vous ne faite que lire une copie à l'instant t des données de la table.

    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/ * * * * *

  12. #12
    Membre habitué Avatar de Vonziz
    Inscrit en
    Décembre 2006
    Messages
    215
    Détails du profil
    Informations forums :
    Inscription : Décembre 2006
    Messages : 215
    Points : 131
    Points
    131
    Par défaut
    Bonjour,

    Alors j'ai fais des tests avec ces préconisations mais pas de changements.
    Par contre, quand je regarde mes requêtes via SQL Profiler, je dénote deux choses.

    - Les requêtes sont exécutées via la procédures stockées sp_executesql, ce qui donne quelque chose du genre :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    exec sp_executesql N'
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
     
    SELECT * FROM test',@timestamp='2013-07-17 17:11:47.107',@login='root'
    - Après chaque requête, j'ai un reset de la connexion :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    exec sp_reset_connection
    et un repositionnement de la transaction :

    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
     
    -- network protocol: LPC
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    Est-ce que ces deux points peuvent empêcher le niveau SNAPSHOT de fonctionner correctement?

  13. #13
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    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 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Non, le snapshot s'effectue bien. Ne vous attendez pas à de meilleurs perf. mais plus de blocage et pas de deadlock.

    Pour les perf, il faut une base parfaitement modélisée, puis indexer ou encore dé normaliser intelligemment....

    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/ * * * * *

  14. #14
    Membre habitué Avatar de Vonziz
    Inscrit en
    Décembre 2006
    Messages
    215
    Détails du profil
    Informations forums :
    Inscription : Décembre 2006
    Messages : 215
    Points : 131
    Points
    131
    Par défaut
    Bien que les résultats n'aient pas été significatifs, je vous remercie de vos retours.

    Nous nous orientons vers la mise en place de table adjacentes que l'on alimentera la nuit pour "déverser" la charge des extractions sur celles-ci.

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

Discussions similaires

  1. Extraction de mots clés
    Par Olive1808 dans le forum Algorithmes et structures de données
    Réponses: 6
    Dernier message: 01/02/2016, 21h49
  2. Réponses: 1
    Dernier message: 24/11/2010, 18h53
  3. Probleme de performance extraction données de BD Oracle vers Excel
    Par Balbo dans le forum Macros et VBA Excel
    Réponses: 8
    Dernier message: 28/05/2008, 14h53
  4. [tables systèmes] extraction de la structure d'une base
    Par laffreuxthomas dans le forum Autres SGBD
    Réponses: 6
    Dernier message: 23/03/2006, 14h24
  5. Extraction des phrases d'un Texte
    Par LE CHAKAL dans le forum Langage
    Réponses: 6
    Dernier message: 19/08/2002, 22h23

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