Discussion :

[Pol63]

bonjour

un petit soucis sur une base sous sql 2005
on demarre le serveur, 10 exe sont connectés dessus, sql server prend 400Mo de ram (la base en fait 500 réels) et 3% du processeur
je fais une trace avec profiler pour voir les requetes qui prennent plus de 20ms
quasiment la meme revient toujours (elle est exécutée toutes les 500ms par chaque exe), entre 20 et 30ms et elle utilise 1500 reads à peu près

quelques heures plus tard, les 10 clients deviennent poussifs
sql serveur prend 1.5Go de ram, 15% processeurs, et sur la meme conf de trace, la requete précédente prend 400ms et toujours 1500 reads

quelqu'un aurait une idée de quoi ca peut venir ?

[SQLpro]

Jouez vous vos trace en live ou sous forme de procédures stockées ?
Placez vous les traces dans une table ou un fichier ??

A +

[Pol63]

traces depuis profiler, ni en table, ni en fichier

en table on avait essayé, les durations sont gonflés et ne semblent plus correspondre entre start et end

[SQLpro]

Implémenter un profiler sous forme de proc stock lancées à distance et dans un fichier rotatif.
Parce que là visiblement vous auditez l'auditeur.

A +

[Pol63]

même si profiler prend un peu de temps, il explique quand meme bien le phénomène
les exe deviennent poussif, je démarre ensuite une trace et en effet mes requetes mettent 10x plus de temps à s'exécuter

avoir une trace réelle, ne m'apportera rien de plus en me montrant le même effet avec d'autres valeurs plus réelles

et moi c'est pas les effets que je veux connaitre, c'est la cause ... (ou un moyen de la trouver)

les verrous sont à priori des S ou des IS, pas vu de verrous de type U ou X ...


et par fichier rotatif, vous entendez quoi ?

[SQLpro]

Quel est le client ? Comment a t-il été développée ? Par quel outil ?

A +

[Pol63]

un exe .net qu'on a développé


c'est revenu à peu près à la normale ... on ne sait pas trop pourquoi ...
les journaux n'indiquent rien de spécial

on va voir si va revient vu que ca fait plusieurs jours qu'on a ce soucis, et en rebootant ca repart à fond

[SQLpro]

Avez vous d'autres exécutables sur le serveur ou c'est un serveur dédié ?

A +

[SQLpro]

Je reprends cela :

elle est exécutée toutes les 500ms par chaque exe), entre 20 et 30ms

Cela veut dire : 30ms * 10 utilisateurs = 300 ms. Ajoutez-y les temps ,des autres requêtes et petit à petit vous allez commencer à avoir des chevauchement d'exécution qui vont induire des attentes de verrous qui vont escalader façon avalanche !

Le seul moyen de la savoir est de voir s'il y a des chaine de blocage en laissant bien pourrir la situation...

En conclusion ce genre de gadget à la WIndev pour rafraichir un écran est généralement une vaste c...

A +

[Pol63]

le server ainsi qu'sql server ne sont que pour nos applications

des chevauchements d'exécution, c'est un peu obligé, sql server ne sert pas qu'à faire des requetes toutes les 2 minutes ...

par contre on a très peu d'update, on verifie avant de les faires s'ils sont nécessaires, donc on a beaucoup de lecture, qui à priori font des verrous partagés

entre les moments ou ca marche bien et les moments ou ca marche mal, le nombre de requetes/s a l'air dans le meme ordre de grandeur


dans management studio/gestion/moniteur d'activité, on a entre 50 et 100 connexions apparemment


sinon pour le rafraichissement d'écran en "temps réel", c'est bien pratique quand meme ...
mais si vous avez des suggestions pour éviter de faire des requetes ...

[SQLpro]

Le but des applications client serveur est justement d'éviter d'avoir à faire des rafraichissement parce que les curseurs sont en live sur les données...

Dans le pire des cas vous pouvez implémenter une technique de call back, c'est à dire être tenu informé des modifications concernant les lignes que vous scrutez. C'est un peu complexe à mettre en oeuvre, mais beaucoup plus efficace.
Pour en connaître le principe, lisez l'article que nous avions écrit dans delphi news il y a plus de 12 ans :
http://brouardf.club.fr/DelphiCallBack.html

A +

[Pol63]

je veux bien croire que ma requete toutes les 500ms c'est pas ce qu'il y a de mieux

mais ma question initiale est pourquoi ma liaison .net / sql server se ralenti
ca vient encore d'arriver, toutes les requetes deviennent beaucoup plus lentes
meme avec 2 reads sur une requete select ch1 from table where ch2 = valeur avec ch2 comme pk et un index sur ch2/ch1 on passe de 0ms à plusieurs dizaines de ms ...
un redémarrage du service sql server remet tout en place

