Discussion :

[Donpi]

Bonjour,

Je cherche des pistes ou des axes d'analyse pour un problème de réplication qui plante aléatoirement.

Mon infrastructure est assez simple, j'ai un Server SQL 2012 sur une machine virtuelle dont certaines tables sont répliquées sur deux machines avec leur propre instance de SQL Server 2012.
Tout a bien fonctionné pendant des mois, mais depuis 3 semaine j'ai la réplication qui plante sur une des deux machines, toujours la même, toujours dans la deuxième moitie de la semaine.

Lors que je relance la tâche de synchronisation manuellement, la réplication se termine et j'ai bien toutes mes données.

Au niveau des messages d'erreurs, voilà ce que j'ai :

2016-03-17 00:19:58.692 Bulk copying data into table 'XL_JourFerie_Pdv'
2016-03-17 00:19:58.708 Bulk copied data into table 'XL_JourFerie_Pdv' (624 rows)
2016-03-17 00:19:58.724 Bulk copying data into table 'XL_JourFerie_Pdv'
2016-03-17 00:19:58.755 Bulk copied data into table 'XL_JourFerie_Pdv' (4005 rows)
2016-03-17 00:19:58.864 <stats state="2" fetch="0" wait="1178" cmds="1122" callstogetreplcmds="1"><sincelaststats elapsedtime="1178" fetch="0" wait="1178" cmds="1122" cmdspersec="0.000000"/><message>Raised events that occur when an agent's reader thread waits longer than the agent's -messageinterval time. (By default, the time is 60 seconds.) If you notice State 2 events that are recorded for an agent, this indicates that the agent is taking a long time to write changes to the destination.</message></stats>
2016-03-17 00:30:46.241 Delivering replicated transactions
2016-03-17 00:30:46.335 <stats state="2" fetch="0" wait="1825" cmds="1248" callstogetreplcmds="1"><sincelaststats elapsedtime="647" fetch="0" wait="647" cmds="126" cmdspersec="0.000000"/><message>Raised events that occur when an agent's reader thread waits longer than the agent's -messageinterval time. (By default, the time is 60 seconds.) If you notice State 2 events that are recorded for an agent, this indicates that the agent is taking a long time to write changes to the destination.</message></stats>
2016-03-17 00:30:46.335 Applied script 'MODE_1_12.dri'
2016-03-17 00:30:46.335 Applied script 'Pdv_16.dri'
2016-03-17 00:35:46.342 The process is running and is waiting for a response from the server.
2016-03-17 00:40:46.443 The process is running and is waiting for a response from the server.
2016-03-17 00:45:46.544 The process is running and is waiting for a response from the server.
2016-03-17 00:50:46.643 The process is running and is waiting for a response from the server.
2016-03-17 00:55:46.707 The process is running and is waiting for a response from the server.
2016-03-17 01:00:46.551 Query timeout expired
2016-03-17 01:00:46.957 Category:COMMAND
Source: Failed Command
Number:
Message: IF OBJECT_ID(N'[dbo].[FK_ARRMAG_IDCOULMOD]') IS NULL
BEGIN
ALTER TABLE [dbo].[ARRMAG] ADD CONSTRAINT [FK_ARRMAG_IDCOULMOD] FOREIGN KEY
([IDCOULMOD])
REFERENCES
[dbo].[COULMOD]([IDCOULMOD])
NOT FOR REPLICATION
ALTER TABLE [dbo].[ARRMAG] CHECK CONSTRAINT [FK_ARRMAG_IDCOULMOD]
END


2016-03-17 01:00:46.957 Category:NULL
Source: Microsoft SQL Server Native Client 11.0
Number: HYT00
Message: Query timeout expired

Au niveau des logs :

