Discussion :

[Batou69]

Bonjour à tous,

Nous rencontrons un sérieux problème de fonctionnement sur un site qui utilise SQL Server 2012.

- Nous utilisons SQL 2012 Standard x64 11.0.5058 (SP2) sur une machine équipée de 64Go de ram et 16 coeurs.
- Nous avons environ 50 tables. La plus grosse table de la base fait 140 000 lignes et la moyenne est d'environ 20 000.
- La base est répliquée sur le même serveur. De plus le serveur est dupliqué en temps réel avec Double Take
- Nous avons pour politique interne de n'utiliser que des procédures stockées pour nos requêtes.

Le problème est que les procédures prennent un temps fou à s'éxécuter. Certaines procédures qui mettent quelques millisecondes à s’exécuter en temps normal, prennent certains jours 10 secondes (jusqu'a 25s) à chaque exécution !.
Après de nombreuses investigations, nous avons découvert qu'elles ne restent pas dans le cache.

En fait sur le serveur nous avons environ 200 connexions et 500 PS. Seules 2 d'entre elles restent dans le cache en permanence (pourquoi ces deux là ?), les autre disparaissent toutes d'un coup à intervalles réguliers. Je sais que la recompilation d'une procédure est normal dans le cas de changement de stats par exemple, mais seulement les procédures impactées par ladite stat.
Le buffer cache est utilisé à 10%.
Si on regarde le plan du cache, lorsqu'il y est, le nombre de lignes estimé est souvent délirant pour une des tables. J'ai tenté de mettre à jours les statistiques à la main pour cette table. La performance est meilleure, sans etre au top, et surtout cela reste très provisoire.

Nous avons une grande habitude de SQL Server dans les versions précédentes et nous n'avons jamais rencontré ce problème.

Comment pouvons nous déterminer l'origine du problème ???

Merci pour votre aide !!

[hmira]

Juste pour confirmer vos propos :

Que donne le résultat de la requête ci-dessous :

Code SQL :

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

1
2
3
4
5
6
7
SELECT objtype  AS 'Cached_Object_Type',
count(*) AS 'Nombre_Plans',
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan_Cache_Size_in_MB',
avg(usecounts) AS 'Avg_Use_Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
GO

A+

[Batou69]

Le résultat :

Cached_Object_Type	Nombre_Plans	Plan_Cache_Size_in_MB	Avg_Use_Count
UsrTab	1	0	3516
Prepared	16	1	143
View	28	4	3954
AdHoc	35298	1233	12
Check	4	0	188
Trigger	4	0	158
Proc	192	163	755

Donc tout le cache est occupé par des requetes adhoc. Or nous n'utilisons pas ce genre de requetes (sauf pour les diags, tests, devs... ce qui est temporaire).
En fait le cache est rempli d'appel aux procédures avec les arguments différents :

exec MaProcedure 1,3
exec MaProcédure 1,5....

J'ai regardé sur nos autre serveurs en production en 2008R2 et la requete que vous m'avez donné est plutot du genre :

Cached_Object_Type	Nombre_Plans	Plan_Cache_Size_in_MB	Avg_Use_Count
UsrTab	6	0	14421
Prepared	30	3	833
View	114	11	5
AdHoc	3450	156	175
Check	20	0	8
Trigger	5	0	3
Proc	352	107	13164

Je précise que nous utilisons toujours la même DLL d'accès à la base de données et que l'application est de même type avec un certain nombre de programmes identique sans toutefois être une copie conforme.

[SQLpro]

Pourquoi cette réplication et comment est-elle faite ?
(peut être justement pourrie t-elle votre cache...)

Pourquoi Double Take et non pas le mirroring ou AlwaysON ?
Double take ne garantie pas le reprise d'une base de données MS SQL Server... du fait des opérations asynchrones entre fichiers de données et JT !

êtes vous sur de votre version ?
Pouvez vous nous donner le résultat de la requête suivante :

