Chute des performances sur nouveau serveur

**monmien** · 29/01/2016, 16h15

Bonjour,

On a changé le serveur supportant le serveur SQL 2005. On est passé d'un serveur de 4/5 avec 8 go de ram et 1 raid 1 pour système de db a un serveur récent avec 32 go de ram et 1 raid 1 pour le système et 1 raid 1 (ssd) pour les bases (mdf et ldf).

Sql server est le même, SQL 2005 SP2 (32 bits) pour l'ancien et SQL 2005 SP4 (64bits) pour le nouveau.

J'ai transféré mes bases avec l'option "copier une base de données" de SSMS, jusque la pas de soucis.

Sauf que j'ai une baisse dramatique de perf sur le nouveau, une requête qui met 4 secondes sur l'ancien peut mettre jusqu’à 5 min sur le nouveau... Les index sont les mêmes, je les ai réorganisé, reconstruit pas mieux.

J'ai comparé les paramétrages, je ne vois pas de différence.

Je ne suis pas administrateur de DB de formation et la ça dépasse la recherche google "mon serveur est lent".

Si vous avez des pistes je suis preneur.

Autre point, qui j’espère ne rendre pas en compte, l'OS est un serveur 2012 R2, SQL 2005 n'est normalement pas supporté par cet OS, est-ce que ca peut jouer ?

Merci d'avance !

**Boubou2020** · 29/01/2016, 17h03

avez vous pensé à recalculer de nouveau vos statistiques surtout les statistiques hors index

**monmien** · 29/01/2016, 18h58

Euh non, mais c'est chose faite avec EXEC sp_updatestats;
et pas de d'amélioration.

**SQLpro** · 29/01/2016, 19h43

Quel est la configuration du parallélisme ?
Combien avez vous de socket/core ?
Pouvez vous mettre le parallélisme à 1 ? (MAXDOP)

Avez vous limité la RAM utilisé par SQL Server ?

Bref commencez par nous dire ce qu'il y a dans sp_configure....

A +

**monmien** · 29/01/2016, 19h58