LogDate ProcessInfo Text
2016-03-17 00:00:19.250 spid29s This instance of SQL Server has been using a process ID of 2460 since 07.03.2016 14:00:29 (local) 07.03.2016 13:00:29 (UTC). This is an informational message only; no user action is required.
2016-03-17 01:00:02.530 spid56 AppDomain 12 (SSISDB.dbo[runtime].11) created.
2016-03-17 01:00:02.740 spid56 Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 12 (SSISDB.dbo[runtime].11).
2016-03-17 01:00:02.990 spid56 Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 12 (SSISDB.dbo[runtime].11).
2016-03-17 01:00:08.410 spid57 Configuration option 'min server memory (MB)' changed from 2000 to 22000. Run the RECONFIGURE statement to install.
2016-03-17 01:00:08.410 spid57 Configuration option 'max server memory (MB)' changed from 4000 to 22000. Run the RECONFIGURE statement to install.
2016-03-17 01:00:08.410 spid57 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2016-03-17 01:00:08.410 spid57 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2016-03-17 01:00:08.410 spid57 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2016-03-17 02:24:57.240 spid27s AppDomain 12 (SSISDB.dbo[runtime].11) is marked for unload due to memory pressure.
2016-03-17 02:24:57.340 spid27s AppDomain 12 (SSISDB.dbo[runtime].11) unloaded.
2016-03-17 02:25:37.810 spid57 Error: 1204, Severity: 19, State: 4.
2016-03-17 02:25:37.810 spid57 The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Le package SSIS qui pilote tout ça commence à 1h00.

Qqun a-t-il une idée ?



Edit :

J'ai aussi cette erreur là dans l'observateur d’evenement système :

Les paramètres d’autorisation spécifiques à l’application n’accordent pas d’autorisation Local Activation pour l’application serveur COM avec le CLSID
{FDC3723D-1588-4BA3-92D4-42C430735D7D}
et l’APPID
{83B33982-693D-4824-B42E-7196AE61BB05}
au SID NT SERVICE\SQLSERVERAGENT de l’utilisateur (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) depuis l’adresse LocalHost (utilisation de LRPC). Cette autorisation de sécurité peut être modifiée à l’aide de l’outil d’administration Services de composants.

[SQLpro]

Vous avez visiblement des tâches de la réplication qui se superposent dans le temps alors qu'elles ne devrait pas.

Le process a dû être rendu lent probablement il y a 3 semaines, par une modification au niveau système.
Investiguez ce qui s'est passé il y a 3 semaine dans la VM, l'OS, le réseau.
Ce peut être par exemple le changement du stockage, des coeurs attribués à la VM, de la RAM.... ou un goulet d'étranglement dans le réseau !

Et au passage, vive les VM, cette merde infame qui pourrit tout !

A +

[elsuket]

Il y a tout de même quelques choses assez bizarres dans les logs :

2016-03-17 01:00:08.410 spid57 Configuration option 'min server memory (MB)' changed from 2000 to 22000. Run the RECONFIGURE statement to install.
2016-03-17 01:00:08.410 spid57 Configuration option 'max server memory (MB)' changed from 4000 to 22000. Run the RECONFIGURE statement to install.
2016-03-17 01:00:08.410 spid57 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2016-03-17 01:00:08.410 spid57 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2016-03-17 01:00:08.410 spid57 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

D'abord on change la configuration mémoire de SQL Server, de 2000 à 22000, puis une nouvelle fois de 4000 à 22000, ce qui suppose qu'entre temps un autre acteur a modifiée de 22000 à 4000, ou bien que l'on a affaire à deux instances.
Dans les deux cas, ces changements de configuration sont très suspects.

Les lignes qui suivent montrent une pression mémoire : des plans sont évincés du cache de requêtes pour dégager de la mémoire.
Cela sent donc la requête qui a une empreinte IO élevée. Cela peut aussi être du à la quantité de mémoire octroyée à la requête, ...

Cela expliquerait donc le message suivant :

016-03-17 02:25:37.810 spid57 The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Sous l'effet de la pression mémoire, le moteur ne peut pas acquérir les (nombreux ? ) verrous nécessaires à l’exécution des requêtes.

Il faudrait donc prendre une trace côté serveur pour comprendre ce qui se passe au moment de l'exécution.

@++

[elsuket]

Et au passage, vive les VM, cette merde infame qui pourrit tout !

Tu aurais pas l'impression d'exagérer un peu là ?
Tu sais comme moi qu'il y a aussi des gens qui disent que SQL Server c'est de la merde, et on sait aussi que c'est souvent mal configuré, mal modélisé, mal développé ...
C'est comme tous les outils, il "suffit" de savoir s'en servir. Rappelles-toi que c'est notre gagne-pain

@++

[aieeeuuuuu]

Bonjour,