Code :

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

1
2
3
SELECT @@VERSION;
EXEC xp_msver;
SELECT * FROM sys.configurations;

Vos procédures contiennent-elles des commandes DDL (CREATE, ALTER, DROP) ? notamment pour les tables temporaires ???

A +

[Batou69]

Pourquoi cette réplication et comment est-elle faite ?
(peut être justement pourrie t-elle votre cache...)

La réplication est transactionnelle en push dans une base située dans la même instance sur le serveur.
Bon dis comme ça, cela parait débile mais : je ne réplique que les insert et update, pas les delete. Cela constitue donc une base de données d'historique que je peut vider selon un délai différent de la base dite de production.
J'ai pas trouvé mieux pour constituer une base historique sans développer d'usine à gaz. de plus le jour ou j'ai a besoin d'un peu plus de puissance je met en place deux serveurs différents sans changer notre méthode, l'abonné pouvant se trouver n'importe où.
Si vous avez mieux et plus simple je suis preneur....

Pourquoi Double Take et non pas le mirroring ou AlwaysON ?

Plusieurs raisons :

• Always on est nouveau et je ne sait pas m'en servir, je ne sais pas non plus quelles sont exactement ses possibilités (on aime pas changer de techno sans savoir si elle marche !!!)
• Les deux techniques SQL que vous citez ne mirrorent pas le système. Donc sur le serveur répliqué on est obligé de maintenir les applications à jour
• Double take est imposé par le client comme solution de sécurisation des serveurs, mais je doute qu'il sache quelles sont les conséquence sur SQL

Double take ne garantie pas le reprise d'une base de données MS SQL Server... du fait des opérations asynchrones entre fichiers de données et JT !

Ca je ne le savait pas, mais cela ne m'étonne pas du tout.... Je vais en parler au chef du projet qui sera ravi de l'apprendre

êtes vous sur de votre version ?
Pouvez vous nous donner le résultat de la requête suivante :
SELECT @@VERSION;
EXEC xp_msver;
SELECT * FROM sys.configurations;

Résultat : (désolé je ne sais pas copier les résultats proprement)
SELECT @@VERSION;

Code :

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

1
2
3
4
5
 
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.2 <X64> (Build 9200: )

EXEC xp_msver;

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
 
ProductName;NULL;Microsoft SQL Server
ProductVersion;720896;11.0.5058.0
Language;1036;Français (France)
Platform;NULL;NT x64
Comments;NULL;SQL
CompanyName;NULL;Microsoft Corporation
FileDescription;NULL;SQL Server Windows NT - 64 Bit
FileVersion;NULL;2011.0110.5058.00 ((SQL11_PCU_Main).140514-1820 )
InternalName;NULL;SQLSERVR
LegalCopyright;NULL;Microsoft Corp. All rights reserved.
LegalTrademarks;NULL;Microsoft SQL Server is a registered trademark of Microsoft Corporation.
OriginalFilename;NULL;SQLSERVR.EXE
PrivateBuild;NULL;NULL
SpecialBuild;331481088;NULL
WindowsVersion;331481088;6.2 (9200)
ProcessorCount;16;16
ProcessorActiveMask;NULL;            ffff
ProcessorType;8664;NULL
PhysicalMemory;65491;65491 (68671807488)
Product ID;NULL;NULL

SELECT * FROM sys.configurations;

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
61
62
63
64
65
66
67
68
 