pour le parallélisme c'est mis a 0 (identique précédent serveur)
il y a 6 core (4 core sur l'ancien)
pas de configuration sur la limite en ram.

dans sp_configure j'ai :

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
 
allow updates	0	1	0	0
clr enabled	0	1	0	0
cross db ownership chaining	0	1	0	0
default language	0	9999	2	2
max text repl size (B)	0	2147483647	65536	65536
nested triggers	0	1	1	1
remote access	0	1	1	1
remote admin connections	0	1	0	0
remote login timeout (s)	0	2147483647	20	20
remote proc trans	0	1	0	0
remote query timeout (s)	0	2147483647	600	600
server trigger recursion	0	1	1	1
show advanced options	0	1	0	0
user options	0	32767	0	0

**SQLpro** · 01/02/2016, 09h07

Faites les modifications suivantes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
 
EXEC sp_configure 'max server memory', 28672;
GO
EXEC sp_configure 'max degree of parallelism', 2
GO
EXEC sp_configure 'cost threshold for parallelism', 25;
GO
EXEC sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE

Il faudrait aussi dimensionner correctement la tempdb :

4 fichiers de données de 1 Go
1 fichier du JT de 500 Mo

Après ceci, si pas de pefs acceptable il est possible que votre problème se situe dans la config vCPU à cause du NUMA....

A +

**monmien** · 01/02/2016, 10h02

Merci pour votre aide.

J'ai appliqué vos recommandations (sauf le "optimize for ad hoc workloads" qui n'existe qu'a partir de 2008)

Pas de changement de perfs.

Concernant NUMA dans le bios UEFI j'ai 2 choix de réglages, Clustered (defaut) et Flat. La doc indique que Flat est une option pour les applications ne supportant pas les groupes de processeurs logiques. SQL 2005 pourrait être dans cette catégorie ?

**SQLpro** · 01/02/2016, 11h04

Envoyé par monmien

Merci pour votre aide.

J'ai appliqué vos recommandations (sauf le "optimize for ad hoc workloads" qui n'existe qu'a partir de 2008)

Pas de changement de perfs.

Concernant NUMA dans le bios UEFI j'ai 2 choix de réglages, Clustered (defaut) et Flat. La doc indique que Flat est une option pour les applications ne supportant pas les groupes de processeurs logiques. SQL 2005 pourrait être dans cette catégorie ?

D'abord pourquoi ne pas avoir mis la version 2012 de SQL Server ? En laissant votre base en version 2005.

Si vous avez du NUMA, il faut que les cores soient équilibrés avec les noeuds NUMA....
Si mes souvenir sont exacts, SQL Server 2005 ne les gèrent pas. Il faut attendre 2008 !

Tentez donc le mode flat...

A +

**monmien** · 01/02/2016, 12h26

Envoyé par SQLpro

D'abord pourquoi ne pas avoir mis la version 2012 de SQL Server ? En laissant votre base en version 2005.

Le nerf de la guerre,le cout de la licence... Même si l’expérience montre toujours que le temps passé en maintenance ne vaut pas l’économie, il faut souvent démontrer que ça ne peut pas fonctionner pour justifier l'achat.

Je vais tenter le passage en Flat, on verra ce que ça donne.

Edit: j'ai profité du midi pour passer en Flat, aucun changement.

**SQLpro** · 01/02/2016, 14h39

Il faut voir ou il perd du temps maintenant.....

Faites une RAZ des stats de temps avec :

Code :

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

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

Faites ensuite la maximum de tests puis lancez la requête suivante :

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
WITH T_WAITS
AS (
SELECT wait_type,
       wait_time_ms / 1000.0 AS wait_time_seconds,
       (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_seconds,
       signal_wait_time_ms / 1000.0 AS signal_seconds,
       waiting_tasks_count AS wait_count,
       100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
       ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS row_num
FROM   sys.dm_os_wait_stats
WHERE  wait_type NOT IN (
        N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
        N'CHKPT',                           N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                        N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
        N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')
  AND  waiting_tasks_count > 0
)
SELECT MAX (W1.wait_type) AS WaitType,
       CAST (MAX (W1.wait_time_seconds) AS DECIMAL (16,2)) AS wait_time_seconds,
       CAST (MAX (W1.resource_seconds)  AS DECIMAL (16,2)) AS resource_seconds,
       CAST (MAX (W1.signal_seconds)    AS DECIMAL (16,2)) AS signal_seconds,
       MAX (W1.wait_count) AS wait_count,
       CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS Percentage,
       CAST ((MAX (W1.wait_time_seconds) / MAX (W1.wait_count)) AS DECIMAL (16,4)) AS average_wait_time_seconds,
       CAST ((MAX (W1.resource_seconds)  / MAX (W1.wait_count)) AS DECIMAL (16,4)) AS average_resource_seconds,
       CAST ((MAX (W1.signal_seconds)    / MAX (W1.wait_count)) AS DECIMAL (16,4)) AS average_signal_seconds
FROM   T_WAITS AS W1
       INNER JOIN T_WAITS AS W2
             ON W2.row_num <= W1.row_num
GROUP  BY W1.row_num
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 95;

Donnez nous le résultat et nous vous donnerons une analyse de ce qui perds du temps.

A +

**monmien** · 01/02/2016, 16h10

Voici ce que ca donne :

Code :

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

1
2
3
4
5
 
WaitType	wait_time_seconds	resource_seconds	signal_seconds	wait_count	Percentage	average_wait_time_seconds	average_resource_seconds	average_signal_seconds
CXPACKET	2442.28	1847.56	594.72	781677	86.92	0.0031	0.0024	0.0008
ASYNC_NETWORK_IO	196.38	194.08	2.30	489060	6.99	0.0004	0.0004	0.0000
PAGEIOLATCH_SH	144.47	143.36	1.11	888112	5.14	0.0002	0.0002	0.0000

**SQLpro** · 01/02/2016, 17h03

87 % des temps d'attente sont des CXPACKET liés au parallélisme.

Passez "max degree of parallelism" à 1,
faite un RAZ des stats et recommencez les tests et métriques...

A +

**Mohamed.Houri** · 01/02/2016, 20h34

Envoyé par SQLpro

87 % des temps d'attente sont des CXPACKET liés au parallélisme.

Passez "max degree of parallelism" à 1,
faite un RAZ des stats et recommencez les tests et métriques...

A +

Je ne suis pas du tout un spécialiste SQL Server mais j'aurai voulu savoir pourquoi en forçant la sérialisation à un niveau global il va gagner en performance. Il se peut qu'un processus "slave" reçoive plus de charge que l'autre et voilà que celui-ci se mette à attendre sur CXPACKET (je me suis placé ici dans un DOP de 2). Votre suggestion d'augmenter le paramètre cost threshold for parallelism' me parait plus pertinente. La dernière fois, j'ai suivi, de loin, une pareille situation dont la cause était une key Lookup exécutée des centaines de milliers de fois qui, lorsque suppliée par index adéquat et couvrant, a fait chuter drastiquement le wait event CXPACKET et augmenter la performance de la requête

Bien Cordialement

**julien94320** · 02/02/2016, 10h33

Pour le coup je pense que SQL PRO essai de "Bencher" l'origine du probleme et a raison

Effectivement il doit y avoir une/des requete(s) "consommatrice(s)" et "problématique(s)" qui est mal geré.
Augmenter le paramètre cost threshold for parallelism est une bonne idée mais il faut s'assurer avant que le probleme vient bien de la et peut etre identifier la source réél du probleme...

Essayer de faire un rebuild des index / Mettre a jour l'ensemble des stats en mode full (comme déjà évoqué) mais je ne suis pas sur que cela change fondamentalement le soucis (peut etre que les requetes problematiques retrouveront le bon chemin ... ) .

Pour mon information quels sont les vitesses d'horloge des anciens processeur ? et des nouveaux processeurs ?

Utilisez vous des fonctions de formatage des données ?

C'est une piste a conserver, j'ai dejà eu le cas ou lors d'une migration ISO SQL Server sur une machine plus performante nous avons eu des baisse de performance, cela était du a du formatage de données qui solicitait énormément la vitesse d'horloge du CPU ...

Julien

**monmien** · 02/02/2016, 10h44

Envoyé par SQLpro

87 % des temps d'attente sont des CXPACKET liés au parallélisme.

Passez "max degree of parallelism" à 1,
faite un RAZ des stats et recommencez les tests et métriques...

A +

les résultats avec ce paramétrage.

Code :

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

1
2
 
ASYNC_NETWORK_IO	207.33	204.42	2.91	587692	99.03	0.0004	0.0003	0.0000

**SQLpro** · 02/02/2016, 10h49

Envoyé par monmien

les résultats avec ce paramétrage.

Code :

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

1
2
 
ASYNC_NETWORK_IO	207.33	204.42	2.91	587692	99.03	0.0004	0.0003	0.0000

Donc vous avez un problème de réseau..... probablement !

Ce qui signifie que vous devez tester en local sur le serveur puis débusquer ce qui se passe sur votre réseau....

Peut être une carte, un cable ou un port de switch défectueux.....

A +

**monmien** · 02/02/2016, 10h51

Envoyé par julien94320

Pour mon information quels sont les vitesses d'horloge des anciens processeur ? et des nouveaux processeurs ?
Utilisez vous des fonctions de formatage des données ?
C'est une piste a conserver, j'ai dejà eu le cas ou lors d'une migration ISO SQL Server sur une machine plus performante nous avons eu des baisse de performance, cela était du a du formatage de données qui solicitait énormément la vitesse d'horloge du CPU ...

L'ancien était un Xeon3430 2.40ghz, le nouveau un Xeon E5-2609 1.90ghz.
Formatage de donnée comme une gestion de date ? La requête qui me sert "d'étalon" est beaucoup basée sur des comparaison de date.

**julien94320** · 02/02/2016, 15h00

Envoyé par monmien

L'ancien était un Xeon3430 2.40ghz, le nouveau un Xeon E5-2609 1.90ghz.
Formatage de donnée comme une gestion de date ? La requête qui me sert "d'étalon" est beaucoup basée sur des comparaison de date.

Il y a des chances perso j'avais résolu le soucis avec des fonctions CLR et des DLL dans SSIS...

Après comme l'a vu SQL Pro il y a des chances pour que vous ayez des soucis de réseau ...

comment vont les cartes HBA ?
Les switch ...

**monmien** · 02/02/2016, 16h11

J'ai fait le test en local avec sqlcmd, je passe de 5min a 1min30. je ne suis pas au 4s de l'ancien mais il y'a clairement un soucis a ce niveau la.

J'aurais du faire le test plus tôt mais je pensais naïvement que SQL Studio ne faisait qu'envoyer puis recevoir le résultat de la requête.

Je vais supprimer mon agrégation de lien et passer par un seul cable de facon classique et changer de switch pour voir ce que ca donne et cibler le problème.

Dans tous les cas merci de prendre du temps pour me guider.

Edit 04/02 :
J'ai changé de switch, j'ai maintenant le même temps sur Sql Studio que en local (1mn 30), il va falloir que je vois pourquoi ce switch pose problème mais un problème après l'autre

Je refait un reset des stats et je refait tourner des test.

**monmien** · 04/02/2016, 11h04

J'ai toujours ASYNC_NETWORK_IO comme point de blocage...

Code :

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

1
2
 
ASYNC_NETWORK_IO	272.89	268.47	4.42	947438	97.66	0.0003	0.0003	0.0000

le réseau n'a plus d’impact, même temps en local que sur SSMS.

La remarque du dessus sur les conversions me met un doute, le sql server est une version anglaise, j'ai copié les bases depuis un sql FR, puis je me suis rendu compte que mes dates ne sortait pas au bon format, j'ai donc passé la langue en Fr, date au bon format pour moi problème réglé. Est-ce que ça peut être une raison de cette baisse de perfs ?

Edit 05/02 :
La copie de base est en cause, j'ai recrée la base par un script, puis importé les données, hop la requete tombe a 10s ce qui est plus proche de l'origine.
Je vais procéder comme ça, et refaire mes bases.

Chute des performances sur nouveau serveur

Administration SQL Server

Discussions similaires

Partager

Partager