ce qui suppose qu'entre temps un autre acteur a modifiée de 22000 à 4000, ou bien que l'on a affaire à deux instances.
Dans les deux cas, ces changements de configuration sont très suspects.

Non,il s'agit des deux paramètres différents

2016-03-17 01:00:08.410 spid57 Configuration option 'min server memory (MB)' changed from 2000 to 22000. Run the RECONFIGURE statement to install.
2016-03-17 01:00:08.410 spid57 Configuration option 'max server memory (MB)' changed from 4000 to 22000. Run the RECONFIGURE statement to install.

[Donpi]

Re,

Alors le seul server qui est virtualisé c'est le fournisseur qui tourne a merveille.

Les 2 abonnés sont des PC sous Windows Seven avec de gros processeurs.
Avant de me lancer des cailloux, sachez que j'ai choisis cette option car j'avais besoins de puissance de calcul brute et que les coeur des serveur virtuels sont souvent limités a ce niveau.
De plus comme on a une licence standard les cubes se calculent en simple cœur bla bla bla bla.

Bref, les 2 machines sont identiques avec une petit disque à plateaux pour le système et un terra en SSD pour le reste.
SQL server est installé sur les disques à plateaux mais toutes les bases, y compris tempdb, les logs et le fichier de swap de windows sont sur le ssd.

En gros j'ai un package SSIS qui fait les actions suivantes :
-Configure la mémoire au maximum pour sqlserver.
-Réplication
-Boucle d'attente que la réplication aie terminé (ou planté en occurrence)
-Pré traitement SQL des données
-Configure la mémoire à 50% pour sqlserver.
-Calcul des cubes
-Configure la mémoire au minimum pour sqlserver.

De cette façon j'ai toutes les ressource à disposition quand j'en ai besoin.

[SQLpro]

En gros j'ai un package SSIS qui fait les actions suivantes :
-Configure la mémoire au maximum pour sqlserver.
-Réplication
-Boucle d'attente que la réplication aie terminé (ou planté en occurrence)
-Pré traitement SQL des données
-Configure la mémoire à 50% pour sqlserver.
-Calcul des cubes
-Configure la mémoire au minimum pour sqlserver.

De cette façon j'ai toutes les ressource à disposition quand j'en ai besoin.

Ce n'est pas une façon orthodoxe de faire... En effet l'usage d'une reconfiguration devrait être exceptionnelle. Chaque fois que vous allez faire baisser la mémoire SQL Server, vous allez devoir faire ,dégager en urgence une quantité impressionnante de page en cache et de ce fait, si des données nécessaires à une requête ont été sorties, il va falloir bouffer du disque pour les y remettre.

Il est probable que votre réplication s'attendait à trouver les données en cache et comme elles sont dégagées il y a chevauchement des process du fait des temps d'attente.

sp_configure doit être faite hors production !

Je serais curieux de voir les temps de sys.dm_os_wait_stats...


A +

[Donpi]

Ce n'est pas une façon orthodoxe de faire... En effet l'usage d'une reconfiguration devrait être exceptionnelle. Chaque fois que vous allez faire baisser la mémoire SQL Server, vous allez devoir faire ,dégager en urgence une quantité impressionnante de page en cache et de ce fait, si des données nécessaires à une requête ont été sorties, il va falloir bouffer du disque pour les y remettre.

Il est probable que votre réplication s'attendait à trouver les données en cache et comme elles sont dégagées il y a chevauchement des process du fait des temps d'attente.

sp_configure doit être faite hors production !

Je serais curieux de voir les temps de sys.dm_os_wait_stats...


A +

Dans le sens ou la machine entière est optimisée pour SSAS et qu'il n'y a "normalement" aucune requête SQL en dehors de la réplication et du calcul du cube (de 1h00 à 5h00) le temps de "warmup" de sql est peu important.

[SQLpro]

Dans le sens ou la machine entière est optimisée pour SSAS et qu'il n'y a "normalement" aucune requête SQL en dehors de la réplication et du calcul du cube (de 1h00 à 5h00) le temps de "warmup" de sql est peu important.

Encore une fois ce n'est pas une pratique recommandée.
Vos valeurs de configurations doivent être stable et le passage de paramètres de configuration devrait être fait en dehors de toute activité, réplication comprise.

A +