101;recovery interval (min);0;0;32767;0;Maximum recovery interval in minutes;1;1
102;allow updates;0;0;1;0;Allow updates to system tables;1;0
103;user connections;0;0;32767;0;Number of user connections allowed;0;1
106;locks;0;5000;2147483647;0;Number of locks for all users;0;1
107;open objects;0;0;2147483647;0;Number of open database objects;0;1
109;fill factor (%);0;0;100;0;Default fill factor percentage;0;1
114;disallow results from triggers;0;0;1;0;Disallow returning results from triggers;1;1
115;nested triggers;1;0;1;1;Allow triggers to be invoked within triggers;1;0
116;server trigger recursion;1;0;1;1;Allow recursion for server level triggers;1;0
117;remote access;1;0;1;1;Allow remote access;0;0
124;default language;2;0;9999;2;default language;1;0
400;cross db ownership chaining;0;0;1;0;Allow cross db ownership chaining;1;0
503;max worker threads;0;128;65535;0;Maximum worker threads;1;1
505;network packet size (B);4096;512;32767;4096;Network packet size;1;1
518;show advanced options;1;0;1;1;show advanced options;1;0
542;remote proc trans;0;0;1;0;Create DTC transaction for remote procedures;1;0
544;c2 audit mode;0;0;1;0;c2 audit mode;0;1
1126;default full-text language;1036;0;2147483647;1036;default full-text language;1;1
1127;two digit year cutoff;2049;1753;9999;2049;two digit year cutoff;1;1
1505;index create memory (KB);0;704;2147483647;0;Memory for index create sorts (kBytes);1;1
1517;priority boost;0;0;1;0;Priority boost;0;1
1519;remote login timeout (s);10;0;2147483647;10;remote login timeout;1;0
1520;remote query timeout (s);600;0;2147483647;600;remote query timeout;1;0
1531;cursor threshold;-1;-1;2147483647;-1;cursor threshold;1;1
1532;set working set size;0;0;1;0;set working set size;0;1
1534;user options;0;0;32767;0;user options;1;0
1535;affinity mask;0;-2147483648;2147483647;0;affinity mask;1;1
1536;max text repl size (B);4194304;-1;2147483647;4194304;Maximum size of a text field in replication.;1;0
1537;media retention;0;0;365;0;Tape retention period in days;1;1
1538;cost threshold for parallelism;5;0;32767;5;cost threshold for parallelism;1;1
1539;max degree of parallelism;0;0;32767;0;maximum degree of parallelism;1;1
1540;min memory per query (KB);1024;512;2147483647;1024;minimum memory per query (kBytes);1;1
1541;query wait (s);-1;-1;2147483647;-1;maximum time to wait for query memory (s);1;1
1543;min server memory (MB);0;0;2147483647;16;Minimum size of server memory (MB);1;1
1544;max server memory (MB);32768;128;2147483647;32768;Maximum size of server memory (MB);1;1
1545;query governor cost limit;0;0;2147483647;0;Maximum estimated cost allowed by query governor;1;1
1546;lightweight pooling;0;0;1;0;User mode scheduler uses lightweight pooling;0;1
1547;scan for startup procs;1;0;1;1;scan for startup stored procedures;0;1
1549;affinity64 mask;0;-2147483648;2147483647;0;affinity64 mask;1;1
1550;affinity I/O mask;0;-2147483648;2147483647;0;affinity I/O mask;0;1
1551;affinity64 I/O mask;0;-2147483648;2147483647;0;affinity64 I/O mask;0;1
1555;transform noise words;0;0;1;0;Transform noise words for full-text query;1;1
1556;precompute rank;0;0;1;0;Use precomputed rank for full-text query;1;1
1557;PH timeout (s);60;1;3600;60;DB connection timeout for full-text protocol handler (s);1;1
1562;clr enabled;0;0;1;0;CLR user code execution enabled in the server;1;0
1563;max full-text crawl range;4;0;256;4;Maximum  crawl ranges allowed in full-text indexing;1;1
1564;ft notify bandwidth (min);0;0;32767;0;Number of reserved full-text notifications buffers;1;1
1565;ft notify bandwidth (max);100;0;32767;100;Max number of full-text notifications buffers;1;1
1566;ft crawl bandwidth (min);0;0;32767;0;Number of reserved full-text crawl buffers;1;1
1567;ft crawl bandwidth (max);100;0;32767;100;Max number of full-text crawl buffers;1;1
1568;default trace enabled;1;0;1;1;Enable or disable the default trace;1;1
1569;blocked process threshold (s);0;0;86400;0;Blocked process reporting threshold;1;1
1570;in-doubt xact resolution;0;0;2;0;Recovery policy for DTC transactions with unknown outcome;1;1
1576;remote admin connections;0;0;1;0;Dedicated Admin Connections are allowed from remote clients;1;0
1579;backup compression default;1;0;1;1;Enable compression of backups by default;1;0
1580;filestream access level;0;0;2;0;Sets the FILESTREAM access level;1;0
1581;optimize for ad hoc workloads;0;0;1;0;When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.;1;1
1582;access check cache bucket count;0;0;65536;0;Default hash bucket count for the access check result security cache;1;1
1583;access check cache quota;0;0;2147483647;0;Default quota for the access check result security cache;1;1
16384;Agent XPs;1;0;1;1;Enable or disable Agent XPs;1;1
16386;Database Mail XPs;0;0;1;0;Enable or disable Database Mail XPs;1;1
16387;SMO and DMO XPs;1;0;1;1;Enable or disable SMO and DMO XPs;1;1
16388;Ole Automation Procedures;0;0;1;0;Enable or disable Ole Automation Procedures;1;1
16390;xp_cmdshell;0;0;1;0;Enable or disable command shell;1;1
16391;Ad Hoc Distributed Queries;0;0;1;0;Enable or disable Ad Hoc Distributed Queries;1;1
16392;Replication XPs;0;0;1;0;Enable or disable Replication XPs;1;1
16393;contained database authentication;0;0;1;0;Enables contained databases and contained authentication;1;0

