Performance très différente entre différents serveurs SQL (TEST/PROD)

Version imprimable

16/12/2015, 17h46
[DreaMs]

Performance très différente entre différents serveurs SQL (TEST/PROD)

Bonjour,

Suite à l'installation de notre application chez le client nous avons constaté des différences de performances très importantes. Après avoir investigué il s'agit de problèmes de performance au niveau du SQL Server.
Nous sommes pourtant sur des versions identiques avec la même base de données. Les 2 SQL Server ont été installé de base sans customisation. Le serveur du client est normalement plus performant.

N'étant pas expert SQL Server je ne sais pas comment investiguer sur ce problème.

Y a t'il un moyen simple de mesurer les performances brutes d'un SQL Server ?
Quel sont les points qui pourrait expliquer ces différences de performance ?

Bref ... comment investiguer !

D'avance merci

Peux tu commencer par mesurer les attentes sur votre instance

Code:

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
 
WITH
	CTE_WAIT AS
	(	
		SELECT	wait_type, 
			wait_time_ms / 1000.0 AS wait_time_s,
			100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
			ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS n
		FROM	sys.dm_os_wait_stats
		WHERE	wait_type NOT IN
		(
			'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE'
			, 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH'
			, 'WAITFOR', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT'
		)
	)
SELECT		W.wait_type,
		CAST(W.wait_time_s AS decimal(12,2)) AS wait_time_s,
		CAST(W.pct AS decimal(4,2)) AS pct,
		CAST(SUM(W2.pct) AS decimal(12,2)) AS running_pct
FROM		CTE_WAIT AS W
INNER JOIN	CTE_WAIT AS W2 ON W2.n <= W.n
GROUP BY	W.n, 
		W.wait_type,
		W.wait_time_s,
		W.pct
HAVING		SUM(W2.pct) - W.pct < 95

17/12/2015, 09h39
julien94320
Bonjour,

peut -on avoir les resultats des requetes suivantes :
- EXEC xp_msver
- SELECT @@VERSION
Peux tu nous donner le détail des configurations physiques et des OS installés sur les environnements ainsi que :
- La quantité de RAM sur les machines ?
- La quantité de RAM affecté à SQL Server ?
- Le reglage du parrallelisme sur l'instance de DEV / Du reglage du parrallelisme sur l'instance de PROD ?
- Les types de disques et le type de stockage affectés sur chaque instances (SAN dédié ? Nombre de LUN ? type de découpage et de disques affectés ? Type de RAID ? )
- Type et nombre de processeurs et vitesse d'horloge sur chaque environnement ?
- Machine Physique ? Machine virtuelle ?
...

Autre point :
- Après l'installation de votre programme chez votre client avez vous mis a jour les stats en full scan sur la nouvelle instance ?
Bref de prime abord rien ne me choque qu'il y est une différence de perf (même avec une machine plus puissante) 8O.

SQL server fonctionne enormement avec la machine et les reglages de la machine ;)
17/12/2015, 10h45
SQLpro

Comment avez vous installé votre application au niveau des données :
1) RESTAURATION de votre base chez le serveur du client ?
2) création de la base et des tables par script SQL (CREATE DATABASE... CREATE TABLE...) et alimentation des tables via des commandes SQL INSERT ou fichier (BULK INSERT ..) ?

Ensuite, quelle est la version de SQL Server utilisée chez vous (2005, 2008, 2008R2, 2012, 2014...) et chez le client ?

Est-ce un serveur physique ou une VM ?

Enfin, quelle est la configuration du serveur du client au niveau hardware (RAM, CPU/core, disques...) et au niveau SQL ? (lancez un exec sp_configure)

Sans cela il n'est pas possible de vous aiguiller.

A +
17/12/2015, 11h56
rudib

Bonjour,

