SelectEtoile : Base de connaissance SGBD

Posts Tagged 'spconfigure'

Paramètre 'optimizer level'


 A partir de la version 15.0.3 ESD 2 pour bénéficier des changements de l'optimiseur, il fallait utiliser un set option. A partir de la version 15.0.3 ESD 3, il est possible d'utiliser le paramètre de configuration sp_configure 'optimizer level'

sp_configure 'optimizer level:

 Par défaut l'option à pour valeur "ase_default". C'est à dire que les modifications effectuées sur l'optimiseur à partir de l'ESD1 ne sont pas prises en compte. Il faut positionner l'option à "ase_current" pour que tous les changements soient effectifs.


1> sp_options show
2> go

Category: Query Tuning

 name                                                                                              currentsetting defaultsetting scope
 ------------------------------------------------------------------------------------------------- -------------- -------------- -----
 optlevel                                                                                          ase_current    ase_current        3
 optgoal                                                                                           allrows_dss                                                                       1              1                  7
 no_stats_distinctness: allow duplicate estimates without stats                                    0              0                  7
 conserve_tempdb_space: keep estimated tempdb below resource granularity                           1              1                  7
 search_engine_timeout_factor                                                                      1              1                  7
 cr559034: avoid preferring non-covering over covered index scans                                  1              1                  7
 allow_wide_top_sort: allow max row size to be exceeded for top sorts                              1              1                  7
 cr562947: OPTLEVEL EXCEPTION SEE CR - allow cursor table scans                                    1              1                  7
 data_page_prefetch_costing: clustered row bias added                                              1              1                  7
 mru_buffer_costing: wash size buffer limit for MRU                                                1              1                  7
 cr546125: implicitly updatable cursor non-unique index scan                                       1              1                  7
 cr545771: improves multi-table outer-join and semi-join costing                                   1              1                  7
 cr545653: avoid inner table buffer estimate starvation                                            1              1                  7
 cr545585: covered iscan CPU costing too expensive                                                 1              1                  7
 cr545379: disallow reformatting on user forced index scan                                         1              1                  7
 cr545180: avoid reformat with no sargs if useful index exists                                     1              1                  7
 cr545059: reduce usage of buffer manager optimization sorts                                       1              1                  7
 cr544485: mark subquery join predicates with distinct view as sargs                               1              1                  7
 cr556728: facilitates merge joins between small tables                                            1              1                  7
 cr534175: compute GROUP BY worktables in nested subqueries only once when possible                1              1                  7
 cr531199: increases the number of useful nested loop join plans considered                        1              1                  7
 cr500736: supports nocase sortorder columns in mergejoin and hashjoin keys                        1              1                  7
 cr487450: improves DISTINCT costing of multi-table outer joins and/or semi-joins                  1              1                  7
 cr467566: allow abstract plans and statement cache to work together                               1              1                  7
 avoid_bmo_sorts: avoid sorts used only for buffer manager optimization                            1              1                  7
 cr497066: infer the nullability of isnull() by looking at its parameters                          1              1                  7
 cr421607: support NULL=NULL merge and hash join keys                                              1              1                  7
 distinct_exists_transform: transform distinct to semi-join                                        1              1                  7
 cr552795: eliminate duplicate rows during reformatting when they're not needed                    1              1                  7
 outer_join_costing: outer join row counts and histogramming                                       1              1                  7
 join_duplicate_estimates: avoid overestimates of dups in joins                                    1              1                  7
 auto_temptable_stats: auto generation of statistics for #temptables                               1              1                  7
 use_mixed_dt_sarg_under_specialor: allow special OR in case of mixed datatype sargs in IN/OR list 1              1                  7
 full_index_filter: eliminate non-covered full index scan strategies                               0              0                  7
 timeout_cart_product: timeout queries involving cartesian product and more than 5 tables.         1              1                  7
 in_list_general_or: optimize in lists under general ors                                           1              1                  7
 multi_attribute_density_costing: Use legacy multi-attribute density costing technique             0              0                  7
 disable_nonbin_sortorder_interpolation: turn off non-binary sort order interpolation costing      0              0                  7
 cr611385: enable ScanValues costing                                                               1              1                  7
 cr574923: extend search space hidden by greedy algorithm                                          1              1                  7
 cr614461: enable in-order rid scan estimates                                                      1              1                  7
 cr611637: avoid duplicate NULL selectivity                                                        1              1                  7
 cr619965: dense frequency for 'Jan  1 1900 12:00:00:000AM'                                        1              1                  7
 cr439123: 0.0 range density for low cardinality tables                                            1              1                  7
 cr619756: DISTINCT, EXISTS, and GROUP BY row estimation                                           1              1                  7
 cr624835: correlated equi-join constant costing                                                   1              1                  7
 cr628311: subquery decorrelated store index costing                                               1              1                  7
 cr628845: ORDER BY consumes parallel bmo sorts                                                    1              1                  7

 Je vous laisse le soin de comparer les différences entre ase_current et ase_default...... Innocent

 Le paramètre 'optimizer level' permet d'activer les changements de l'optimiseur de façon plus personnelle et ceux pas divers moyens (login script, paramètre de session, server...).

 Les valeurs possibles pour 'optimizer level' sont:

     ase_current: Active à tous les changements de l'optimiseur à partir de l'ESD actuelle.
    ase_default: désactive toutes les modifications optimiseur depuis la version de base. C'est la valeur par défaut, et correspond actuellement à  ASE 15.0.3 ESD # 1.
    ase1503esd2: Active à tous les changements de l'optimiseur à partir d'ASE 15.0.3 ESD # 2.
    ase1503esd3: Active à tous les changements de l'optimiseur à partir d'ASE 15.0.3 ESD # 3.
    ase1503esd4: Active à tous les changements de l'optimiseur à partir d'ASE 15.0.3 ESD # 4.