Vos procédures contiennent-elles des commandes DDL (CREATE, ALTER, DROP) ? notamment pour les tables temporaires ???

Nous n'utilsons ni curseurs, ni tables temporaires directement. Les procédures sont uniquement constituées d'une commande select, insert, update ou delete. En revanche, nous utilisons très intensivement les CTE, parfois plus de 20 à 30 dans certaines requetes extrêmes...

Pour ce qui est du serveur :
La base tempdb est sur le disque système (oui je sais c'est pas bien) mais on a que 2 disques et on a préféré la séparer des disques de données normales. elle comporte 8 fichiers comme suggéré par pas mal de posts sur le sujet....
Le plan de maintenance est effectué toutes les nuit hors production, il réorganise ou reconstruit les index selon le taux de fragmentation (entre 10 et 30% reorg, > 30% rebuild)
On effectue aussi un update systématique des statistiques qui on plus d'un jour. MAIS : il y a boulette dans le script car on utilise les options par défaut et donc il me semble qu'il fait un sample de la table, pas un fullscan.

A+

[SQLpro]

La réplication est transactionnelle en push dans une base située dans la même instance sur le serveur.
Bon dis comme ça, cela parait débile mais : je ne réplique que les insert et update, pas les delete. Cela constitue donc une base de données d'historique que je peut vider selon un délai différent de la base dite de production.
J'ai pas trouvé mieux pour constituer une base historique sans développer d'usine à gaz. de plus le jour ou j'ai a besoin d'un peu plus de puissance je met en place deux serveurs différents sans changer notre méthode, l'abonné pouvant se trouver n'importe où.
Si vous avez mieux et plus simple je suis preneur....

Vous feriez mieux d'utiliser change tracking ou change data capture et faire le différentiel aux heures creuses

Plusieurs raisons :

• Always on est nouveau et je ne sait pas m'en servir, je ne sais pas non plus quelles sont exactement ses possibilités (on aime pas changer de techno sans savoir si elle marche !!!)
• Les deux techniques SQL que vous citez ne mirrorent pas le système. Donc sur le serveur répliqué on est obligé de maintenir les applications à jour
• Double take est imposé par le client comme solution de sécurisation des serveurs, mais je doute qu'il sache quelles sont les conséquence sur SQL

AlwaysOn est extrêmement fiable. Nous l'utilisons par exemple pour Geodis sur une base de 7 To.

Vous avez donc des applications sur le serveur. C'est probablement cela qui fait que votre système est bancal. SQL Server doit être installé sur un serveur dédié et aucune autre application ni service ne doit tourner sur ce serveur, pas même un antivirus !

SELECT * FROM sys.configurations;

vous devriez régler correctement le parallélisme :
1) cost threshold for parallelism à 12 ou 24
2) max degree of parallelism à 2 ou 4