Vous pouvez utiliser mon outil SQL Trismegiste (lien dans la signature) pour récupérer des statistiques sur les deux serveurs, et les joindre ici pour analyse. SQL Trismegiste vous permet de générer un fichier zip qui contient le résultat de nombreuses requêtes de diagnostic.
17/12/2015, 14h16
[DreaMs]

Voici un maximum d'informations par rapport à vos demandes :

1) RESTAURATION de votre base chez le serveur du client ?

La base est restauré (sur une base vierge) via un backup complet réalisé depuis notre base.

x) Est-ce un serveur physique ou une VM ?

Dans les 2 cas ce sont des VMs :

Nous : 2vCPU (CPU physique de la machine hôte : Intel Xeon E5-2620 v3 @ 2,40GHz) / 8Go RAM / Disque SAS 600Go 10ktr/mn

Client : 6vCPU (dédié) (CPU physique de la machine hôte : AMD Opteron 6320 @ 2,40GHz (2 CPU)) / 12Go RAM / Je n'ai pas l'information sur les disques (pour l'instant)

x) EXEC xp_msver / SELECT @@VERSION

Nous :

Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Index Name Internal_Value Character_Value
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 720896 11.0.5343.0
3 Language 1033 Anglais (États-Unis)
4 Platform NULL NT x64
5 Comments NULL SQL
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT - 64 Bit
8 FileVersion NULL 2011.0110.5343.00 ((SQL11_SP2_GDR).150504-1902 )
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft SQL Server is a registered trademark of Microsoft Corporation.
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 350158848 NULL
15 WindowsVersion 350158848 6.1 (7601)
16 ProcessorCount 2 2
17 ProcessorActiveMask NULL 3
18 ProcessorType 8664 NULL
19 PhysicalMemory 8192 8192 (8589467648)
20 Product ID NULL NULL

Client :

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

1 ProductName NULL Microsoft SQL Server
2 ProductVersion 720896 11.0.5058.0
3 Language 1033 English (United States)
4 Platform NULL NT x64
5 Comments NULL SQL
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT - 64 Bit
8 FileVersion NULL 2011.0110.5058.00 ((SQL11_PCU_Main).140514-1820 )
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft SQL Server is a registered trademark of Microsoft Corporation.
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 331481088 NULL
15 WindowsVersion 331481088 6.1 (7601)
16 ProcessorCount 6 6
17 ProcessorActiveMask NULL 3f
18 ProcessorType 8664 NULL
19 PhysicalMemory 12287 12287 (12884369408)
20 Product ID NULL NULL

x) exec sp_configure :

Nous :

name minimum maximum config_value run_value
allow updates 0 1 0 0
backup compression default 0 1 0 0
clr enabled 0 1 0 0
contained database authentication 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
filestream access level 0 2 2 1
max text repl size (B) -1 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 10 10
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

Client :

name minimum maximum config_value run_value
allow updates 0 1 0 0
backup compression default 0 1 0 0
clr enabled 0 1 0 0
contained database authentication 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
filestream access level 0 2 1 1
max text repl size (B) -1 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 10 10
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

x) Mesure des attentes :

Nous :

wait_type wait_time_s pct running_pct
FT_IFTS_SCHEDULER_IDLE_WAIT 1262167.14 20.29 20.29
DIRTY_PAGE_POLL 430511.97 6.92 27.22
REQUEST_FOR_DEADLOCK_SEARCH 430508.54 6.92 34.14
SP_SERVER_DIAGNOSTICS_SLEEP 430501.31 6.92 41.06
SQLTRACE_INCREMENTAL_FLUSH_SLEEP 430500.43 6.92 47.98
XE_TIMER_EVENT 430489.89 6.92 54.90
LOGMGR_QUEUE 430486.01 6.92 61.82
HADR_FILESTREAM_IOMGR_IOCOMPLETION 430479.51 6.92 68.74
BROKER_EVENTHANDLER 430479.19 6.92 75.67
FT_IFTSHC_MUTEX 430449.46 6.92 82.59
XE_DISPATCHER_WAIT 430437.43 6.92 89.51
CHECKPOINT_QUEUE 428243.20 6.89 96.39

