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

MS SQL Server Discussion :

Cache du plan d'execution se vide


Sujet :

MS SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Homme Profil pro
    Inscrit en
    Octobre 2009
    Messages
    118
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Octobre 2009
    Messages : 118
    Par défaut Cache du plan d'execution se vide
    Bonjour à tous,

    Nous rencontrons un sérieux problème de fonctionnement sur un site qui utilise SQL Server 2012.

    - Nous utilisons SQL 2012 Standard x64 11.0.5058 (SP2) sur une machine équipée de 64Go de ram et 16 coeurs.
    - Nous avons environ 50 tables. La plus grosse table de la base fait 140 000 lignes et la moyenne est d'environ 20 000.
    - La base est répliquée sur le même serveur. De plus le serveur est dupliqué en temps réel avec Double Take
    - Nous avons pour politique interne de n'utiliser que des procédures stockées pour nos requêtes.

    Le problème est que les procédures prennent un temps fou à s'éxécuter. Certaines procédures qui mettent quelques millisecondes à s’exécuter en temps normal, prennent certains jours 10 secondes (jusqu'a 25s) à chaque exécution !.
    Après de nombreuses investigations, nous avons découvert qu'elles ne restent pas dans le cache.

    En fait sur le serveur nous avons environ 200 connexions et 500 PS. Seules 2 d'entre elles restent dans le cache en permanence (pourquoi ces deux là ?), les autre disparaissent toutes d'un coup à intervalles réguliers. Je sais que la recompilation d'une procédure est normal dans le cas de changement de stats par exemple, mais seulement les procédures impactées par ladite stat.
    Le buffer cache est utilisé à 10%.
    Si on regarde le plan du cache, lorsqu'il y est, le nombre de lignes estimé est souvent délirant pour une des tables. J'ai tenté de mettre à jours les statistiques à la main pour cette table. La performance est meilleure, sans etre au top, et surtout cela reste très provisoire.

    Nous avons une grande habitude de SQL Server dans les versions précédentes et nous n'avons jamais rencontré ce problème.

    Comment pouvons nous déterminer l'origine du problème ???

    Merci pour votre aide !!

  2. #2
    Membre Expert

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Billets dans le blog
    8
    Par défaut
    Juste pour confirmer vos propos :

    Que donne le résultat de la requête ci-dessous :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT objtype  AS 'Cached_Object_Type',
    count(*) AS 'Nombre_Plans',
    sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan_Cache_Size_in_MB',
    avg(usecounts) AS 'Avg_Use_Count'
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype
    GO

    A+

  3. #3
    Membre confirmé
    Homme Profil pro
    Inscrit en
    Octobre 2009
    Messages
    118
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Octobre 2009
    Messages : 118
    Par défaut
    Le résultat :

    Cached_Object_Type Nombre_Plans Plan_Cache_Size_in_MB Avg_Use_Count
    UsrTab 1 0 3516
    Prepared 16 1 143
    View 28 4 3954
    AdHoc 35298 1233 12
    Check 4 0 188
    Trigger 4 0 158
    Proc 192 163 755


    Donc tout le cache est occupé par des requetes adhoc. Or nous n'utilisons pas ce genre de requetes (sauf pour les diags, tests, devs... ce qui est temporaire).
    En fait le cache est rempli d'appel aux procédures avec les arguments différents :

    exec MaProcedure 1,3
    exec MaProcédure 1,5....

    J'ai regardé sur nos autre serveurs en production en 2008R2 et la requete que vous m'avez donné est plutot du genre :

    Cached_Object_Type Nombre_Plans Plan_Cache_Size_in_MB Avg_Use_Count
    UsrTab 6 0 14421
    Prepared 30 3 833
    View 114 11 5
    AdHoc 3450 156 175
    Check 20 0 8
    Trigger 5 0 3
    Proc 352 107 13164


    Je précise que nous utilisons toujours la même DLL d'accès à la base de données et que l'application est de même type avec un certain nombre de programmes identique sans toutefois être une copie conforme.

  4. #4
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    22 001
    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 : 22 001
    Billets dans le blog
    6
    Par défaut
    Pourquoi cette réplication et comment est-elle faite ?
    (peut être justement pourrie t-elle votre cache...)

    Pourquoi Double Take et non pas le mirroring ou AlwaysON ?
    Double take ne garantie pas le reprise d'une base de données MS SQL Server... du fait des opérations asynchrones entre fichiers de données et JT !

    êtes vous sur de votre version ?
    Pouvez vous nous donner le résultat de la requête suivante :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT @@VERSION;
    EXEC xp_msver;
    SELECT * FROM sys.configurations;
    Vos procédures contiennent-elles des commandes DDL (CREATE, ALTER, DROP) ? notamment pour les tables temporaires ???

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

  5. #5
    Membre confirmé
    Homme Profil pro
    Inscrit en
    Octobre 2009
    Messages
    118
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Octobre 2009
    Messages : 118
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Pourquoi cette réplication et comment est-elle faite ?
    (peut être justement pourrie t-elle votre cache...)
    La réplication est transactionnelle en push dans une base située dans la même instance sur le serveur.
    Bon dis comme ça, cela parait débile mais : je ne réplique que les insert et update, pas les delete. Cela constitue donc une base de données d'historique que je peut vider selon un délai différent de la base dite de production.
    J'ai pas trouvé mieux pour constituer une base historique sans développer d'usine à gaz. de plus le jour ou j'ai a besoin d'un peu plus de puissance je met en place deux serveurs différents sans changer notre méthode, l'abonné pouvant se trouver n'importe où.
    Si vous avez mieux et plus simple je suis preneur....

    Citation Envoyé par SQLpro Voir le message
    Pourquoi Double Take et non pas le mirroring ou AlwaysON ?
    Plusieurs raisons :
    • Always on est nouveau et je ne sait pas m'en servir, je ne sais pas non plus quelles sont exactement ses possibilités (on aime pas changer de techno sans savoir si elle marche !!!)
    • Les deux techniques SQL que vous citez ne mirrorent pas le système. Donc sur le serveur répliqué on est obligé de maintenir les applications à jour
    • Double take est imposé par le client comme solution de sécurisation des serveurs, mais je doute qu'il sache quelles sont les conséquence sur SQL



    Citation Envoyé par SQLpro Voir le message
    Double take ne garantie pas le reprise d'une base de données MS SQL Server... du fait des opérations asynchrones entre fichiers de données et JT !
    Ca je ne le savait pas, mais cela ne m'étonne pas du tout.... Je vais en parler au chef du projet qui sera ravi de l'apprendre

    Citation Envoyé par SQLpro Voir le message
    êtes vous sur de votre version ?
    Pouvez vous nous donner le résultat de la requête suivante :
    SELECT @@VERSION;
    EXEC xp_msver;
    SELECT * FROM sys.configurations;
    Résultat : (désolé je ne sais pas copier les résultats proprement)
    SELECT @@VERSION;
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
    	May 14 2014 18:34:29 
    	Copyright (c) Microsoft Corporation
    	Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
    EXEC xp_msver;
    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
     
    ProductName;NULL;Microsoft SQL Server
    ProductVersion;720896;11.0.5058.0
    Language;1036;Français (France)
    Platform;NULL;NT x64
    Comments;NULL;SQL
    CompanyName;NULL;Microsoft Corporation
    FileDescription;NULL;SQL Server Windows NT - 64 Bit
    FileVersion;NULL;2011.0110.5058.00 ((SQL11_PCU_Main).140514-1820 )
    InternalName;NULL;SQLSERVR
    LegalCopyright;NULL;Microsoft Corp. All rights reserved.
    LegalTrademarks;NULL;Microsoft SQL Server is a registered trademark of Microsoft Corporation.
    OriginalFilename;NULL;SQLSERVR.EXE
    PrivateBuild;NULL;NULL
    SpecialBuild;331481088;NULL
    WindowsVersion;331481088;6.2 (9200)
    ProcessorCount;16;16
    ProcessorActiveMask;NULL;            ffff
    ProcessorType;8664;NULL
    PhysicalMemory;65491;65491 (68671807488)
    Product ID;NULL;NULL
    SELECT * FROM sys.configurations;
    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
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
     
    101;recovery interval (min);0;0;32767;0;Maximum recovery interval in minutes;1;1
    102;allow updates;0;0;1;0;Allow updates to system tables;1;0
    103;user connections;0;0;32767;0;Number of user connections allowed;0;1
    106;locks;0;5000;2147483647;0;Number of locks for all users;0;1
    107;open objects;0;0;2147483647;0;Number of open database objects;0;1
    109;fill factor (%);0;0;100;0;Default fill factor percentage;0;1
    114;disallow results from triggers;0;0;1;0;Disallow returning results from triggers;1;1
    115;nested triggers;1;0;1;1;Allow triggers to be invoked within triggers;1;0
    116;server trigger recursion;1;0;1;1;Allow recursion for server level triggers;1;0
    117;remote access;1;0;1;1;Allow remote access;0;0
    124;default language;2;0;9999;2;default language;1;0
    400;cross db ownership chaining;0;0;1;0;Allow cross db ownership chaining;1;0
    503;max worker threads;0;128;65535;0;Maximum worker threads;1;1
    505;network packet size (B);4096;512;32767;4096;Network packet size;1;1
    518;show advanced options;1;0;1;1;show advanced options;1;0
    542;remote proc trans;0;0;1;0;Create DTC transaction for remote procedures;1;0
    544;c2 audit mode;0;0;1;0;c2 audit mode;0;1
    1126;default full-text language;1036;0;2147483647;1036;default full-text language;1;1
    1127;two digit year cutoff;2049;1753;9999;2049;two digit year cutoff;1;1
    1505;index create memory (KB);0;704;2147483647;0;Memory for index create sorts (kBytes);1;1
    1517;priority boost;0;0;1;0;Priority boost;0;1
    1519;remote login timeout (s);10;0;2147483647;10;remote login timeout;1;0
    1520;remote query timeout (s);600;0;2147483647;600;remote query timeout;1;0
    1531;cursor threshold;-1;-1;2147483647;-1;cursor threshold;1;1
    1532;set working set size;0;0;1;0;set working set size;0;1
    1534;user options;0;0;32767;0;user options;1;0
    1535;affinity mask;0;-2147483648;2147483647;0;affinity mask;1;1
    1536;max text repl size (B);4194304;-1;2147483647;4194304;Maximum size of a text field in replication.;1;0
    1537;media retention;0;0;365;0;Tape retention period in days;1;1
    1538;cost threshold for parallelism;5;0;32767;5;cost threshold for parallelism;1;1
    1539;max degree of parallelism;0;0;32767;0;maximum degree of parallelism;1;1
    1540;min memory per query (KB);1024;512;2147483647;1024;minimum memory per query (kBytes);1;1
    1541;query wait (s);-1;-1;2147483647;-1;maximum time to wait for query memory (s);1;1
    1543;min server memory (MB);0;0;2147483647;16;Minimum size of server memory (MB);1;1
    1544;max server memory (MB);32768;128;2147483647;32768;Maximum size of server memory (MB);1;1
    1545;query governor cost limit;0;0;2147483647;0;Maximum estimated cost allowed by query governor;1;1
    1546;lightweight pooling;0;0;1;0;User mode scheduler uses lightweight pooling;0;1
    1547;scan for startup procs;1;0;1;1;scan for startup stored procedures;0;1
    1549;affinity64 mask;0;-2147483648;2147483647;0;affinity64 mask;1;1
    1550;affinity I/O mask;0;-2147483648;2147483647;0;affinity I/O mask;0;1
    1551;affinity64 I/O mask;0;-2147483648;2147483647;0;affinity64 I/O mask;0;1
    1555;transform noise words;0;0;1;0;Transform noise words for full-text query;1;1
    1556;precompute rank;0;0;1;0;Use precomputed rank for full-text query;1;1
    1557;PH timeout (s);60;1;3600;60;DB connection timeout for full-text protocol handler (s);1;1
    1562;clr enabled;0;0;1;0;CLR user code execution enabled in the server;1;0
    1563;max full-text crawl range;4;0;256;4;Maximum  crawl ranges allowed in full-text indexing;1;1
    1564;ft notify bandwidth (min);0;0;32767;0;Number of reserved full-text notifications buffers;1;1
    1565;ft notify bandwidth (max);100;0;32767;100;Max number of full-text notifications buffers;1;1
    1566;ft crawl bandwidth (min);0;0;32767;0;Number of reserved full-text crawl buffers;1;1
    1567;ft crawl bandwidth (max);100;0;32767;100;Max number of full-text crawl buffers;1;1
    1568;default trace enabled;1;0;1;1;Enable or disable the default trace;1;1
    1569;blocked process threshold (s);0;0;86400;0;Blocked process reporting threshold;1;1
    1570;in-doubt xact resolution;0;0;2;0;Recovery policy for DTC transactions with unknown outcome;1;1
    1576;remote admin connections;0;0;1;0;Dedicated Admin Connections are allowed from remote clients;1;0
    1579;backup compression default;1;0;1;1;Enable compression of backups by default;1;0
    1580;filestream access level;0;0;2;0;Sets the FILESTREAM access level;1;0
    1581;optimize for ad hoc workloads;0;0;1;0;When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.;1;1
    1582;access check cache bucket count;0;0;65536;0;Default hash bucket count for the access check result security cache;1;1
    1583;access check cache quota;0;0;2147483647;0;Default quota for the access check result security cache;1;1
    16384;Agent XPs;1;0;1;1;Enable or disable Agent XPs;1;1
    16386;Database Mail XPs;0;0;1;0;Enable or disable Database Mail XPs;1;1
    16387;SMO and DMO XPs;1;0;1;1;Enable or disable SMO and DMO XPs;1;1
    16388;Ole Automation Procedures;0;0;1;0;Enable or disable Ole Automation Procedures;1;1
    16390;xp_cmdshell;0;0;1;0;Enable or disable command shell;1;1
    16391;Ad Hoc Distributed Queries;0;0;1;0;Enable or disable Ad Hoc Distributed Queries;1;1
    16392;Replication XPs;0;0;1;0;Enable or disable Replication XPs;1;1
    16393;contained database authentication;0;0;1;0;Enables contained databases and contained authentication;1;0
    Citation Envoyé par SQLpro Voir le message
    Vos procédures contiennent-elles des commandes DDL (CREATE, ALTER, DROP) ? notamment pour les tables temporaires ???
    Nous n'utilsons ni curseurs, ni tables temporaires directement. Les procédures sont uniquement constituées d'une commande select, insert, update ou delete. En revanche, nous utilisons très intensivement les CTE, parfois plus de 20 à 30 dans certaines requetes extrêmes...

    Pour ce qui est du serveur :
    La base tempdb est sur le disque système (oui je sais c'est pas bien) mais on a que 2 disques et on a préféré la séparer des disques de données normales. elle comporte 8 fichiers comme suggéré par pas mal de posts sur le sujet....
    Le plan de maintenance est effectué toutes les nuit hors production, il réorganise ou reconstruit les index selon le taux de fragmentation (entre 10 et 30% reorg, > 30% rebuild)
    On effectue aussi un update systématique des statistiques qui on plus d'un jour. MAIS : il y a boulette dans le script car on utilise les options par défaut et donc il me semble qu'il fait un sample de la table, pas un fullscan.

    A+

  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
    22 001
    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 : 22 001
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par Batou69 Voir le message
    La réplication est transactionnelle en push dans une base située dans la même instance sur le serveur.
    Bon dis comme ça, cela parait débile mais : je ne réplique que les insert et update, pas les delete. Cela constitue donc une base de données d'historique que je peut vider selon un délai différent de la base dite de production.
    J'ai pas trouvé mieux pour constituer une base historique sans développer d'usine à gaz. de plus le jour ou j'ai a besoin d'un peu plus de puissance je met en place deux serveurs différents sans changer notre méthode, l'abonné pouvant se trouver n'importe où.
    Si vous avez mieux et plus simple je suis preneur....
    Vous feriez mieux d'utiliser change tracking ou change data capture et faire le différentiel aux heures creuses


    Plusieurs raisons :
    • Always on est nouveau et je ne sait pas m'en servir, je ne sais pas non plus quelles sont exactement ses possibilités (on aime pas changer de techno sans savoir si elle marche !!!)
    • Les deux techniques SQL que vous citez ne mirrorent pas le système. Donc sur le serveur répliqué on est obligé de maintenir les applications à jour
    • Double take est imposé par le client comme solution de sécurisation des serveurs, mais je doute qu'il sache quelles sont les conséquence sur SQL

    AlwaysOn est extrêmement fiable. Nous l'utilisons par exemple pour Geodis sur une base de 7 To.

    Vous avez donc des applications sur le serveur. C'est probablement cela qui fait que votre système est bancal. SQL Server doit être installé sur un serveur dédié et aucune autre application ni service ne doit tourner sur ce serveur, pas même un antivirus !


    SELECT * FROM sys.configurations;
    vous devriez régler correctement le parallélisme :
    1) cost threshold for parallelism à 12 ou 24
    2) max degree of parallelism à 2 ou 4

    Pourquoi un max server memory à 32768 ? avez-vous d'autres instances sur le même serveur ? d'autres applications ? d'autres services ??? Lesquels ??????
    Si non, alors mettez 60 Go

    Placez le OPTIMIZE FOR ad hoc workloads à 1

    Pour ce qui est du serveur :
    La base tempdb est sur le disque système (oui je sais c'est pas bien) mais on a que 2 disques et on a préféré la séparer des disques de données normales. elle comporte 8 fichiers comme suggéré par pas mal de posts sur le sujet....
    sur le disque local, c'est bien. Mais en revanche 8 fichiers c'est beaucoup. Passez à 4 et régler le parallélisme à 4 maximum. Faites de même pour votre base de production => 4 fichiers d'égale longueur pour les données

    Le plan de maintenance est effectué toutes les nuit hors production, il réorganise ou reconstruit les index selon le taux de fragmentation (entre 10 et 30% reorg, > 30% rebuild)
    vous voulez dire "aux heures creuses" ?

    On effectue aussi un update systématique des statistiques qui on plus d'un jour. MAIS : il y a boulette dans le script car on utilise les options par défaut et donc il me semble qu'il fait un sample de la table, pas un fullscan.
    Ce serait mieux !

    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
    Membre Expert

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Billets dans le blog
    8
    Par défaut
    Oui, effectivement, il y a manifestement une différence de comportement entre votre Serveur de prod (SQL Server 2008 R2 ) et le Seveur SQL 2012 !
    Ci-dessous quelques pistes à vérifier
    - Vérifiez et comparer (entre les 2 Serveurs) le paramétrage de la base de données ( PARAMETERIZATION) SIMPLE ou FORCED (?). En effet ce paramétrage (PARAMETERIZATION = FORCED) peut limiter la prolifération des requêtes AdHoc. Mais si ce paramétrage n'était actif pour la base de données, sur le Serveur de prod SQL Server 2008 R2, il n'y a aucune raison de l'activer pour la base dans l'environnement SQL Server 2012, tant que vous n'avez déterminé les causes exacts du problème dans un environnement censé être presque identique.

    - Vérifier le contenu des ces fameuses requête SQL AdHoc (à partir de leur plan_handle), Vérifier si celles-ci proviennent bien de vos applications ou si leur texte ne vous évoquent rien et au quel elle proviendraient d'autres applications qui vous ne gérez pas et vous ne connaissez pas (?)
    -- Exemple :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    select p.refcounts, p.usecounts, p.plan_handle, s.text
    from sys.dm_exec_cached_plans as p
    cross apply sys.dm_exec_sql_text (p.plan_handle) as s
    where p.cacheobjtype = 'compiled plan'
    and p.objtype = 'adhoc'
    AND p.usecounts <= 3 
    order by p.usecounts asc
    - Vérifiez, par exemple avec le profiler, si les connexions de vos applications ne modifient pas les options SET de façons non homogène d'une session à une autre de vos application. En effet, les options SET font partie intégrante du cahe plan, et les modifier fait en sorte qu'un plan déjà existant compilé avec d'autres options SET ne sera pas réutilisé (voir sys.dm_exec_plan_attributes (plan_handle)).

    Voilà j'espère que d'autres experts sur ce forum (et il y en a !) vous donneront d'autres conseils et d'autres pistes à explorer ...

    A+

    PS : A propos d'expert ! j'avais pas vu la réponse de SQLPro ci-dessus !

  8. #8
    Membre Expert Avatar de iberserk
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Novembre 2004
    Messages
    1 795
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2004
    Messages : 1 795
    Par défaut
    Bonsoir,
    passer presque tous les recorsets des application en forwardonly -> supprime les FETCH API_CURSOR (environ 30000 plan dans le cache !!!)
    Quel langage utilisez vous pour générer des FETCH_API_CURSOR???

    Je ne suis pas sûr d'avoir bien lu... vos requètes sont-elles bien paramétrées?
    Vous pouvez tentez d'activer la trace 2371 pour améliorer l'auto update stats (au fait est-il bien activé???) et ainsi éviter la mise à jour "sauvage" des 4 tables.

    Pour vérification quand le plan d'exécution se "plante" quelle est la valeur réellement prise (valeur du paramètre qui a servit à compiler le plan)?

    Vous n'utilisez aucune variable table dans vos SP?

    Avez vous vérifié qu'aucun guide de plan n'est présent?

    Dernière question vos contraintes sont-elles bien considéré comme valide par SQL SERVER (WITH CHECK...) (rare... mais déjà vu chez un éditeur)

  9. #9
    Membre confirmé
    Homme Profil pro
    Inscrit en
    Octobre 2009
    Messages
    118
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Octobre 2009
    Messages : 118
    Par défaut
    Citation Envoyé par iberserk Voir le message
    Bonsoir,


    Quel langage utilisez vous pour générer des FETCH_API_CURSOR???

    Je ne suis pas sûr d'avoir bien lu... vos requètes sont-elles bien paramétrées?
    Bonjour,

    Le langage que nous utilisons est le C++ avec Qt. Nous utilisons le driver ODBC version 10.0 (le sql native client)

    Nos procédures comportent des paramètres. Cependant, je ne suis pas sûr que nos requetes soient vues commes paramétrées par le moteur..... Je ne sais pas comment le savoir.

    Citation Envoyé par iberserk Voir le message
    Vous pouvez tentez d'activer la trace 2371 pour améliorer l'auto update stats (au fait est-il bien activé???) et ainsi éviter la mise à jour "sauvage" des 4 tables.
    Non la trace XXXX n'est pas activée. Je la découvre.

    D'une façon générale, je me considère comme un fervent partisant de SQL Server, mais je suis exaspéré par toutes ces options cachées que l'on découvre au détour des forums et qui changent le moteur.
    Ne peut il pas exister quelque part un répertoire de ce qu'il faut faire pour que l'installation soit faite correctement ????
    Une sorte de livre blanc sur les meilleures pratiques ?
    /fin de la parenthèse

    Citation Envoyé par iberserk Voir le message
    Pour vérification quand le plan d'exécution se "plante" quelle est la valeur réellement prise (valeur du paramètre qui a servit à compiler le plan)?
    Je ne sais pas comment le vérifier... D'autant plus que certaines requetes qui partent en vrille n'ont pas de paramètres.


    Citation Envoyé par iberserk Voir le message
    Vous n'utilisez aucune variable table dans vos SP?

    Avez vous vérifié qu'aucun guide de plan n'est présent?

    Dernière question vos contraintes sont-elles bien considéré comme valide par SQL SERVER (WITH CHECK...) (rare... mais déjà vu chez un éditeur)
    Nous n'avons pas de variable table dans nos SP. Uniquement des parametres et des gros cte dans tous les sens....
    Pas non plus de 'repères de plan'

    Enfin pour le check, si j'ai bien compris votre question, elles ont toutes le champ i's_not_trusted' à 0 dans la table sys.foreign_keys

    Merci pour votre aide !!

  10. #10
    Membre confirmé
    Homme Profil pro
    Inscrit en
    Octobre 2009
    Messages
    118
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Octobre 2009
    Messages : 118
    Par défaut
    Arg...

    plus personne ?

  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
    22 001
    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 : 22 001
    Billets dans le blog
    6
    Par défaut
    Nous avons fait :
    - passer presque tous les recorsets des application en forwardonly -> supprime les FETCH API_CURSOR (environ 30000 plan dans le cache !!!)
    Je ne serais pas étonné que vos curseurs bouffent toute la mémoire au détriment du cache !

    Sont-ce des curseurs côté client ou côté serveur ?

    Ou est situé l'applicatif ? Sur le client ? Sur un serveur à part ? Sur le serveur SQL ?

    Avez-vous dimensionné correctement tout vos espaces de stockage ? (pour ne jamais avoir de croissance de fichier...)

    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 Expert Avatar de iberserk
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Novembre 2004
    Messages
    1 795
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2004
    Messages : 1 795
    Par défaut
    Est-ce vous qui générez vous même les curseur, si non pouvez vous publier un exemple de code d'accès aux données?

    Pk ne pas passer par ADO? (SqlCommand/SqlConnection/SqlDataReader).

    Cordialement

Discussions similaires

  1. Bind variables et plan d'execution
    Par Wurlitzer dans le forum Oracle
    Réponses: 6
    Dernier message: 26/02/2007, 14h04
  2. [Oracle 10.2] Plan d'execution fonction PL/SQL
    Par pegase06 dans le forum PL/SQL
    Réponses: 6
    Dernier message: 13/02/2007, 12h02
  3. Plans d'execution differents
    Par jajaCode dans le forum Oracle
    Réponses: 13
    Dernier message: 14/12/2006, 12h29
  4. cache pour plan de requette
    Par foblar dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 09/08/2006, 04h03
  5. plan d'execution
    Par osoudee dans le forum MS SQL Server
    Réponses: 1
    Dernier message: 09/03/2006, 10h40

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