[Pol63]

un peu de nouveau :


quand on arrête le service agent SQL, la charge processeur du service sql server passe de 12% à 2%
et les requetes redeviennent instantanément rapide

par contre au bout de quelques heures, ca réaugmente encore, plus doucement, mais je trouve pas ca normal
c'est les meme requetes qui passent tout le temps, vu qu'on ne fait que du select, pas d'insert ni d'update le temps ne devrait pas varier ...

on utilise des variables tables @, les rares curseurs qui trainent sont bien closés et deallocatisés

les transactions démarrés explicitement dans une connexion, si on ferme la connexion sans avoir fait commit ou rollback, ca rollback ? (on cherche des sources de problème possible, on a pas mal de code, ca pourrait arriver qu'on ai oublié une ligne)

[SQLpro]

des variables tables @

C'est pas mieux que des tables temporaires (elles sont physiquement instanciés dans la base tempdb.

A +

[Pol63]

oui enfin apparemment c'est quand même plus rapide, y a pas d'inscriptions dans le log

enfin ca ne change pas le problème, sql server autorise l'utilisation de tout ce qu'il peut faire, y compris les tables @ # ou autre

mon server fait les meme requetes SELECT en boucle pendant plusieurs heures, et le temps d'execution de ses requetes grandit anormalement


tempdb.mdf fait 8Mo, donc je pense pas qu'elle grossit dans le temps

[SQLpro]

oui enfin apparemment c'est quand même plus rapide, y a pas d'inscriptions dans le log

Si...

Essaye de voir si de fixer les tailles des fichiers des bases de dprod et de tempdb et msdb résous le problème. Comme je l'ai indiqué ici : http://blog.developpez.com/sqlpro/p5...fichiers-et-t/
cela à de forts impacts sur les perfs.

Pour tempdb, il faudra rebooter les services SQL

Sinon, difficile de t'en dire plus. Il faudrait auditer.
Pour l'agent SQL Server, voir quels sont les jobs...

A +

[Pol63]

pas d'autoshrink
les bases j'avais déjà lu que tu disais de les mettre grosses dès le départ
on a mis 1Go pour l'instant et la base prend moins de 600Mo
le log on a mis 3Go
sauvegarde data 2x par jour
sauvegarde log toutes les 2H

il nous arrive de rappatrier les bases clients chez nous, et donc si je mets 8Go pour chaque, c'est moins pratique, quand j'aurais trouvé comment faire des backup ou des restore en spécifiant la taille, j'augmenterais la taille chez les clients
au passage tu aurais pas une technique pour avoir un fichier compressé ? (j'utilise backup database with copy_only)


pour le fait que les tables @ font beaucoup moins de choses que les tables # je l'ai lu dans msdn hier ... en touta cas en passant les tables # en table @ on a gagné pas mal en temps de traitement

l'agent SQL on ne s'en sert pas tout est vide dedans ...
par contre on avait fait des tests, il en reste peut être des reliquats dans des tables systèmes ...
on a aussi la table de conversation du broker qui est géante, du à quelques tests de dependancy, mais on ne l'utilise plus


j'ai trouvé aussi ca
http://technet.microsoft.com/fr-fr/l...40(en-us).aspx
intitulé Troubleshooting Performance Problems in SQL Server 2005
je suis en train de regarder un peu
j'ai utilisé quelques commandes DBCC dont DBCC FREEPROCCACHE et ca a soulagé un peu aussi



question subsidiaire, comment faire l'équivalent d'un order by ni asc ni desc, mais selon un choix de l'utilisateur

[SQLpro]

tu aurais pas une technique pour avoir un fichier compressé ?

A partir de 2008 y'a la compression des sauvegardes.

en touta cas en passant les tables # en table @ on a gagné pas mal en temps de traitement

Certes mais le mieux est ni l'un ni l'autre (faisable avec CTE notamment).

on a aussi la table de conversation du broker qui est géante, du à quelques tests de dependancy, mais on ne l'utilise plus

Service broker ? Ca peut impacter...

j'ai utilisé quelques commandes DBCC dont DBCC FREEPROCCACHE et ca a soulagé un peu aussi

A éviter car tu perd toute la mis en cache des plans de requêtes et procédures.

Ce que tu peut faire et qui mange pas de pain :
ALTER DATABASE mabase
SET PARAMETRIZATION FORCED.

D'autre part avez vous utilisé SYSTÉMATIQUEMENT les préfixes de schéma ? (dbo. ... ) ???
Parce que ça c'est très impactant sur de grosses requêtes ! (chez un client en audit l'absence des péfixes de schéma coutait 80% des ressources...).

question subsidiaire, comment faire l'équivalent d'un order by ni asc ni desc, mais selon un choix de l'utilisateur

Différente solution :
1) avec CASE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ORDER BY CASE... END