(note : Notre serveur est mutualisé avec beaucoup d'autres bases alors que le serveur du client n'a que notre base)

Client:

DIRTY_PAGE_POLL 16332.82 9.54 9.54
LOGMGR_QUEUE 16332.77 9.54 19.08
XE_TIMER_EVENT 16331.37 9.54 28.62
REQUEST_FOR_DEADLOCK_SEARCH 16331.18 9.54 38.16
HADR_FILESTREAM_IOMGR_IOCOMPLETION 16330.75 9.54 47.70
SQLTRACE_INCREMENTAL_FLUSH_SLEEP 16329.22 9.54 57.24
XE_DISPATCHER_WAIT 16320.91 9.53 66.77
SP_SERVER_DIAGNOSTICS_SLEEP 16200.05 9.46 76.23
FT_IFTS_SCHEDULER_IDLE_WAIT 16140.13 9.43 85.66
CHECKPOINT_QUEUE 16127.43 9.42 95.08

x) Le réglage du parallélisme sur l'instance de DEV / Du réglage du parallélisme sur l'instance de PROD ?

Je ne connais pas ce réglage.

D'avance merci pour votre aide
17/12/2015, 14h48
julien94320

1 pièce(s) jointe(s)
Pour le parrallelisme :

Faite un clic droit sur l'instance :

advanced puis les valeurs dans parrallelisme :

Pièce jointe 195576

Déjà a première vue quelques points :
- Il faut limiter la mémoire allouer a sql Server car le SGBD peut s'accaparer de l'ensemble de la mémoire existante sur le systeme ce qui peut le geler
- La version de votre machine de prod est moins a jour que votre machine de dev
- vu la méthode de transaction des données il faut mettre a jour les stats en fullscan sur l'ensemble des bases
17/12/2015, 15h52
SQLpro

En sus des choses dites :

Votre client est sur une version Standard, vous une version Enterprise. Certaines fonctionnalités fonctionnent différemment par exemple les vues indexées.

D'autre part vous êtes au niveau 11.0.5343.0 et lui au niveau 11.0.5058.0, faites lui passer les SP et correctifs manquants

A +
18/12/2015, 16h51
[DreaMs]

Au niveau du parallélisme j'ai les valeurs par défaut (les mêmes que dans la screenshot 5 0 0 -1).

Infos supplémentaires:
- Nous n'utilisons pas de vue.
- Nos tables dépassent rarement 1 millions d'éléments mais utilisant un système de méta-modèle nous avons pas mal de jointure dans nos requêtes
- Nous utilisons beaucoup les tables temporaires

D'autre part un benchmark (NovaBench et CrystalMarkDisk) remonte que sur le serveur du client la mémoire RAM est plus lente (Presque 3fois plus lente d'après NovaBench), et aussi que les accès disques dûrs sont beaucoup plus rapide sur des fichiers de 1Go mais beaucoup moins rapide sur des fichiers 50Mo ... Est-ce que ces éléments peuvent jouer un rôle important ?

