Bonjour, désolé d'arriver comme un chien dans un jeu de quilles, d'autant que je suis absolument pas expert de la partie administration.
Mais lorsque vous dites que les requêtes "ad hoc" sont principalement des appels aux procédure stockées avec des paramètres différents, je suis tout de même un peu interpellé.
En effet, vos applications lancent des appels SQL littéraux ?
Pourquoi ne pas passer par des requêtes paramétrées ?
Ceci résoudrait, indépendamment des autres problème, ce volume de requêtes Ad Hoc.
Désolé par avance si j'ai dit une bêtise.
On ne jouit bien que de ce qu’on partage.
"Mille milliards de mille sabords" voilà ce qu'aurait dit le Capitaine Haddock à propos des requêtes "Ad Hoc" !
En réalité le problème avec les procédures stockées, ce n'est pas tant les appels avec des arguments littéraux, même s'il est préférable à ce que les arguments soient "typés" et que les types des arguments correspondent aux types des différents paramètres de la procédure.
Par ailleurs, il ne faut pas oublier qu'une procédure est par essence déjà paramétrée, ie, une procédure possède déjà ses propres paramètres !
Le problème avec les procédure stockées c'est souvent le comportement normal du moteur SQL Server, appelé le "Parameter Sniffing". Le "Parameter Sniffing" a parfois un effet désastreux qui consiste à réutiliser un plan préalablement compilé et stocké dans le cache plan, alors qu'il n'est pas du tout approprié aux nouveaux arguments !
A+
"Une idée mal écrite est une idée fausse !"
http://hamid-mira.blogspot.com
Ok, donc j'ai rien dit
On ne jouit bien que de ce qu’on partage.
Je dois être idiot, mais j'ai deux questions :
1) quel est l'intérêt de mettre un GROUP BY dans la première requête alors qu'il n'y a aucun calcul d'agrégat ? De surcroit dans une sous requête ! (CTE) ???
2) pourquoi faire une restriction dans le prédicat de jointure (AND SUP.NO_CONTENEUR IS NULL), sachant qu'une jointure est une opération relationnelle et une restriction en est une autre ? Ne serait-ce pas plus logique de faire une restriction dans la clause WHERE prévue pour ce faire plutôt que dans le ON de la jointure interne ?
Enfin, vous pouvez activer l'option de configuration 'optimize for ad hoc workloads' dans sp_configure.
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/ * * * * *
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/ * * * * *
Ci-dessous un exemple de l'utilisation des variables locales pour palier aux problèmes relatifs aux "Parameter Sniffing".
Version initiale de la procédure
Version 2 de procédure où nous avons rajouté des variables locales
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5 CREATE PROCEDURE dbo.GetSalesOrderHeader_V1 (@pi_iCustid_Debut INT, @pi_iCustid_Fin INT ) AS SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @pi_iCustid_Debut AND @pi_iCustid_Fin
Explication :
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11 CREATE PROCEDURE dbo.GetSalesOrderHeader_V2 (@pi_iCustid_Debut INT, @pi_iCustid_Fin INT ) AS DECLARE @iCustid_Debut INT DECLARE @iCustid_Fin INT SET @iCustid_Debut = @pi_iCustid_Debut SET @iCustid_Fin = @pi_iCustid_Fin SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @iCustid_Debut AND @iCustid_Fin
La manière traditionnelle de traiter le paramètre sniffing consiste à assigner les valeurs des paramètres à des variables locales, puis utiliser les variables locales en lieu et place des paramètres.
Lorsque les valeurs des paramètres sont affectés à des variables locales, et que les dites variables locales sont utilisées à place des paramètres, l'optimiseur ne peut pas réutiliser le plan, au lieu de cela, l'optimiseur utilise les statistiques de la table sous-jacente et génère un plan "générique" (attention, je dis bien générique, c.à.d. il ne génère pas un plan spécifique adapté aux paramètres effectifs).
c.à.d un plan qui globalement et statistiquement est correct. Ce plan peut néanmoins s'avérer catastrophique pour certains valeurs de paramètres en particuliers !
Conclusion : L'utilisation des variables locales et l'assignation des paramètres aux variables locales est une solution au problème, mais ce n'est pas une solution miracle !
A+
"Une idée mal écrite est une idée fausse !"
http://hamid-mira.blogspot.com
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/ * * * * *
A noter que depuis SQL Server 2008, il est possible (et à mon sens préférable car plus explicite) d'utiliser le hint OPTIMIZE FOR UNKNOWN.
Effectivement mais cela a en réalité le même effet que d'utiliser des variables intermédiaires. Dans les 2 cas on force l'optimiseur à se baser non pas sur l'histogramme mais sur la valeur de densité globale pour les statistiques de colonnes concernées ... solution qui n'est pas non plus parfaite mais qui peut aider dans certains cas conne le soulève hmira.A noter que depuis SQL Server 2008, il est possible (et à mon sens préférable car plus explicite) d'utiliser le hint OPTIMIZE FOR UNKNOWN.
++
Bon après une longue interruption pour cause de vacance, me voici de retour.
Tout d'abord merci pour vos réponses.
Nous n'avons pas résolu les problèmes de performance pour l'instant.
Le principal facteur vient maintenant des I/O trop importantes.
Nous avons fait :
- passer presque tous les recorsets des application en forwardonly -> supprime les FETCH API_CURSOR (environ 30000 plan dans le cache !!!)
- appliqué les recommandations plus haut
- toutes les nuits :
1 rebuild des indexes si frag > 30%
2 reorg si frag > 10%
3 update stats de plus d'un jour (with fullscan)
- toutes les heures : update stats with fullscan sur quatre tables critiques (mais comportant une centaine de lignes chacune)
A noter que le dernier point divise par 10 le nombre d'I/O de l'une des requetes !
Comment les stats peuvent elles etre aussi fausses ?
L'optimiseur a l'air vraiment perdu. Surtout j'ai des doutes sur le moteur compte tenu des nombreuses corrections de performance apportées par le service pack 2
Le message de SqlPro m'interpelle :
Si j'ai bien compris (modestement), les deux principaux intérets des CTE sont :
- faire une sous requete avec un aggregat
- faire des requetes rescursives.
Dans tous les autres cas ils sont au moins inutiles voire contre productif.... mais je me trompe certainement.
Dans le cas présent le développeur confond DISTINCT et GROUP BY sur tous les champs.
J'avoue que le plan d’exécution entre les deux est strictement identique.... Est ce mal de confondre les deux ???
Je suis d'accord avec vous dans le cas d'une jointure interne mais si on a une jointure droite ou gauche alors le fait de mettre la restriction dans le ON ou le WHERE ne fait pas du tout la même chose !.
Par habitude nous mettons les restrictions dans le ON car il nous arrive de temps en temps de changer d'une jointure interne à une droite. Alors il n'y a pas d'erreur possible. (Sauf si effectivement on veut une jointure droite avec une restriction dans le where sur un champ de la table jointe)
Ca c'est déjà fait !!
A+
Bonsoir,
Quel langage utilisez vous pour générer des FETCH_API_CURSOR???passer presque tous les recorsets des application en forwardonly -> supprime les FETCH API_CURSOR (environ 30000 plan dans le cache !!!)
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)
Prendre conscience, c'est transformer le voile qui recouvre la lumière en miroir.
MCTS Database Development
MCTS Database Administration
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.
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
Je ne sais pas comment le vérifier... D'autant plus que certaines requetes qui partent en vrille n'ont pas de paramètres.
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 !!
Je ne serais pas étonné que vos curseurs bouffent toute la mémoire au détriment du cache !Nous avons fait :
- passer presque tous les recorsets des application en forwardonly -> supprime les FETCH API_CURSOR (environ 30000 plan dans le 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/ * * * * *
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
Prendre conscience, c'est transformer le voile qui recouvre la lumière en miroir.
MCTS Database Development
MCTS Database Administration
Nous ne générons pas nous même les curseurs. Nous n'avons pas de curseurs dans les procédures.
En fait c'est la couche ODBC qui les génère lorsque l'on déclare un recordset qui n'est pas forwardonly.
Mais le problème des curseurs est réglé en passant en forwardonly tous les recordset qui pouvaient.
Pour info, voici le morceau de code que nous utilisions avant (retravaillé pour le rendre lisible)
Nous avons découvert de plus que nous n'utilisons pas la forme préconisée par Microsoft pour l'appel au PS : il faudrait faire un {Call %PsName([args])}
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 QString l_sQuery; QSqlQuery l_oQuery; QTextStream l_oQueryStream( &l_sQuery, QIODevice::WriteOnly ); bool l_bRetCode = false; QStringList l_oClientList; // build stored proc call l_oQueryStream << "execute [dbo].Pilote_SelectDetailCommande_100 " << p_iNoDetailCommande; QSqlDatabase l_oDatabase = m_poDatabasePool->getDB( "DATABASE_CLIENT" ); // check database access if ( l_oDatabase.isValid() == true ) { QSqlQuery l_oQuery; l_oQuery = l_oDatabase.exec( l_sQuery ); while ( l_oQuery.next() == true ) { l_oClientList.append( l_oQuery.value( l_oColumnsIndex[ "CODE_CLIENT" ] ).toString() ); } // if ( l_oQuery.next() == true ) } // if ( l_bRetCode == true )
Maintenant, le problème vient des procédures qui peuvent prendre 10 I/O lorsque tout va bien et jusqu'a 100 000 I/O lorsque les statistiques des tables sur lesquelles elles lisent sont à la rue. On est obligé de faire des update stats manuellement toutes les heures (je n'ai pas encore mis le flag préconisé plus haut)
Mais ce comportement est nouveau en 2012. Nous n'avions pas ce problème en 2008 R2
Je précise que les tables en question on en général une centaine de lignes voire un millier tout au plus !
Enfin on ne passe pas par ADO parceque nous sommes en Qt et que l'on fait du multi plateforme pour la partie client. Bien que pour l'instant les applications qui accèdent à la base se trouvent aussi sur le même serveur.
Avez vous placé votre base en READ COMMITTED SNAPSHOT ?
M'est avis que vous avez un problème grave avec vos curseurs.
Essayez de déporter votre application vers un autre serveur et utiliser un serveur dédié pour SQL Server.
Les histoires de stats j'y crois pas. jamais j'ai vu qu'il fallait refaire les stats toutes les heures.
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/ * * * * *
Je viens de vérifier, la base est bien en READ COMMITED.
Pour les stats, c'est aussi la première fois que je vois cela. Je ne comprend pas ce qui se passe. Il est possible que les stats ne soient pas l'origine du problème mais que leur mise a jour corrige la cause réelle.
+
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager