Discussion :

[sevyc64]

Bonjour à tous,

Sur mon poste de développement, je suis passé de SQLServer 2008 à SQLServer 2017 et je rencontre depuis quelques problèmes de performance générale de SQLServer. Il est lent, les requêtes mettent 2 à 5 fois plus temps à s’exécuter.
Des scripts qui fonctionnaient très bien sous 2008, sortent souvent en timeout, maintenant.

Quels sont les paramétrages à regarder et ajuster pour revenir à des performances acceptables ?


SQLServer Standard 64 bits 14.0.2027.2
Instance nommée
Windows 10 PRO 1909 64 bits
I7-6700 3.4GHz
16Go de ram

[SQLpro]

1) recalculer toutes les stats en mode FULLSCAN
2) redimensionner les epsaces de stockage pour éviter les opérations de croissance des fichiers
3) revoir le plan d'indexation

A +

[sevyc64]

Je précise, ce n'est pas une base en particulier qui est lente mais le serveur dans son ensemble.

Sur ce serveur, je peux monter, démonter, restaurer, créer dynamiquement de toute pièce, jusqu'à une 10ène de bases par jour, des bases qui fonctionnent correctement sur d'autres postes.
Même les restaurations sont bien plus lentes.
L'espace de stockage est strictement le même que pour le 2008, même dossier sur le même disque.

Autre phénomène observé, une requête exécutée en un temps limite raisonnable (pour un poste de dev) une fois, pour se révéler très lente quelques minutes plus tard, comme si à l’instar de Windows, le serveur s'était mis en veille entre-temps et donc avait besoin de plus de temps pour exécuter la requête, le temps de sortir de veille.
A ma connaissance il n'y a pas de système de veille automatique sur SQLServeur, si ?

SQLpro> pour les points que tu cite, tu peux donner plus de précision, stp. Bien que j'ai quelques connaissances, je suis pas admin sql. Je suis pas sur de savoir où chercher.

[SQLpro]

Autres hypothèses :

1) les bases ne serait_elle pas en mode autoclose ?

Pour voir et rectifier, lancez la requête suivante :

Code :

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

1
2
3
SELECT name, is_auto_close_on, 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE OFF;' AS SQL_COMMAND
FROM   sys.databases 
WHERE  is_auto_close_on = 0;

Copiez coller le code de la colonne SQL_COMMAND dans une nouvelle fenêtre et exécutez-le.

2) quelle est la RAM affectées aux instances ?

Avec 16Go de ram vous devriez mettre 6 Go par instance

3) le PC serait-il en mode d'économie d'énergie ?

Si oui désactivez cette fonctionnalité

Pour recalculer toutes les statistiques en mode FULLSCAN de toutes les bases (sauf systèmes et inaptes) , lancer :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @QRY NVARCHAR(max) = 
'SELECT N''UPDATE STATISTICS [???].['' + s.name + N''].['' + o.name + N''] (['' + st.name + N'']) WITH FULLSCAN;'' COLLATE French_CI_AI
FROM   [???].sys.stats AS st
       INNER JOIN [???].sys.objects AS o ON st.object_id = o.object_id
       INNER JOIN [???].sys.schemas AS s ON o.schema_id = s.schema_id';
DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = CASE WHEN @SQL = N''  THEN N'' ELSE @SQL + ' UNION ALL ' END  + REPLACE(@QRY, '???', name)
FROM   sys.databases AS db
WHERE  database_id > 4
  AND  db.is_read_only = 0
  AND  db.state = 0;
EXEC (@SQL);

Copiez, coller le résultat dans une nouvelle fenêtre et exécutez-là.

A +

[sevyc64]

Bon, retour après quelques jours d'utilisation. Les requêtes n'ont rien changé . J'ai tenter une réparation de l'instance, ainsi que l'install de toutes les mises à jours possibles, le dernier patch cumulatif notamment, cela n'a rien changé non plus.

Là, je viens de passer 25min à attendre la suppression de 13500 lignes depuis le mode "Modifier toutes les lignes" de SSMS. Ca ne devrait prendre que quelques secondes habituellement.

Par contre, 2 min après, l'insertion, dans la même table, de 13900 ligne a, elle, pris moins d'une 1s.

Ce matin, je travaille en parallèle sur un autre serveur, 2017 aussi, au bureau, donc connexion distante, plusieurs 100ène de km, à travers un vpn, malgré les lenteurs inhérentes à ce mode de connexion, c'est nettement plus rapide qu'en local sur mon poste

[Bernardos]

Bonjour,
Moi j'ai eu des soucis lors d'une migration de SQl server 2008 R2 à 2016.
Certaines requêtes qui était express se sont mis à prendre des 20 minutes.
C'est un peu loin dans ma mémoire mais de ce que je me souviennes, ca concernait des procedures stockées avec paramètres et pour la plus part des CTE. c'était dans mon cas une histoire de "parameter sniffing".
Je ne dis pas que c'est ton problème mais en tout cas moi je l'ai eu. je l'avais résolu en utilisant l'option Recompile dans les procédures concernées.
En sql server 2017 il y a l'option de bases de données Parameter Sniffing mais je pense que c'est le genre de paramètres à utiliser avec beaucoup de précautions (tout comme le recompile).
Cordialement,

[StringBuilder]

Juste comme ça en passant, quelle est la taille initiale :
- des fichiers tempdb ?
- des fichiers de données et journaux de transaction des bases en question ?

Et quel est le paramètre d'accroissement ? Quel pourcentage ? Quel taille ?

Car si un delete prend 3 plombes et un insert du même volume par la suite est instantané, ça sent le redimensionnement de fichier...

[SQLpro]

Bon, retour après quelques jours d'utilisation. Les requêtes n'ont rien changé . J'ai tenter une réparation de l'instance, ainsi que l'install de toutes les mises à jours possibles, le dernier patch cumulatif notamment, cela n'a rien changé non plus.

Là, je viens de passer 25min à attendre la suppression de 13500 lignes depuis le mode "Modifier toutes les lignes" de SSMS. Ca ne devrait prendre que quelques secondes habituellement.

Par contre, 2 min après, l'insertion, dans la même table, de 13900 ligne a, elle, pris moins d'une 1s.

Ceci est un syndrome très classique lié au capacity planning. Si vous avez taillé des espace de stockage important, l'effort à faire pour l'insertion physique est négligeable. En ayant supprimé, vous avez dégagé de l'espace pour les nouvelles insertions..... Donc, voir a bien dimensionner vos espaces de stockage et vérifier la latence de votre disque...

Ce matin, je travaille en parallèle sur un autre serveur, 2017 aussi, au bureau, donc connexion distante, plusieurs 100ène de km, à travers un vpn, malgré les lenteurs inhérentes à ce mode de connexion, c'est nettement plus rapide qu'en local sur mon poste

Donc, réellement un problème de PC ou de config....

Vous n'avez pas répondu à mon précédent post.....

A +

[sevyc64]

ca concernait des procedures stockées avec paramètres et pour la plus part des CTE. c'était dans mon cas une histoire de "parameter sniffing".
Je ne dis pas que c'est ton problème mais en tout cas moi je l'ai eu. je l'avais résolu en utilisant l'option Recompile dans les procédures concernées.

Petit test rapide (si on peut dire) sur une base cet apm, ça semble être mieux effectivement, bien que la base effectivement testée cet apm ne contient pas de ps.
Mais il faudrait que le paramètre tienne.
Effectivement, comme mes bases sont, pour la plupart, créées dynamiquement il faudrait pouvoir configurer la valeur par défaut de ce paramètre pour les nouvelles bases au niveau du serveur, pas de chacune des bases.
Mes bases ne sont pas des bases de production (en tout cas sur mon poste), ceux sont des bases temporaires de travail pour certaines, créées dynamiquement par les process (que je développe et maintien), ou des bases clients restaurées pour rechercher les causes d'un bug identifié. Elles ne restent généralement pas bien longtemps.

Juste comme ça en passant, quelle est la taille initiale :
- des fichiers tempdb ?
- des fichiers de données et journaux de transaction des bases en question ?

Et quel est le paramètre d'accroissement ? Quel pourcentage ? Quel taille ?

Car si un delete prend 3 plombes et un insert du même volume par la suite est instantané, ça sent le redimensionnement de fichier...

Il va falloir que je fouille pour trouver les valeurs, mais ceux sont les valeurs par défaut de l'installation. Ces paramètres-là n'ont pas été modifiés.
Juste un détail, normalement les bases sont configurées en mode de récupération simple, donc un journal de faible taille. Mais cela ne posait pas de problème avec la version 2008R2.
Normalement, car j'ai pas su retrouver où la valeur par défaut se configurait sur le serveur pour vérifier.

Ceci est un syndrome très classique lié au capacity planning. Si vous avez taillé des espace de stockage important, l'effort à faire pour l'insertion physique est négligeable. En ayant supprimé, vous avez dégagé de l'espace pour les nouvelles insertions..... Donc, voir a bien dimensionner vos espaces de stockage et vérifier la latence de votre disque...

Donc, réellement un problème de PC ou de config....

Possible. Le disque et même le dossier de stockage sont exactement les mêmes qu'avec la version 2008, mais je constate effectivement dans le moniteur de ressource de Windows, que le disque sature bien plus fort et plus longtemps qu'il ne faisait, me semble-t-il avec la version 2008.
Le PC est le même, la configuration est la même, à l'exception des mises à jours Windows qui se sont installées depuis. Il y a juste VS2019 qui a été installé en plus à quelques jours d'intervalle (il y avait et a toujours VS2012 sur le poste)

Vous n'avez pas répondu à mon précédent post.....

Pour ce qui est des requêtes, elles n'avaient pas donner d'amélioration.
Pour la mémoire, c'est la config par défaut de l'installation, soit min 0Mo, max 2To. Pour le processeur, idem, config par défaut, pas de restriction de cœur, pas d’affinités non plus. J'ai juste coché une option (depuis SSMS) pour augmenter la priorité mais ça n'a pas donné de résultat particulier.

Pour ce qui est de l'économie d'énergie, à part un diaporama en guise d'écran de veille, et un autolock de la session imposé par le domaine, tout le reste est désactivé, pas de veille, pas d’arrêt des disques ...
De toute façon, le problème se produit quand je travaille réellement sur la machine.

[StringBuilder]

Pour tempdb, regardez à la fin de la journée quelle taille fait la base réellement.
Modifiez alors la taille initiale dans SSMS avec cette taille + par exemple 20% ou 50% selon si c'est plutôt quelques centaines de Mo ou de Go.
Pour le journal des transactions de tempdb, idem, fixez-le avec sa taille de fin de journée + un pourcentage afin d'être "large" en cas de plus gros traitement.
Quant au redimensionnement, perso je met toujours un pas d'au moins 1 Go : au pire, il se produit une fois de temps en temps, par 50 fois de suite durant la même transaction.

Pour les fichiers de data des base, idem, regardez la taille en fin de journée, et vérifiez qu'au moins 30% est libre (sauf si elles sont énormes évidement), si ce n'est pas le cas, modifiez leur taille de façon à ce qu'il y ait clairement de la place.
Et pour les logs de chaque base, idem.

Dans tous les cas, tout accroissement doit se faire avec un pas très important (perso je mets généralement 1 Go pour les data et 256 ou 512 Mo pour les logs).

Car clairement, les symptômes que vous décrivez ressemblent clairement à ce que j'ai pu expérimenter avec SQL Server 2000 qui collait un accroissement automatique de 1 Mo pour les logs et de 1% pour les data (avec une taille initiale de 5 Mo...) : la moindre insertion en masse pouvait mettre des heures. Après redimensionnement propre, la même transaction ne durait plus que quelques secondes.

[SQLpro]

....
Pour ce qui est des requêtes, elles n'avaient pas donner d'amélioration.
Pour la mémoire, c'est la config par défaut de l'installation, soit min 0Mo, max 2To. Pour le processeur, idem, config par défaut, pas de restriction de cœur, pas d’affinités non plus. J'ai juste coché une option (depuis SSMS) pour augmenter la priorité mais ça n'a pas donné de résultat particulier.

Pour ce qui est de l'économie d'énergie, à part un diaporama en guise d'écran de veille, et un autolock de la session imposé par le domaine, tout le reste est désactivé, pas de veille, pas d’arrêt des disques ...
De toute façon, le problème se produit quand je travaille réellement sur la machine.

Avez-vous plusieurs instances ? Si oui, il faut IMPÉRATIVEMENT baisser la RAM de chacune des instances pour éviter un effet de résonance lors de la tentative de prise de RAM. J'ai suggéré 6 Go...

Avez-vous recalculé les stats en mode FULLSCAN ?

Ne pas booster la priorité. Cela conduit à des pertes de perf....



Vous pouvez tenter de modifier le comportement de récupération du journal qui a été changé.... Par défaut depuis SQL Server 2016 le paramètre TARGET_RECOVERY_TIME est à 60 secondes (précédement il était à 0).

Code :

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

ALTER DATABASE mabase SET TARGET_RECOVERY_TIME = 0 SECONDS WITH NO_WAIT;

Dans le pire des cas, voyez si une journalisation asynchrone règle le problème :

Code :

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

ALTER DATABASE mabase SET DELAYED_DURABILITY = FORCED;

Vérifiez aussi la latence de vos disques :

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
WITH 
disk_activity AS
(SELECT LEFT(mf.physical_name, 2) AS Drive, 
        SUM(num_of_reads) AS num_of_reads,
	     SUM(io_stall_read_ms) AS io_stall_read_ms, 
        SUM(num_of_writes) AS num_of_writes,
	     SUM(io_stall_write_ms) AS io_stall_write_ms, 
        SUM(num_of_bytes_read) AS num_of_bytes_read,
	     SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
 FROM   sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
        INNER JOIN sys.master_files AS mf WITH (NOLOCK)
              ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
 GROUP  BY LEFT(mf.physical_name, 2))
SELECT (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS SINCE,
       Drive AS DISK_DRIVE,
       CASE 
		    WHEN num_of_reads = 0 THEN 0 
		    ELSE (io_stall_read_ms/num_of_reads) 
	    END AS READ_LATENCY,
	    CASE 
		    WHEN io_stall_write_ms = 0 THEN 0 
          ELSE (io_stall_write_ms/num_of_writes) 
	    END AS WRITE_LATENCY,
       CASE 
          WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
          ELSE (io_stall/(num_of_reads + num_of_writes)) 
       END AS GLOBAL_LATENCY,
       CASE 
          WHEN num_of_reads = 0 THEN 0 
          ELSE (num_of_bytes_read/num_of_reads) 
       END AS AVG_BYTES_PER_READ,
       CASE 
          WHEN io_stall_write_ms = 0 THEN 0 
          ELSE (num_of_bytes_written/num_of_writes) 
       END AS AVG_BYTES_PER_WRITE,
       CASE 
          WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
          ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes)) 
       END AS AVG_BYTES_PER_TRANSFER
FROM   disk_activity AS tab
ORDER  BY GLOBAL_LATENCY 
OPTION (RECOMPILE);

Votre latence ne doit pas être au dessus de 7 ms pour les lectures et 14 pour les écritures...

A +