Optimiser les performances de SQL 2005 - Comment faire ?
Le
Glenn Gagné
Bonjour,
Je cherche à fignoler les performances (temps d'accès. gestion des grosses
requêtes et temps de réponse) de mon serveur SQL 2005 au maximum à partir
des options configurables dans celui-ci. J'ai donc besoin d'un coup de pouce
de ceux qui savent à quoi servent ces options.
--
Avant tout, le contexte d'utilisation de ce serveur:
- Serveur Windows 2003 avec 1 Go RAM et Pentium D 3.4 Ghz et disque dur en
RAID-1
- SQL Server 2005 Entreprise
- Site Web ASP qui utilise cette base de données pour notre ERP
- Quelques connexions via ODBC localement pour des feuilles Excel qui
récupère uniquement des données pour fin de rapport
- 2 sites distants (via un VPN) avec MSDE2000A et une applications en VB qui
synchronise une seule table à toutes les 5 minutes afin de récupérer les
quantités de production via des terminaux RS-422
*** Je sais que je pourrais ajouter un autre Go de RAM, mais pour le moment
le budget ne me permet pas de le faire, et il se pourrait que cet ajout soit
inutile si l'optimisation mémoire n'est pas bon à la base
Voici les options que j'ai trouvé qui peuvent influencer les performances,
j'aimerais savoir ce qu'on peut en faire et si ça fait vraiment une
différence:
Propriétés du serveur -> Mémoire -> Utiliser AWE pour allouer la mémoire
* Pour celui-ci je sais que AWE peut permettre la configuration d'une plage
statique de mémoire alloué.
Propriétés du serveur -> Processeurs -> Renforcer la priorité SQL Server
* Je trouve pas d'info la dessus
Propriétés du serveur -> Processeurs -> Utiliser les fibres Windows
(lightweight pooling)
* Je trouve pas d'info la dessus, sauf des bugs avec DTC et SQL 2000
Propriétés du serveur -> Sécurité -> Chaînage des propriétés des bases de
données croisées
* N'offre pas de performance (accélération) mais plus sécuritaire, sauf
qu'il peut occasionner des problèmes de sécurité si mal géré avec SQL 2000
SP3, pour SQL 2005 je sais pas
Propriétés du serveur -> Connexion -> Nombre maximal de connexion
simultanées
* Défaut à 0 (illimité), le fait de spécifier une valeur raisonnable peut-il
permettre une meilleure gestion de sa mémoire en temps de traitement en
plaçant en attente les connexions au delà de la valeur permise ?
Propriétés du serveur -> Connexion -> Utiliser l'Administrateur de requêtes
pour empêcher les requêtes longues
* Est-ce que cet administrateur permet une meilleure gestion ?
Propriétés du serveur -> Paramètre de base deonnées -> Facteur de
remplissage par défaut de l'index
* Défaut: 0. Allouer un taux de remplissage de l'index peut-il accélérer les
requêtes ?
Propriétés du serveur -> Avancé ->Seuil du curseur
Propriétés du serveur -> Avancé -> Taile de réplication de texte maximum
Propriétés du serveur -> Avancé -> Attente de la requête
Propriétés du serveur -> Avancé -> Degré de parallélisme
Propriétés du serveur -> Avancé -> Seuil de coût pour le parallélisme
Propriétés du serveur -> Avancé -> Verrous
Propriétés du serveur -> Avancé -> Délai d'attente de la connexion distante
Propriétés du serveur -> Avancé -> Taille du paquet réseau
Propriétés de la base de données -> Fichiers -> Utiliser l'indexation de
texte intégral
Propriétés de la base de données -> Options -> tous
Voilà il y a plusieurs options modifiables qui tournent autour de la
performance de SQL, sans compter les modifications possible probablement
depuis le registre de Windows.
Est-ce que l'ouveture d'une connexion SQL d'une manière peut être plus
efficace qu'une autre ?
--
Merci
Glenn
Je cherche à fignoler les performances (temps d'accès. gestion des grosses
requêtes et temps de réponse) de mon serveur SQL 2005 au maximum à partir
des options configurables dans celui-ci. J'ai donc besoin d'un coup de pouce
de ceux qui savent à quoi servent ces options.
--
Avant tout, le contexte d'utilisation de ce serveur:
- Serveur Windows 2003 avec 1 Go RAM et Pentium D 3.4 Ghz et disque dur en
RAID-1
- SQL Server 2005 Entreprise
- Site Web ASP qui utilise cette base de données pour notre ERP
- Quelques connexions via ODBC localement pour des feuilles Excel qui
récupère uniquement des données pour fin de rapport
- 2 sites distants (via un VPN) avec MSDE2000A et une applications en VB qui
synchronise une seule table à toutes les 5 minutes afin de récupérer les
quantités de production via des terminaux RS-422
*** Je sais que je pourrais ajouter un autre Go de RAM, mais pour le moment
le budget ne me permet pas de le faire, et il se pourrait que cet ajout soit
inutile si l'optimisation mémoire n'est pas bon à la base
Voici les options que j'ai trouvé qui peuvent influencer les performances,
j'aimerais savoir ce qu'on peut en faire et si ça fait vraiment une
différence:
Propriétés du serveur -> Mémoire -> Utiliser AWE pour allouer la mémoire
* Pour celui-ci je sais que AWE peut permettre la configuration d'une plage
statique de mémoire alloué.
Propriétés du serveur -> Processeurs -> Renforcer la priorité SQL Server
* Je trouve pas d'info la dessus
Propriétés du serveur -> Processeurs -> Utiliser les fibres Windows
(lightweight pooling)
* Je trouve pas d'info la dessus, sauf des bugs avec DTC et SQL 2000
Propriétés du serveur -> Sécurité -> Chaînage des propriétés des bases de
données croisées
* N'offre pas de performance (accélération) mais plus sécuritaire, sauf
qu'il peut occasionner des problèmes de sécurité si mal géré avec SQL 2000
SP3, pour SQL 2005 je sais pas
Propriétés du serveur -> Connexion -> Nombre maximal de connexion
simultanées
* Défaut à 0 (illimité), le fait de spécifier une valeur raisonnable peut-il
permettre une meilleure gestion de sa mémoire en temps de traitement en
plaçant en attente les connexions au delà de la valeur permise ?
Propriétés du serveur -> Connexion -> Utiliser l'Administrateur de requêtes
pour empêcher les requêtes longues
* Est-ce que cet administrateur permet une meilleure gestion ?
Propriétés du serveur -> Paramètre de base deonnées -> Facteur de
remplissage par défaut de l'index
* Défaut: 0. Allouer un taux de remplissage de l'index peut-il accélérer les
requêtes ?
Propriétés du serveur -> Avancé ->Seuil du curseur
Propriétés du serveur -> Avancé -> Taile de réplication de texte maximum
Propriétés du serveur -> Avancé -> Attente de la requête
Propriétés du serveur -> Avancé -> Degré de parallélisme
Propriétés du serveur -> Avancé -> Seuil de coût pour le parallélisme
Propriétés du serveur -> Avancé -> Verrous
Propriétés du serveur -> Avancé -> Délai d'attente de la connexion distante
Propriétés du serveur -> Avancé -> Taille du paquet réseau
Propriétés de la base de données -> Fichiers -> Utiliser l'indexation de
texte intégral
Propriétés de la base de données -> Options -> tous
Voilà il y a plusieurs options modifiables qui tournent autour de la
performance de SQL, sans compter les modifications possible probablement
depuis le registre de Windows.
Est-ce que l'ouveture d'une connexion SQL d'une manière peut être plus
efficace qu'une autre ?
--
Merci
Glenn
Poser une question
1 Go c'est faible, mais tout dépend de la taille de la BD (si celle-ci
ne dépasse pas quelques 200 ou 300 Mo, pas de problème.
Si votre serveur Web est sur la même machine que votre serveur SQL,
c'est extrémement mauvais. Ces deux services sont en contrariété totale
quand à l'utilisation du serveur et comme SQL Server est toujours
prioritaire pour l'obtention des ressources, vous risquez d'avoir un
serveur web qui ne répond plus.
Donc utilisez un serveur dédié pour le serveur SQL, ou alors bloquez
l'utilisation des ressources, mais dans ce cas les performances seront
singulièrement dégradées.
Cela dépend surtout de votre modèle de données, de la fenêtre
d'exploitation des données et du volume des lignes à manipuler.
mais si ces indicateurs nécessite plus de RAM il faudra s'y contraindre !
N'a d'intérêt qu'au dela de 4 Go de RAM. Strictement inutile dans votre cas.
oui, lancez la commande :
EXEC sp_configure 'priority boost', 1
Aucun intérêt si vous n'êtes pas au moins sur un quadriprocesseur (réels
pas virtuels) et si vous n'avez pas quasi exclusiement de nombreuses
petites requêtes.
Strictement aucun intérêt en terme de perf.
Non, pas dans une config standard.
Cela interdit des requêtes dont le coût peut être exhorbitant. Mais si
vous maîtrisez votre code, il n'y a pas de raison.
Oui et non. Si vous ne faites pas de maintenance d'index régulière, vous
pouvez construire des index avec un fill factor plus grand, afin
d'éviter la framentation des index. Inconvénient, les index seront peu
fragmentés mais plus volumineux, donc plus long à lire....
Je n'ai bien évidemment pas le temps de vous faire un cours...
Mais vous pouvez venir au cours d'optimisation que j'ai mis au point
pour Orsys.... On y répond justement à toutes ces choses et bien d'autres.
Vous pouvez aussi lire la série d'article dont j'ai entamé la rédaction
et qui parait en ce moment dans SQL Server magazine.
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Oui, j'aimerais bien avoir plus d'information... mais faudrait premièrement
que mon patron voudrait payer les cours... et que ce soit disponible à la
ville de Québec au plus loin :o(
Ce que je trouve le plus malheureux c'est le nombre minime d'information à
ce sujetr sur l'Internet. Pourtant combien d'entreprise de moyenne envergure
sont dans cette situation.
NOTE: Ce que je ne comprends pas c'est qu'auparavant nous avions un serveur
Pentium III avec 512 Mo RAM, SQL 2000 Server et IIS 5.0 avec la même base de
données (qui n'a guère changée depuis) et nous n'avions j'amais eu de
problèmes du genre. Actuellement, il arrive fréquemment que lors de la
demande de requête assez volumineuses (qui sont les mêmes qu'avant) il y a
perte de connexion entre la page ASP et le requête SQL. La base de données à
environ 1.2 Go au total.
----------------
Merci encore
Glenn
"Fred BROUARD" news:
grosses
partir
pouce
en
qui
moment
soit
performances,
cas.
plage
de
2000
peut-il
requêtes
les
distante
ces paramètres. Avec d'autres recherches j'ai découvert qu'il était possible
d'ouvrir une connexion SQL différement avec SQL 2005.
Mes pages en ASP, lorsqu'elles appellaient SQL, utilisait la requête
d'ouverture suivante:
conn.OPEN = "PROVIDER=SQLOLEDB;DATA
SOURCE=localhost;UID=username;PWD=password;DATABASE=mabdd
Et je l'ai remplacé par:
conn.OPEN = "PROVIDER=SQLNCLI;DATA
SOURCE=localhost;UID=username;PWD=password;DATABASE=mabdd
-------------------------------------------
Vu que le site était auparavant connecté à une base de données SQL 2000, il
n'y avait pas de problème avec PROVIDER=SQLOLEDB. Le serveur SQL 2005 est
supposé supporter les même options que SQL 2000 mais il semble qu'en
utilisant le client Natif SQL 2005 (PROVIDER=SQLNCLI) à la place il devient
beaucoup plus performant et stable.
Mes requêtes tout le temps depuis la modification !!!
--------------------------------------------
Glenn
"Glenn Gagné" news:%
premièrement
envergure
serveur
de
à
dur
VB
les
ajout
!
mémoire
Server
sauf
en
accélérer
maximum
de
probablement
d'autres.
N'y a t-il pas un programe de formation prpfessionnel au quebec pour les
entreprises ?
L'optimisation est un artisanat, pas un truc automatisable... Si c'était
le cas, il y a belle lurette que MS l'aurait ajouté à, son offre SQL
Server !
1) le programme de MS SQL Server 2005 est plus gros et prends plus de
place en mémoire
2) le moteur de requetes de SQL Server 2005 a été récrit et les requêtes
optimisées pour la v 2000 ne le sont pas forécement pour la 2005.
Actuellement, il arrive fréquemment que lors de la
C'est donc un problème de time out. Augmentez le, mais tentez de réduire
les temps de réponse ensuite !
> La base de données à
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
tiens tiens, intéressant !!!
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************