Lorsque vous appuyez sur la fameuse touche “F5” de voter clavier ou lorsque votre superbe application Web ou Windows se connecte à votre Server SQL et envoie des requêtes, avez vous pensé à tout ce qu’il se passe en coulisses ?
C’est lors d’une intervention chez un de mes clients que j’ai pensé à écrire ce post. Un tour sur les compteurs de performances, quelques DMVs et rapidement j’ai vu 8.3 Gb de mémoire alloués au cache de requêtes ad hoc, plus de 160 000 dont seulement 150 étaient ré utilisées. A côté de cela, seulement 2000 plans d’exécution de procédure stockées étaient stockés en cache.
Je reprends ici quelques démonstrations que j’emploie lorsque je donne les formations Administration SQL Server et Développement SQL Server.
Tout d’abord, faisons un point sur les étapes du traitement de votre requête par SQL Server :
-
Analyse syntaxique : est-ce que votre requête est correctement écrite, vous avez bien écrit SELECT et non pas SLEECT comme cela m’arrive souvent (hum hum).
-
Algébrisation : cela permet de créer un arbre qui décrit ce que la requête fait. Deux requêtes écrites différemment peuvent avoir le même arbre car elle font la même chose, d’un point de vue logique.
-
Optimisation : chercher le meilleur moyen de traiter la requête. Quel type de jointure, dans quel ordre traiter ces jointures, quels index vont être utilisés en fonction des statistiques d’index et des conditions WHERE… Un travail énorme où SQL Server va examiner des hypothèses afin de traiter le plus rapidement la requête.
-
Exécution : l’exécution de la requête en elle même.
Waouh, et moi qui croyait simplement soumettre un petit SELECT * sur une table toute simple.
Vous avez deviné que cette phase peut être relativement longue. Petite démonstration :
Use AdventureWorks2008
Go
— affichage du temps de compilation
— et du temps de traitement de la requête
SET STATISTICS TIME ON
GO
Maintenant, exécutons une requête relativement simple :
SELECT * FROM sales.SalesOrderDetail
WHERE SalesOrderID = 43665
Passons sur l’onglet “Messages” du volet résultat de la requête :
Oups, 92 millisecondes de compilation pour seulement 17 millisecondes d’exécution. La requête est plus rapide a exécuter qu’à compiler.
Lors de la seconde exécution de la requête :
La phase de compilation n’a consommé aucune ressource, en effet, le plan d’exécution a été mis en cache. Les données elles aussi sont dans le buffer pool, l’exécution de la requête a été bien plus rapide, mais le gain le plus perceptible reste sur la phase de compilation.
D’où le grand intérêt que je porte aux compteurs de performance suivants :
o SQL Statistics : Batch Requests/Sec
o SQL Statistics : SQL Compilations / Sec
o SQL Statistics : SQL Recompilations / Sec
Si je vois un nombre trop important de compilations (ou pire de re-compilations) à la seconde rapporté au nombre de requêtes soumises au serveur, je crains pour les performances du serveur. Le processus de compilation est relativement gourment en ressources CPU et mémoire. D’autant plus que l’optimiseur ne peut pas traiter un nombre infini de création de plans d’exécution en parallèle (on peut voir ces chiffres au travers de la commande DBCC MEMORYSTATUS et dans les résultat sous forme de texte, recherchez “gateway”).
Jetons un œil au niveau du cache de procédure (que l’on devrait appeler autrement car il contient des informations pour els requêtes ad-hoc et autres …) :
Effectivement, on constate que la requête a bien été exécutée deux fois, une seule référence dans le cache. Donc, lors de la seconde exécution SQL Server a bien “vu” qu’il s’agissait de la même requête et a réutilisé le plan mis en cache à la suite de la phase de compilation.
Soyons joueur, changeons seulement le premier caractère de la requête pour mettre un S minuscule à SELECT.
Après exécution de la requête :
Aïe, deux plans, cela veut dire que SQL Server a du compiler cette requête car pour lui, ce n’était pas la même requête…
Premier enseignement : écrire les requêtes toujours de la même manière ….
Testons maintenant le changement de numéro de commande pour voir comment réagit SQL Server à la “paramétrisation” d’une requête.
Avec mes petits doigts fourchus de consultant, j’ai donc écrit un petit programme tout bête qui fait ce même select depuis une application Winform, 10000 fois …
Au préalable, je vide tous les caches :
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE(‘ALL’)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
J’exécute mon petit programme :
10 000 entrées dans mon cache. Chacune des requêtes a été compilée … Alors que finalement il n’y a que le numéro de commande qui change… Au passage, les plus affutés d’entre vous auront noté que chaque plan “consomme” 24576 octets (la requête est relativement simple ….), ce qui au final représente près de 246 Mb !!! Voilà de l’espace mémoire bien mal utilisé.
Second enseignement : que ce soit en formation administration ou développement, je dis toujours à mes stagiaires qu’il “faut” travailler avec des procédures stockées. Cela permet d’augmenter la productivité des développeurs en réutilisant du code (difficilement mesurable), ça permet de réduire le volume des échanges réseau (peu visible là aussi), mais surtout cela permet d’augmenter l’efficacité du cache de procédure ….
J’ai donc créé une procédure stockée qui fait le même SELECT :
create procedure usp_testcache
(
@orderID int
)
AS
SELECT * FROM sales.SalesOrderDetail
WHERE SalesOrderID = @orderID
go
Je vide les caches
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE(‘ALL’)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
Et j’exécute mon test en faisant appel à la procédure stockée
Vos yeux avertis auront déjà noté un gain de temps sur l’exécution.
La requête de visualisation du cache confirme ce pressenti :
Un seul plan d’exécution apparait, appelé 10 000 fois.
Le gain en terme de performance peut être conséquent sur un système en production. Tout comme le gain d’espace en mémoire.
Combien d’espace mémoire SQL Server réserve t’il à ce cache, voilà quelques chiffres :
- SQL 2000:
- max 4GB
- SQL 2005 RTM etSP1:
- 75% 0-8GB + 50% 8-64GB + 25% >64GB
- SQL 2005 SP2 et SQL 2008:
- 75% 0-4GB + 10% 4-64GB + 5% >64GB
Ces chiffres sont aussi à pondérer en fonction du nombre d’éléments dans les 4 catégories de cache.
Un fonctionnement similaire est obtenu en préparant le requête dans le code dotnet et en soumettant ensuite la requête à SQL Server en substituant la valeur du paramètre.
Un seul plan d’exécution est présent dans le cache. Notez la syntaxe issue de la préparation de la requête.
Et, en tant qu’administrateur, que pouvons nous faire s’il n’est pas possible d’agir au niveau du code dans l’application cliente pour remplacer l’exécution d’une requête par l’appel à une procédure stockée.
Il existe bien un paramètre “optimize for ad hoc workloads”. Ce paramètre a pour effet de créer un plan stub en lieu et place du véritable plan d’exécution. C’est seulement lors du second appel à cette même requête que SQL Server va supprimer le stub du cache et le remplacer par le plan véritable.
Testons ce paramètre serveur :
sp_configure ‘optimize for ad hoc workloads’,1
go
reconfigure with override;
go
Vidons les cache et exécutons de nouveau notre programme de test (option requête basique …)
Il y a toujours 10 000 entrée dans le cache de plans d’exécution, mais cette fois ci, chaque plan stub ne pèse que 216 octets.
Troisième enseignement : si votre système supporte énormément de requêtes ad-hoc et que vous constatez une pression mémoire sur votre serveur SQL, essayez d’activer ce paramètre, cela permettre de libérer quelques précieuses ressources mémoire.
Il existe bien encore un possibilité, que je réserverais à un public averti et à des DBA expérimentés : l’option de base de donnée de “paramétrisation forcée”. Avec ce paramètre activé, SQL Server va forcer la substitution de chaque littéral dans la requête SQL par un paramètre (il y a des exceptions, je vous renvoie à la documentation en ligne de SQL Server).
ALTER DATABASE AdventureWorks2008
SET PARAMETERIZATION FORCED
L’application de cette option de base de donnée a pour effet de vider le cache.
Relançons l’outil de test pour voir le résultat.
J’ai toujours mes 10 000 entrées dans le cache plan, mais tous référencent un et un seul plan réellement compilé. Cette méthode est vraiment très agressive, et je ne la recommande que dans des cas extrêmes, mais sur des systèmes avec une CPU très fortement chargée, ça peut réellement permettre de gros gains de performance.
Pourquoi est-ce dangereux : tout va dépendre de la dispersion de vos données. Si vos données sont relativement bien dispersées (d’un point de vue statistiques), cela fonctionnera bien. Si ce n’est pas le cas, vous vous exposez à un plan d’exécution déterminé par le premier contexte d’exécution qui ne sera pas forcément le meilleur et qui entrainera énormément d’IO.
Démonstration :
Regardons de près le plan d’exécution :
select * from sys.dm_exec_query_plan(0x060006003A5A1D04B880D514000000000000000000000000)
Double click sur la colonne XML query_plan
Click droit sur le prédicat Cluster Index Seek et sélectionnons “Show Execution Plan XML”.
On voir apparaitre un certain nombre d’informations et tout en bas :
nous apercevons la valeur qui a été utilisée lors de la compilation, soit dans ce cas le chiffre 1.
Si cette valeur de paramètre n’était pas représentative d’un plan d’exécution le plus adapté, notre pari est perdu. Sinon, c’est tout bon pour les performances.
D’où mon avertissement sur le danger de cette méthode ….
Il aussi tout a fait possible d’utiliser un Query Hint pour forcer cette “paramétrisation” de requête.
Vous l’aurez compris, il n’y a pas de recette miracle pour contenir et gérer votre cache de procédure. Si votre développement est propre, si vous utilisez des procédures stockées, vous aurez déjà fourni un gros effort qui paiera une fois en production. Si le DBA doit intervenir de manière relativement brutale en modifiant le comportement du serveur ou une option de base de donnée, il a tout intérêt à suivre de près les compteurs de performances pour voir si l’exécution d’une requête basée sur un plan d’exécution non adapté ne grève pas plus les performances que d’avoir laissé SQL Server gérer ses plans d’exécution comme il l’entendait. Car en cas de pression mémoire,les plan ayant un current_cost (select * from sys.dm_os_memory_cache_entries ) à 0 peuvent être supprimés du cache.
Vous pouvez aussi vider votre cache de manière sélective, par base, par type de plan ou pour un un plan en particulier:
DBCC FREESYSTEMCACHE(‘SQL Plans’);
DBCC FLUSHPROCINDB (@intDBID);
DBCC FREEPROCCACHE (@PlanHandle);
Si vous avez des questions, n’hésitez pas à me contacter.
Si vous souhaitez un audit SQL Server, je suis à votre disposition.
|
|
Christophe LAPORTE Conseil IT | Web : http://www.conseil-it.fr |
Christophe LAPORTE - Consultant 
comment mise la cache de procédure stocké
La mise en cache s’effectue automatiquement a moins de préciser with recompile dans le create procedure ce qui correspond à ne jamais utiliser le plan d’exécution en cache.
Merci pour votre blog. il est très enrichissant.
J’ai un problème avec mon sql server 2005 installer sur un OS Windows 2008 Server.
Lorsque je lance une requete sql, après l’execution de cette requete la memoire ram reste statique,elle ne se vide pas. Comment puis-je m’en sortir.
Merci
Ce phénomène est tout a fait normal.
Je ne sais pas comment est configuré votre intance SQL au niveau des paramètres mémoire.
En mode mémoire dynamique, la mémoire ne sera restituée à l’OS que si un autre process hors SQL Server a besoin de mémoire et que le serveur est sous pression.
Si on est en mémoire statique (min memory = max memory) et que vous avez donné les droits Lock Page In Memory au compte de service, la mémoire ne sera pas désallouée, à moins de réduire le max memory …. Flusher les caches n’y changera rien.
Ping : Mauvaises pratiques : le SELECT * | Christophe LAPORTE – Consultant SQL Server
Merci pour ce blog effectivement très enrichissant…
J’ai rencontré il y a quelques jours un problème concernant je pense mon sql server 2008 installé sur un windows server 2008.
Le temps de réponse entre mon site et la bdd est devenu en quelques heures bien plus long que la normale au point d’avoir des délais de script dépassé.
Pour régler ce problème, j’ai supprimé tous les processus de sql server à la main afin de reprendre l’activité.
Le site est en ligne depuis bientôt deux ans et je n’ai jamais eu ce type problème.
Dans mon observateur d’événement j’ai une erreur concernant la source MSSQLSERVER :
« The client was unable to reuse a session with SPID 77, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. »
Auriez vous une idée ou une piste pour mes recherches?
Je vous remercie par avance
Bonjour,
Difficile de se prononcer sans de plus amples investigations. Mais cela m’inspire plusieurs choses :
– Vérification au niveau des locks
– Vérification des latches
– Vérification des sous-systèmes (dique, cpu et mémoire)
– Intérrogation de sys.dm_os_wait_stats
– Archi 32 bit ou 64 bit ? Combien de worker process ? Combien de cnx simultanées ?
J’ai été confronté à ce genre de problèmes qques fois. La résolution n’est pas simple. Mais je commencerais pas appliquer le dernier service pack et le dernier CU pour SQL Server.
Bonjour et merci pour ce blog qui est une excellente source de renseignement.
J’ ai un SQL Server 2008 avec des procédures stockées dans un job s’ exécutant toutes les heures. (64 bit)
Il y a deux steps qui dure environ 2 minutes.
Depuis ce matin 9 h , le premier step est passé de 2 minutes à 25 minutes sans qu’il y ai eu de modification. le traitement de 8 h était tout à fait normal. Tout les exports du step1 mettent 25 minutes depuis. le step 1 est un insert à partir d’un select et marche depuis quasiment 3 mois.
Je n’ai pas d’idée ou chercher. Peut être auriez-vous une piste ?
Bonne continuation
Bonjour,
Sans avoir accès au serveur, difficile de se prononcer. Vus exportez de SQL Server vers où ? Une autre table dans la mêm ebase, une table sur une autre base, une table sur une autre instance SQL ?
Je regarderais tour d’abord du côté systèmes (antivirus, disque qui supporte autre chose), ensuite le verrouillage, les accès concurrents sur les mêmes données.
Ensuite, il se peut aussi que le volume des données ait atteint un seuil qui implique un changement de plan d’exécution pour vos requêtes. Regardez aussi les statistiques d’index qui, même avec un autoupdate ne sont mises à jour que une fois qu’il y a eu 20% (+500) des enregistrements mis à jour. Regardez aussi au niveau de votre journal de transaction, qu’il ne soit pas plein avec des évènements de type agrandissement automatique.
Mon conseil, regardez niveau système et ensuite, réindexez (rebuild) tous vos index. Eventuellement, videz le cache de procédure. Si le phénomène persiste, cela risque de demander une analyse plus approfondie. N’hésitez pas à me contacter, une intervention à distance est tout a fait envisageable.
Christophe
bonjour,
je suis confronté éxactement a ce problème de dispersion des données.
Nous avons une application java et utilisons hibernate pour générer les requetes (donc nous ne gérons pas l’écriture de celle ci) et elles sont « paramètrisées ».
Au démarrage de notre application, une requete est exécutée avec entre autre deux paramètres de type dates, et le résultat de la requete est vide.
de cette requete, SQL génère un plan d’exécution.
Le problème est que lorsque nous exécutons cette même requete avec des dates différentes, SQL server utilise le plan déjà généré mais qui n’est pas optimisé pour cette requete lorsqu’elle retourne des données. Le résultat est assez édifiant car la table étant assez volumineuse, on passe de 0ms d’éxécution a plus de 500ms.
Si je vide le cache de plan d’exécution avant de faire cette requete avec des données en sortie, la plan utilise les bons index et on retrouve des temps acceptable.
Le problème est qu’après un redémarrage de SQL Serveur (donc vidage des caches de plan) la 1ere requete effectuée retourne aucun résultat et le plan n’est pas le plus optimisé. pour parfaire le tout, j’ai éssayé de créer un « repère de plan » pour forcer SQL serveur a utiliser le plan d’exécution voulu pour cette requete, mais hibernate change le nom de certain des paramètres lors de l’exécution en incrémentant des indices, donc le plan de requete est re-créé et donc pas le plus optiimisé. Donc cette solutions n’est pas valable.
Es ce que quelqu’un a déjà été confronté a ce type de problème et existe t-il un paramètre ou option afin de palier a ce comportement ? Peut on invalider un plan a partir d’un certain temps ou des données en entrée sans que la structure de base ni les données n’ai changée ?
Cordialement,
Eric
Bonjour,
Essayez d’introduire l’option optimize for unknown au niveau de votre requête source (désolé, pas trop le choix, on aurait pu le mettre en plan guide, mais Hibernate vous interdit cette possibilité du fait du renommage des paramètres).
Cette option de requête va indiquer à l’optimiseur de requêtes de travailler non pas avec l’histogramme des statistiques d’index mais avec la densité des données. Le plan généré sera alors compatible avec la majeure partie des données.
Mais sachant qu’il vous faut retoucher la requête initiale, ajouter directement le HINT pour l’utilisation de l’index est aussi une bonne solution.
Lors des cours que je dispense, je mets très souvent l’accent sur le fait d’utiliser des vues, des fonctions et des procédures stockées. Elles permettent de protéger le schéma, et donc fournissent une certaine latitude de modification, sans pour autant devoir redéployer une application. Dans votre cas, si vous étiez passé par une fonction de type table, une procédure stockée ou même une vue, les plan guide aurait pu être appliqué, et il aurait aussi été possible de modifier directement l’objet pour tester les perfs sans impacter votre applicatif.
Bonne journée,
Christophe
Bonjour,
Quels risques et/ou avantages a-ton de vider les caches au niveau des performances et temps d’attentes ?
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE(‘ALL’)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
Tous les caches sont-ils vidés systématiquement au redémarrage du serveur ?
J’ai une base de données sous SQL SERVER 2008 64 dédié qui tourne sous Win Server 2008 avec 4 proc et 8G RAM avec une application en java tournant sur un serveur séparé.
J’ai de ralentissements très important au niveau de la base avec des temps d’attentes importants sur les buffer I/O et logging.
Je ne travaille pas sur les requêtes faites sur la base mais seulement sur la partie SQL.
Une réplication de 70% des tables de la base est faite sur un autre serveur toutes les 6h durant en moyenne de 30s à 2min.
Aurait-il un moyen d’améliorer les performances de ma base qui est en production 7j/7 24h/24 sans impacter la production ?
Cordialement,
François
Bonjour
Vu le nombre de questions que vous posez il em faudrait une bonne journée pour y répondre.
Pour aller à l’essentiel, si vous avez des waits de type logging et Buffer IO, commencez par investir dans un bon sous sytème disque. Maintenant, bien des facteurs peuvent influer sur les accès disques, comme la mémoire, la façon dont celle ci est occupée, le ration full scan / index searches, les transactions, les modes d’isolation, etc …
Vider les caches peut avoir un intéret, dans certains cas, mais cela va entrainer une charge supplémentaire niveau disque et niveau CPU, puisqu’il va falloir recompiler les requêtes. Jetez un oeil aux index, à la fragmentation, aus statistiques d’index et au vérouillge. Une fois tout cela étudiée de près, vous aurez éliminé bon nombres de problèmes pouvant potentiellement ralentir votre système.
Si vous souffrez de ralentissement, faites appel à un consultant qui sera en mesure de vous aider, d’autant plus si votre serveur est critique dans votre infrastructure.
Cdlt,
Christophe
Bonjour,
Nous avons un site Web (ASP) qui attaque une BDD Sql Server 2008 et depuis une semaine je suis confronté à un souci j’ai une requête ou plus précisément une procédure stockée que j’appelle depuis IIS et qui met environ 9 secondes à répondre alors qu’elle met entre 14 et 15 ms à répondre depuis la console du serveur.
Au niveau du nombre de connexions simultanées cela tourne autour de 100 pendant environ 35 45 minutes puis plus rien, pendant ce temps le serveur s’emballe et l’utilisation du proc est pratiquement à 100%. Du coup mes pages mettent entre 4 et 19 secondes a s’afficher.
Auriez vous une piste ?
Merci encore pour votre blog.
Eric
Bonjour,
Il est possible d’explorer rapidement les pistes les plus probables, côté SQL Server et côté IIS.
Je vous contacte.
Cdlt,
Christophe
Bonjour,
j’ai exactement le même problème.
Pouvez vous me donner des pistes d’investigations ?
Merci
M. Thevenet
Si vos métriques systèmes paraissent correctes, vos index reconstruits, jetez un œil aux DMV pour repérer les requêtes et procédures stockées dont les durées d’exécution vous paraissent trop importantes, ou celles générant un grand nombre d’IOs.
Pour ma part, je creuserais sur des requêtes complexes dont les jointures sont traitées différemment entre un 2000 et un 2005/8/12. Comparez les plans d’exécution avant et après.
Christophe
bonjour,
comment effectuer des requêtes programmées sur SQL SERVER
Bonjour
La notion de requête programmée n’existe pas en SQL Server. Si vous faites allusion à l’équivalent MySQL, alors, cela se traduit par un travail dans l’agent SQL Server. Chaque travail dispose d’une ou plusieurs planifications et peut être divisé en étapes suceptibles de s’enchainer entre elles. Chaque étape peut être de type TSQL, PowerShell, ligne de commande, réplication, etc ..
Cdlt
Christophe
Bonjour,
Tout d’abord un grand merci pour l’article et pour le site entier, vraiment une mine d’information.
Je rencontre actuellement des lenteurs sur une application développée en .NET avec une BD SQL SERVER 2008 R2.
En consultant l’activité monitor de sql server, j’ai un latch de 64445 sec (soit 17 heures) sur une base qui a été migrer sur un nouveau serveur il y a a peine 3 semaines (32 Go de mémoire et 8 proc).
Quand les utilisateurs rencontrent des grosses lenteurs, un développeur en interne exécute les 2 requêtes suivantes (exec sp_updatestats,dbcc freeproccache) et apparemment ça améliore les perf pendant quelques heures.
un plan de maintenance tourne tous les soir à 22h pour rebuilder les index.
Avez vous une idée d’où peut venir nos problème de performance?
A partir de quel seuil peut-on considérer que le latch est trop élevé ? et comment peut-on le diminuer?
Je vous remercie par avance de la réponse.
zerjad
Bonjour
Quelle colonne regardez vous dans le monitoeur d’activité ? La durée totale, ou bien la durée actuelle ?
Le LATCH est un méchanisme de protection de la ressource physique : a un instant T, un seul process peut accéder à une ressource.
De quel type de latch s’agit t’il ? PageLatch, PageIOLatch ?
Préférez un requête TSQL plus précise que le moniteur d’activité pour comprendre l’origine du problème.
Glenn Berry, dans son fichier de diagnostic, fourni une requête qui vous permettra de voir le côté historique des waits.
Lorsque les lenteurs sont relevées apr les utilisateurs, il faut alors requêter les DMV sys.dm_exec_requests, dont une colonne renseigne sur le type d’attente en cours pour chaque requeête.
Regardez aussi sys.dm_exec_waiting_tasks qui vous donnera la liste des attentes à l’instant T.
Si votre problème de latch est un PageIOLatch, regardez alors la latence disque et les IOPS supportés par votre serveur. Regardez aussi les DMV erlatives aux missing_indexes. S’il s’agit de PageLatch, il faut creuser davantage, mais le partitonement de table, ou le changement d’index cluster peut solutionner le problème.
Cdlt,
Christophe
Bonjour,
Tout d’abord, un grand merci et félicitation à vous pour votre blog car ceci va aider beaucoup de monde.
J’ai un problème sérieux depuis un temps. Nous avons passé de SQL 2000 vers SQL 2008 sous Windows 2008 R2 avec un Serveur équipé de 32Go de mémoire. Notre application est développé sous Windev. Depuis ce changement, notre application est devenu extrêmement lente. Il faut préciser que sous la version SQL 2000, l’exécution était bonne mais par contre sous SQL 2008, c’est devenu une catastrophe. les utilisateurs se plaigne de temps en temps.
J’aimerai si possible que vous m’aidiez à résoudre ce problème si vous avez déjà été affronté à ce problème au paravent.
Merci beaucoup à vous.
Dolph
Bonjour
Les différences entre le moteur SQL2000 et 2005+ cons assez connues, notamment sur le traitement interne des jointures.
La premier réflexe à avoir est de réindexer toutes les tables la base. Après une migration, c’est une quasi obligation. J’espère que cela a été fait.
Malheureusement pour vous, j’ai également été confronté à des pbms de perf, lorsque le client était développé sous WinDev. Et j’avoue ne plus être fan de cet outil depuis !
Il fonctionne assez bien avec la base fournie en standard, dont j’ai oublié el nom. Mais après un portage sur SQL, les perfs se sont révélées être désastreuses. Malgré un gros serveur.
Il n’y a pas de solution miracle. Il faut investiguer. Repasser sur tous les compteurs de perf Windows & SQL. Puis jeter un œil aux DMVs … La cause des ralentissements devrait apparaitre assez facilement.
Essayer également de voir si vous pouvez retrouver un peu de perf en remplaçant des tables (sur lesquelles sont effectuées des clauses where) dans des grosses requêtes avec beaucoup de jointures par des fonctions de type tables avec des paramètres. Cela peut forcer SQL à exécuter cette partie de la requête et générer un plan d’exécution différent …
Essayez également de travailler avec des planguides, pour corriger un plan qui ne serait pas correct.
Christophe
Bonjour et merci beaucoup pour votre réponse.
A notre niveau, le concepteur de l’application que nous utilisons et qui est développé sous Windev ne veut même pas reconnaître que c’est lenteur sont liés à l’application.
Ils ont passé tout le temps à accuser notre réseau, alors que bien que nous étions sous ces même réseaux locaux et sous la version SQL 2000, on arrivait à travailler avec de meilleurs performance et dans ce temps, une simple machine avec 2 Go de RAM pouvait même servir de Serveur.
Nous avons même entamée les réaménagements de nos réseaux.
C’est depuis le passage de SQL2000 vers SQL2008 que nous rencontrons ces problèmes atroce de lenteurs.
Il faut que je vous précise ici que ce changement de version nous a été proposé du faite que nous sommes passé en Interconnexion de 10 Agences et il nous fallait consolider toute nos bases pour en avoir qu’une seule (10 bases ont été consolidées). Du coup, aujourd’hui, nous avons une base unique plus lourd qu’avant et qui tourne sur SQL2008. Nous avons dopé notre Serveur HP DL380 en 32 Go de RAM mais rien à faire, toujours les lenteurs.
Sur ce, avez vous d’autre directive à me donner ? Merci d’avance.
Dolph
Si vous avez déjà exploré les pistes classiques, à part vous conseiller de faire appel à un consultant (je ne suis qu’un parmi d’autres) pour vous aider à solutionner votre problème si vous pensez ne pas avoir les compétences en interne, je crains de ne pas pouvoir vous aider davantage sans auditer votre serveur.
Désolé.
Christophe
Bonjour,
je voulais savoir si les sources de votre application winforms sont disponibles ?
Sinon, pourriez-vous me dire quelles sont les différences de code entre l’exécution d’une requête Ad-hoc et d’une requête préparée ?
Bonjour
je ne suis plus spécialement développeur. je vous prie donc de me croire sur paarole, ce bout de code en VB.Net n’avait rien d’extraordinaire. Création d’une connexion, création d’un objet commande, et exécution de la requete.
Une requete ad-hoc arrive tel quel sur le serveur. Il doit donc « sniffer » le paramètre, c’est à dire, extraire de la condition where des littéraux qui peuvent sembler etre des paramètres.
Pour une requete préparée, dans un premier temps, le « modèle » de la requete va etre envoyé au serveur, avec l’ordre sp_prepare, contenant le texte de la requete, la liste et le type des paramètres. Ensuite, un ordre sp_execute va etre lancé pour exécuter la requete paramétrée, en substituant les valeurs de paramètres. Pour finir, l’odre sp_unprepare est exécuté.
Une des différences fondamentales réside sur al compilation, ou du moins la réutilisation du plan d’exécution. Une requete paramétrée va réutiliser le plan d’exécution, alors qu’une requete ad-hoc aura davantage tendance à etre compilée systématiquement. En fait une requete autoparamétrée apparait dans le plancache, mais les requetes ad-hoc sont toutes bien visibles. Ce qui pollue le cache de procédure, gache de l’espace, d’où l’utilisé du paramètre optimize for ad-hoc workload.
Bonjour et merci pour votre article,
Je vais creuser votre blog car d’autre articles pourront surement m’aider à en apprendre toujours plus sur SQL Server.
J’aurais une question concernant la rapidité d’une requête avant et après que l’arbre soit compilé.
En faite, je vais faire des statistiques en reporting pour un client. Rarement appelé sauf en fin de mois et par 1 ou 2 connexions maximum.
Sauf que la base va se remplir seconde après seconde et la recherche risque d’être très longue.
J’avais déjà dans l’optique de créer des vues ou des procédures stockées pour alléger les rapports SQL, mais avec votre article ce sera obligatoire pour gagner en temps.
Ceci dit, dois-je créer des jobs pour exécuter mes SELECT une à plusieurs fois par jour pour que le client n’attendent pas 20s (sait-on jamais) pour voir ces statistiques ?
Ou, dois-je lancer mes SELECT via des JOB tout en faisant des INSERT dans une table annexe que je viendrais simplement interroger dans mes rapports ?
En vous remerciant par avance.
Have fun.
Bonjour
De quelle volumétrie parle-t-on ? Plusieurs millions d’enregistrement ?
Avant de se lancer sur ce genre de choses, attendez de tester sur une volumétrie représentative.
Autre piste : SSRS propose de pré calculer le rapport et de le mettre en cache. Vous pouvez planifier cette tâche toutes les heures ou 2 heures et vos users accèderons au rapport en cache.
Cdlt
Christophe
Je vais me renseigner sur le pré-calcul des rapports, ce serait une bonne solution.
Il est vrai que mes premiers calculs sont un peut faussés avec moins d’une centaines de données dans ma base de test, car si je me fit à la demande de mon client, on pourra atteindre les 3 à 5 millions de data/ans.
Merci pour votre temps
bonjour Christophe
merci pour ce article qui s’apparaît très intéressant pour les DBA surtout qu’il attaque une partie très importante pour sql c’est le mis en cache des procédures
sur ce piste je rencontre un problème de snifing c’est que mes procédures qui ont des paramètres différents utilise la même plan du cache et je serai a chaque fois obliger de faire une nouvelle recalcule des statistiques pour dé valider l’ancien plan et de créé un nouveau plan
l’option with recompile je ne peux pas l’utiliser vue qu’elle besoin du ressource CPU et RAM
je rencontre ce problème a chaque fois j’ai un redémarrage du MSSQL
Tu peux stp m’aider a un piste pour analyser le problème
Bonjour
SI le problème ne se produit que après que le serveur démarre, alors le problèmes est ailleurs. Peut être faut il creuser une piste sur les disques car le buffer pool est vide, il faut alors mettre en attente les requêtes, le temps de monter les pages de données en mémoire. On peut essayer de forcer un peu les choses en faisant des gros select * sur les tables les plus volumineuses de manière à forcer des read-ahead. mais le risque c’est alors de consommer de la mémoire pour des données non utilisées.
Si le problème ne se produit que après un start du service, je recommande de ne prendre les mesures qu’une fois passé une demi-heure ou une heure. Le temps de prendre un peu de charge, de monter des données en RAM et de compiler quelques plans.
Est-ce qu’il y a des procédures de recalcul de stat ou des réindexation régulières ?
Christophe
Est-ce qu’il y a des procédures de recalcul de stat ou des réindexation régulières ?
non pas de recalcule de stat ou réindexation régulier –>serveur du production 24/24
la partie maintenace est ce faite de façon hosard sur les index trés fragmenter avec un
recalcul des stat sur 50% sample en attaquant juste les table les plus utilisés
Combien pèsent ces tables ?
1 000 000 000 d’enregistrements ? Plus ?
Avez vous mesuré le temps de recalcul de stats.
Bonjour Christophe
si j’aurai la même phénomène a la prochaine démarrage du MSSQL et je fait un
exec sp_recompile « non du procédure_stocké » sans attaquer faire un recalcul du stats est ce je peux avoir une amélioration du temps d’exécution ???
Bonjour Christophe,
Et tout d’abord merci pour votre page qui est d’un grand secours pour beaucoup d’utilisateurs.
Nous sommes sur SQL Server 2005 et nous l’utilisons comme datawarehouse. J’ai constaté des temps d’exécution longs et après analyse le constat est le suivant:
Dans une requête, si j’utilise des filtres de période en dur dans la clause where, les temps d’exécution sont corrects.
Ex: select * from ma_table_de_fait where periode between 201401 and 201501
Pour cette même requête, si j’utilise des fonctions qui calculent la date automatiquement, le temps d’exécution est multiplié par 5 ou 6 et je constate des temps d’attente importants dans la console d’administration avec pour type d’attente PAGEIOLATCH_SH.
Ex: select * from ma_table_de_fait where periode between dbo.isperdebn1(getdate()) AND dbo.isperfinn(getdate())
Descripion des fonctions:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[ISPerdebN1] (@DATE datetime)
RETURNS int
AS
BEGIN
— Renvoit le premier mois de l’année N -1 (par rapport à la date passée)
DECLARE @ISPerdebN1 int
SET @ISPerdebN1 = cast(DATEPART(yy,@DATE)-1 as char(4))+’01’
if DATEPART(mm,@DATE)=1
set @ISPerdebN1 = cast(cast(DATEPART(yy,@DATE)-2 as char(4))+’01’ as int)
RETURN(@ISPerdebN1)
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[ISPerfinN] (@DATE datetime)
RETURNS int
AS
BEGIN
— Renvoit la période de fin par rapport à la date donnée
— c-à-d le dernier mois avant la date passée
DECLARE @ISPerfinN int
SET @ISPerfinN = cast(DATEPART(yy,@DATE) as char(4))+Right(’00’+cast(DATEPART(mm,@DATE)-1 as varchar(2)),2)
if DATEPART(mm,@DATE)=1
set @ISPerfinN = cast(cast(DATEPART(yy,@DATE)-1 as char(4))+’12’ as int)
RETURN(@ISPerfinN)
END
J’aimerais optimiser cela mais je ne sais pas trop comment m’y prendre. Les fonctions utilisées sont elles exécutées pour chaque ligne ? Si c’est la cas, cela va-t’il générer beaucoup d’I/O ?
La contrainte importante est que comme les requêtes sont générées par Business Objects je suis bridé en terme d’optimisation.
Merci d’avance pour votre aide.
Loic
Sachant que vous n’utilisez que des constantes sur l’appel des fonctions, SQL Server ne doit pas exécuter la fonction pour chacune de lignes. ce ne serait pas le cas si le paramètre de la fonction était une colonne de la table, car SQL Server ne pourrait pas présumer du résultat de la fonction sans l’appliquer à toutes les lignes et donc se lancer dans un scan d’index cluster ou non cluster.
Dans votre cas, le symptôme de PageIOLatch signale que votre requête est mise en attente (suspended) le temps que des requêtes d’entrées sortie disque soient réalisées. Le SH signifie Share, donc pour de la lecture, sinon, vous auriez UP.
Difficiel de vous donner la raison exacte sans regarder de plus près quelques DMV bien choisies, mais en première approche, je pencherais pour un problème de parameter sniffing. Lorsque des valeurs en dur sont données, la requête fonctionne bien. J’imagine avec un non cluster index seek sur cette colonne date. Mais dès que l’on remplace par la fonction (qui j’imagine donne la même tranche de dates pour le test), la requête est plus longue avec des IO. De fortes chances que l’optimiseur de requêtes ait opté pour un Cluster Index Scan, donc lecture complète de votre table de fait, qui doit être relativement volumineuse.
pour en être certain, une fois la requête terminée, regarder le plan d’exécution dans sys.dm_exec_cached_plans. Et comparez les informations Compile Value et RunTime value (tout en bas de l’XML du plan d’exécution). Et comparez également le plan avec la version « correcte ». Vou y verrez probablement des différences.
Assurez vous aussi que vos statistiques d’index soient correctes.
Pour finir, pour BO, je ne sais pas trop ce qu’il y a à faire, sauf a essayer de faire calculer à BO les dates pour les envoyer en dure à SQL Server, dans la requête. ou bien essayer de faire du code dynamique.
Cdlt
Merci de votre réponse. Je vais essayer tout cela.
bonne soir a tous … s’il ya quelcun alors j’ai besoin d’aide conne intepréter le lpan d’execution d’une requête du base de bonnée sous oracle entreprise manager 10g
Merci beaucoup pour votre cours !!
bonjour Christophe
tout d’abord bravo pour le côté didactique du sujet
je déterre cette page, car je suis confronté à un phénomène particulier et je ne sais l’expliquer
j’ai une requête plus ou moins complexe, on va dire composée de plusieurs jointures et fonctions.
les index et stats sont bien calculés et elle me retourne pour donne run exemple précis Temps UC = 188 ms, temps écoulé = 296 ms. pour 143 lignes
jusque là tout va bien
Lorsque je l’encapsule dans un select ou l’enregistre en tant que vue les temps deviennent catastrophiques Temps UC = 7784 ms, temps écoulé = 8086 ms.
auriez vous des pistes à me conseiller ?
merci