L'option existe aussi à partir de ASE 15.5 ESD 2

Pour plus d'information, C'est ici 

 Faut-il l'activer?

 Oui, Il est recommandé d'utiliser le niveau d'optimiseur ase_current. Cependant pour des environnements existants, des tests de performance sont vivement conseillés... 

Mise en place des outils Asemonlogger pour SYBASE

Installation des outils AsemonLogger


Ce document décrit l'installation et la mise en place de l'outil Asemonlogger.Cet outil a été développé par JP MARTIN (consultant SYBASE) et est disponible sur le site de SYBASE  dans Code Exchange.Il permet de monitorer les serveurs SYBASE (Dataserver ASE, Replication Server ou Server IQ)




  • Récupération des fichiers sur le site de SYBASE (Code Exchange)
  • Installer un serveur SYBASE qui accueillera la base asemonlogger de collecte (que l'on appelera ASEMonServer)
  • Installer java sur la machine si ce n'est pas le cas
  • Sourcer le fichier SYBASE/
  • L'outil se décompose en deux parties
    • AsemonLogger récolte les différentes données de monitoring (SYBASE ASE, SYBASE Replication Server, SYBASE IQ)
    • AsemonReport met en forme les résultats sous forme de site internet (http + php)


Read more: Mise en place des outils Asemonlogger pour SYBASE

Utilisation du Profiler SQLServer

SQLServer Trace Profiler permet de tracer l'activité d'un serveur SQLServer, Analysis Services... Il peut être pratique pour récupérer les ordres SQL, le temps d'exécution, les entrées / sorties...


Lancement en Ligne de commande

Cmd :

profiler /E /A WinServer\ASServer /T"Standart" /O c:\jp\temp\ASServer.trc /M "02-19-10 16:40:00"

Description des options : cliquer ici


Trace profiler par défaut :

Il existe une trace par défaut sur SQLServer. Pour la désactiver, il suffit de lancer les commandes suivantes :

EXEC master.dbo.sp_configure 'allow updates', 1;


EXEC master.dbo.sp_configure 'show advanced options', 1;


EXEC master.dbo.sp_configure 'default trace enabled', 0;




EXEC master.dbo.sp_configure 'show advanced options', 0;


EXEC master.dbo.sp_configure 'allow updates', 0;



Trace des requêtes et procédures stockées

Pour tracer les requêtes et les procédures stockées, j'ai choisi de  tracer les events suivants :

  • Stored Procedures
    • RPC:Completed
    • SP:Completed
    • SP:StmtCompleted
  • TSQL
    • Exec Prepared SQL
    • Prepare SQL
    • SQL:StmtCompleted
    • Unprepare SQL

Dans le cas ou nous choisissons de sauvegarder (et donc de rediriger) dans une table, il suffit d'aller dans Trace properties / onglet General, cocher la case 'save to table'. Il sera alors facile de requêter sur cette table pour analyser les compteurs.

Nous redirigeons le résultat vers la table perfs_jps..trace_queries

La requête suivante permet de visualiser ce que le ClientProcessID 6772 lance comme requête et procédures stockées

select StartTime,EndTime,Duration,RowCounts,ObjectName,LineNumber,TextData

from perfs_jps..trace_queries

where ClientProcessID=6772 order by StartTime