En attendant je vais :
- demander au client de mettre à jour son SQL Server
- regarder pour le full scan (que je ne maitrise pas pour l'instant).
- limiter la mémoire de SQL Server (mais d'après nos constatations pour l'instant elle ne prenait pas toute la mémoire de la machine (environ 3Go sur 8 ou 12 selon les machines)
- faire un test avec un SQL Server standard
20/12/2015, 06h54
SQLpro

1 pièce(s) jointe(s)

Citation:

Envoyé par [DreaMs]

Au niveau du parallélisme j'ai les valeurs par défaut (les mêmes que dans la screenshot 5 0 0 -1).

Infos supplémentaires:
- Nous n'utilisons pas de vue.
- Nos tables dépassent rarement 1 millions d'éléments mais utilisant un système de méta-modèle nous avons pas mal de jointure dans nos requêtes
- Nous utilisons beaucoup les tables temporaires

D'autre part un benchmark (NovaBench et CrystalMarkDisk) remonte que sur le serveur du client la mémoire RAM est plus lente (Presque 3fois plus lente d'après NovaBench),

Oui, cela a un impact énorme car entre 95 et 98% des opérations se font en RAM !

Citation:

et aussi que les accès disques dûrs sont beaucoup plus rapide sur des fichiers de 1Go mais beaucoup moins rapide sur des fichiers 50Mo ...

Moins important pour les performances, mais bon pour la concurrence d'accès au niveau transactionnelle (écritures)

Citation:

Est-ce que ces éléments peuvent jouer un rôle important ?

Il est très probable que ces éléments soient liés à la VM et en particulier au "ballooning" qu'il faut désactiver.
Voir aussi s"'il n'y a pas un problème de nœud NUMA (un CPU qui devrait utiliser une partie de la RAM mais ne peut y accéder et fait du "tunneling"

Citation:

En attendant je vais :
- demander au client de mettre à jour son SQL Server
- regarder pour le full scan (que je ne maitrise pas pour l'instant).
- limiter la mémoire de SQL Server (mais d'après nos constatations pour l'instant elle ne prenait pas toute la mémoire de la machine (environ 3Go sur 8 ou 12 selon les machines)
- faire un test avec un SQL Server standard

Si l’application est critique, les données sensible, la volumétrie importante, le nombre d'accès élevé, et surtout s'il n'y a pas de compétence forte sur le système de VM, alors, passez en machine physique....

A +

PS : sur notre livre nous expliquons cela en détailPièce jointe 195781
23/12/2015, 16h34
[DreaMs]

Je reviens un peu à la charge. J'ai étudié des séquences enregistré avec SQL Server Profiler et il s'avère que toutes les requêtes sont plus lente chez les clients que chez nous. Les SELECT encore plus que les INSERT. Il y a aussi certaines requêtes ou le nombre de "Reads" est très supérieur chez le client (alors que la BDD est quasi la même).

Est-ce que cela permet de tirer quelques hypothèses de causes ?
24/12/2015, 09h17
julien94320

A mon sens la configuration des disques joue beaucoup RAID 5 ? Type de disques ? liaison avec les disques ?

Vous avez mis a niveau la version de SQL server de votre client ?
Mis a jour les stats ?

Qu'est ce qu'il y a comme autre VM sur l'environnement du client ?

Les baie de stockage est elle mutualisée ? Si oui quels sont les autres applications qui l'utilisent ?
24/12/2015, 12h12
SQLpro

Citation:

Envoyé par [DreaMs]

Je reviens un peu à la charge. J'ai étudié des séquences enregistré avec SQL Server Profiler et il s'avère que toutes les requêtes sont plus lente chez les clients que chez nous. Les SELECT encore plus que les INSERT. Il y a aussi certaines requêtes ou le nombre de "Reads" est très supérieur chez le client (alors que la BDD est quasi la même).

Est-ce que cela permet de tirer quelques hypothèses de causes ?

Dans ce sens c'est peut être un manque d'index !

A +
11/01/2016, 22h35
iberserk

Citation:

Dans ce sens c'est peut être un manque d'index !

La base client est une restau de celle de DEV si j'ai bien compris donc les indexes doivent-être les même.

Avec vous un plan de maintenance ? (reconstruction des indexes et des statistiques).

PErsonne ne semble le relever mais le client est en AMD... la dev en INTEL, à fréquence égale je doute qu'ils soient à la hauteur...
Pouvez vous poster un exemple de requêtes dont les performances sont drastiquement différentes (plans d'éxécutions)?
12/01/2016, 17h18
mikedavem
et les statistiques d'exécution CPU, temps et IO ?
Code:

1 2 3 4 SET STATISTICS TIME ON; SET STATISTICS IO ON; -- exécution requête
ou éventuellement avec le profiler ou XE ...

++