Pourquoi un max server memory à 32768 ? avez-vous d'autres instances sur le même serveur ? d'autres applications ? d'autres services ??? Lesquels ??????
Si non, alors mettez 60 Go

Placez le OPTIMIZE FOR ad hoc workloads à 1

Pour ce qui est du serveur :
La base tempdb est sur le disque système (oui je sais c'est pas bien) mais on a que 2 disques et on a préféré la séparer des disques de données normales. elle comporte 8 fichiers comme suggéré par pas mal de posts sur le sujet....

sur le disque local, c'est bien. Mais en revanche 8 fichiers c'est beaucoup. Passez à 4 et régler le parallélisme à 4 maximum. Faites de même pour votre base de production => 4 fichiers d'égale longueur pour les données

Le plan de maintenance est effectué toutes les nuit hors production, il réorganise ou reconstruit les index selon le taux de fragmentation (entre 10 et 30% reorg, > 30% rebuild)

vous voulez dire "aux heures creuses" ?

On effectue aussi un update systématique des statistiques qui on plus d'un jour. MAIS : il y a boulette dans le script car on utilise les options par défaut et donc il me semble qu'il fait un sample de la table, pas un fullscan.

Ce serait mieux !

A +

[hmira]

Oui, effectivement, il y a manifestement une différence de comportement entre votre Serveur de prod (SQL Server 2008 R2 ) et le Seveur SQL 2012 !
Ci-dessous quelques pistes à vérifier
- Vérifiez et comparer (entre les 2 Serveurs) le paramétrage de la base de données ( PARAMETERIZATION) SIMPLE ou FORCED (?). En effet ce paramétrage (PARAMETERIZATION = FORCED) peut limiter la prolifération des requêtes AdHoc. Mais si ce paramétrage n'était actif pour la base de données, sur le Serveur de prod SQL Server 2008 R2, il n'y a aucune raison de l'activer pour la base dans l'environnement SQL Server 2012, tant que vous n'avez déterminé les causes exacts du problème dans un environnement censé être presque identique.

- Vérifier le contenu des ces fameuses requête SQL AdHoc (à partir de leur plan_handle), Vérifier si celles-ci proviennent bien de vos applications ou si leur texte ne vous évoquent rien et au quel elle proviendraient d'autres applications qui vous ne gérez pas et vous ne connaissez pas (?)
-- Exemple :

Code SQL :

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

1
2
3
4
5
6
7
select p.refcounts, p.usecounts, p.plan_handle, s.text
from sys.dm_exec_cached_plans as p
cross apply sys.dm_exec_sql_text (p.plan_handle) as s
where p.cacheobjtype = 'compiled plan'
and p.objtype = 'adhoc'
AND p.usecounts <= 3 
order by p.usecounts asc

- Vérifiez, par exemple avec le profiler, si les connexions de vos applications ne modifient pas les options SET de façons non homogène d'une session à une autre de vos application. En effet, les options SET font partie intégrante du cahe plan, et les modifier fait en sorte qu'un plan déjà existant compilé avec d'autres options SET ne sera pas réutilisé (voir sys.dm_exec_plan_attributes (plan_handle)).

Voilà j'espère que d'autres experts sur ce forum (et il y en a !) vous donneront d'autres conseils et d'autres pistes à explorer ...

A+

PS : A propos d'expert ! j'avais pas vu la réponse de SQLPro ci-dessus !

[iberserk]

Bonsoir,

passer presque tous les recorsets des application en forwardonly -> supprime les FETCH API_CURSOR (environ 30000 plan dans le cache !!!)

Quel langage utilisez vous pour générer des FETCH_API_CURSOR???

Je ne suis pas sûr d'avoir bien lu... vos requètes sont-elles bien paramétrées?
Vous pouvez tentez d'activer la trace 2371 pour améliorer l'auto update stats (au fait est-il bien activé???) et ainsi éviter la mise à jour "sauvage" des 4 tables.

Pour vérification quand le plan d'exécution se "plante" quelle est la valeur réellement prise (valeur du paramètre qui a servit à compiler le plan)?

Vous n'utilisez aucune variable table dans vos SP?

Avez vous vérifié qu'aucun guide de plan n'est présent?

Dernière question vos contraintes sont-elles bien considéré comme valide par SQL SERVER (WITH CHECK...) (rare... mais déjà vu chez un éditeur)

[Batou69]

Bonsoir,


Quel langage utilisez vous pour générer des FETCH_API_CURSOR???

Je ne suis pas sûr d'avoir bien lu... vos requètes sont-elles bien paramétrées?

Bonjour,

Le langage que nous utilisons est le C++ avec Qt. Nous utilisons le driver ODBC version 10.0 (le sql native client)

Nos procédures comportent des paramètres. Cependant, je ne suis pas sûr que nos requetes soient vues commes paramétrées par le moteur..... Je ne sais pas comment le savoir.

Vous pouvez tentez d'activer la trace 2371 pour améliorer l'auto update stats (au fait est-il bien activé???) et ainsi éviter la mise à jour "sauvage" des 4 tables.

Non la trace XXXX n'est pas activée. Je la découvre.

D'une façon générale, je me considère comme un fervent partisant de SQL Server, mais je suis exaspéré par toutes ces options cachées que l'on découvre au détour des forums et qui changent le moteur.
Ne peut il pas exister quelque part un répertoire de ce qu'il faut faire pour que l'installation soit faite correctement ????
Une sorte de livre blanc sur les meilleures pratiques ?
/fin de la parenthèse

Pour vérification quand le plan d'exécution se "plante" quelle est la valeur réellement prise (valeur du paramètre qui a servit à compiler le plan)?

Je ne sais pas comment le vérifier... D'autant plus que certaines requetes qui partent en vrille n'ont pas de paramètres.

Vous n'utilisez aucune variable table dans vos SP?

Avez vous vérifié qu'aucun guide de plan n'est présent?

Dernière question vos contraintes sont-elles bien considéré comme valide par SQL SERVER (WITH CHECK...) (rare... mais déjà vu chez un éditeur)

Nous n'avons pas de variable table dans nos SP. Uniquement des parametres et des gros cte dans tous les sens....
Pas non plus de 'repères de plan'

Enfin pour le check, si j'ai bien compris votre question, elles ont toutes le champ i's_not_trusted' à 0 dans la table sys.foreign_keys

Merci pour votre aide !!

[Batou69]

Arg...

plus personne ?

[SQLpro]

Nous avons fait :
- passer presque tous les recorsets des application en forwardonly -> supprime les FETCH API_CURSOR (environ 30000 plan dans le cache !!!)

Je ne serais pas étonné que vos curseurs bouffent toute la mémoire au détriment du cache !

Sont-ce des curseurs côté client ou côté serveur ?

Ou est situé l'applicatif ? Sur le client ? Sur un serveur à part ? Sur le serveur SQL ?

Avez-vous dimensionné correctement tout vos espaces de stockage ? (pour ne jamais avoir de croissance de fichier...)

A +

[iberserk]

Est-ce vous qui générez vous même les curseur, si non pouvez vous publier un exemple de code d'accès aux données?

Pk ne pas passer par ADO? (SqlCommand/SqlConnection/SqlDataReader).

Cordialement