2) avec table de tri
Ajout d'une table de tri
CREATE TABLE dbo.TRI

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
(TRI_USER_ID INT NOT NULL PRIMARY KEY NON CLUSTERED,
 TRI_ORDRE INT NOT NULL DEFAULT 0);
CREATE INDEX X ON dbo.TRI (TRI_ORDRE);

Remplissge et jointure avec cette table.

A +

[Pol63]

j'aurais pas pensé au cas dans l'order by ca le fait bien


nos 2 bases clients avaient encore ralenti ce matin, j'ai testé sur un de vider le cache et sur l'autre d'activer l'option PARAMETERIZATION
dans les 2 cas le serveur a apprécié
on va voir si ce soir un seul ou les 2 auront remonté


pour les dbo. on les as mis que sur les fonctions car c'est obligatoire
m'enfin on a pas créés d'autres schémas
sur que ca joue ?

[SQLpro]

pour les dbo. on les as mis que sur les fonctions car c'est obligatoire
m'enfin on a pas créés d'autres schémas
sur que ca joue ?

Enormément.

80% du temps perdu à caude des absences de schéma du fait de la résolution de noms.
voici ce que j'ai dit dans mon dernier audit :

******

[...]
Ce que l'on peut constater c'est que :
• la première exécution va chercher les données du disque (physical reads et read_ahead reads > 0)
• la seconde exécution n'a plus besoin d'aller chercher les données sur le disque parce qu'elles sont dans le cache.
Néanmoins quelque chose de surprenant se produit : alors que nous lançons toujours la même requête, le temps de compilation est toujours le même (17 secondes) alors qu'en principe le plan de requête élaboré lors de la première exécution aurait du être repris du cache. Or il n'en est rien...
Ceci s'explique à la lecture de la requête : aucun préfixe de schéma n'a été spécifié pour nommer les objets de la base, présent dans la requête.


2.8.2 - Rajout des préfixes de schéma

Après rectification de cette erreur (ajout de dbo en préfixe des tables), et exécution de la requête deux fois de suite, à la deuxième exécution, nous obtenons les temps donnés suivantes :

SQL Server parse and compile time : CPU time = 0 ms, elapsed time = 1 ms.

[...]

Le temps de compilation est passé à zéro (en fait il n'est pas mesurable) du fait de la reprise du plan dans le cache.

Pourquoi cette différence ?

Tout simplement, en l'absence des préfixes de schéma, SQL Server ne sait pas à quel objet il a affaire. En effet une base de données peut être constituée d'autant de schéma que l'on souhaite (les schémas constituant des unités logiques d'appartenance des objets, un peu à la manière des espaces de noms ou des bibliothèques de code), et il y a toujours un schéma par défaut associé à un utilisateur SQL, comme il y a un schéma par défaut propre à la base. Même si ces deux schémas sont identiques et même s'il n'existe qu'un seul schéma dans la base, par exemple le fameux schéma dbo, cette absence de spécification oblige à ce que le moteur SQL effectue une résolution de nom avant compilation et ne puisse donc associer aucun plan de requête déjà établi à une requête qui revient ainsi "déschématisée"...

Nous en concluons qu'il est urgent de préfixer tous les objets par leur schéma dans les requêtes, l'absence d'un seul schéma à un objet dans une requête empêchant de reprendre un plan de requête déjà mis en cache par une requête similaire.

A noter que ce phénomène est généralement amplifié par l'utilisation de la sécurité basé sur les comptes Windows, qui, par le nombre des utilisateurs SQL qu'il engendre dans la base, multiplie la problématique de résolution des noms.
Ainsi je préconise de manière systématique, que ne soit créé dans la base de données que quelques utilisateurs purement SQL car après tout ce ne sont pas les utilisateurs de l'informatique (personnes physiques) que se connectent au serveurs, mais des applications !

Le gain global est donc une réduction de 85% du temps de réponse

Attention cependant, du fait de la gestion du cache, ce gain n'est pas susceptible de se produire systématiquement. En effet l'algorithme LRU fait que les plans de requêtes les plus souvent utilisés sont conservés en cache le plus longtemps au détriment de ceux qui le sont le moins.

***********

Je ne dirais pas le nom de la grande boîte que j'ai audité...

A +