Discussion :

[joujousagem2006]

Bonjour a tous

j'ai eu ce matin a 10 h une grand dégradation de performance sur mon serveur SGBD Sql server
aprés avoir analyser tout sans avoir de résultat et suite au pression faite par la production mon supérieur hiérarchie ma demander de faire une restauration de la base qui pose de question sur le même serveur avec un notre nom
après avoir faire du restauration de cette base avec un non différent le problème a était disparut sur l'original base (on a pas changer la chaîne de connexion sur la nouvelle base restaurer)
pour info le dernier sauvegarde a été passer correctement ce matin
en tant que je Suis DBA la direction ma demandé une explication de ce problème mais j'arrive pas a comprendre ce qui a était passer
pour info la base est en mode de récupération simple + sql server 2005 64 bit SP4

j'ai eu ce message sur sql après la restauration "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"

aider moi svp

[rudib]

Bonjour,

C'est très difficile (pardon ,je veux dire probablement impossible) de répondre précisément à cette question après coup. Il faut résister à la pression de ton manager et analyser le problème quand il se produit.
Le message que tu cites est normal : après avoir fait une restauration de base, le cache de plans a été vidé. Si cela va mieux après, c'est peut-être parce qu'il y avait de mauvais plans d'exécution en cache, et qu'ils étaient utilisés.

Ce que tu peux faire maintenant : si les lenteurs étaient perceptibles sur une utilisation spécifique de la base, tu peux essayer de trouver quel est le code qui est lancé, et si par exemple il s'agit d'une procédure stockée, la surveiller pour voir si cela vient d'un problème de compilation de la procédure.

Tu peux aussi voir dans le journal d'erreur de SQL Server si tu as d'autres messages qui te donnent des indications. Poste en tout cas tous les éléments que tu peux trouver ici, pour qu'on puisse t'aider.

Mais surtout, ce que tu dois faire maintenant est commencer à surveiller (superviser) ton serveur pour détecter ce genre de cas au moment où ils vont se produire. Avec Frédéric Brouard (SQLPro), nous sommes en train de préparer un service de supervision à distance pour nos clients, si ça peut t'intéresser envoie moi un message privé je te tiendrai au courant.

[joujousagem2006]

ok MERCI POUR VOTRE Réponse
par comptre je veux insister sur votre phrase "le cache de plans a été vidé" qui est vraiment mon problème a chaque fois il y a eu de redémarrage de service MSSQL ou par exemple comme cette cas restauration de database sur le serveur j'aurai de dégradation de performance et a chaque Foix le problème se résolu en faisant une requête update stats with full scan sur la table le plus volumineux surtout au moment ou le requête attaque l'index cluster de primary Key
SVP SVP je n'ai mare du pression faite par le production et je veux conserver ses statistiques??

[rudib]

Peux-tu poster ici la structure de la table la plus volumineuse dont tu parles : Script de création de la table et des index

[joujousagem2006]

Peux-tu poster ici la structure de la table la plus volumineuse dont tu parles : Script de création de la table et des index

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
USE [suivi]
GO
 
/****** Object:  Table [dbo].[mvtFabr]    Script Date: 05/20/2014 08:22:22 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[mvtFabr](
	[pt_num] [varchar](18) NOT NULL,
	[mf_dmvtdeb] [datetime] NOT NULL,
	[ni_num] [varchar](23) NOT NULL,
	[mf_trans] [varchar](25) NULL,
	[pr_num] [int] NULL,
	[pl_badge] [int] NULL,
	[mf_statut] [varchar](5) NULL,
	[mf_dmvtfin] [datetime] NULL,
	[mf_duree] [float] NULL,
	[mf_obs] [varchar](78) NULL,
	[mf_seq] [int] NULL,
	[of_num] [varchar](8) NULL,
	[ge_phase] [int] NULL,
	[mf_qte] [real] NULL,
	[qc_code] [varchar](10) NULL,
	[qd_id] [int] NULL,
PRIMARY KEY NONCLUSTERED 
(
	[pt_num] ASC,
	[mf_dmvtdeb] ASC,
	[ni_num] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
)
 
GO
 
SET ANSI_PADDING OFF
GO

pour les index

Code :

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

1
2
3
4
5
6
7
8
CREATE NONCLUSTERED INDEX [missing_index_11001] ON [dbo].[mvtFabr] 
(
	[pt_num] ASC,
	[mf_trans] ASC,
	[mf_dmvtdeb] ASC
)
INCLUDE ( [ni_num]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
GO

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
USE [suivi]
GO
 
/****** Object:  Index [IXN_1755]    Script Date: 05/20/2014 08:24:33 ******/
CREATE NONCLUSTERED INDEX [IXN_1755] ON [dbo].[mvtFabr] 
(
	[mf_trans] ASC
)
INCLUDE ( [pt_num],
[mf_dmvtdeb],
[ni_num],
[pr_num],
[pl_badge],
[mf_statut],
[mf_dmvtfin],
[mf_duree],
[mf_obs],
[mf_seq],
[of_num],
[ge_phase],
[mf_qte],
[qc_code],
[qd_id]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
GO

Code :

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

1
2
3
4
5
6
7
8
9
10
USE [suivi]
GO
 
/****** Object:  Index [idx_Mvtfabr_ni]    Script Date: 05/20/2014 08:24:53 ******/
CREATE NONCLUSTERED INDEX [idx_Mvtfabr_ni] ON [dbo].[mvtFabr] 
(
	[ni_num] ASC,
	[mf_dmvtdeb] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
GO

Code :

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

1
2
3
4
5
6
7
8
9
USE [suivi]
GO
 
/****** Object:  Index [idx_Mvtfabr_dmvtdeb]    Script Date: 05/20/2014 08:25:07 ******/
CREATE CLUSTERED INDEX [idx_Mvtfabr_dmvtdeb] ON [dbo].[mvtFabr] 
(
	[mf_dmvtdeb] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
GO

[rudib]

Intéressant, information précise, merci. Encore une chose, peux-tu poster le résultat de ces deux requêtes ?

Code :

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

1
2
3
4
5
6
SELECT TOP 10 * FROM dbo.mvtFabr;
GO
 
SELECT i.name, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count, ps.avg_fragmentation_in_percent, ps.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('mvtFabr'), NULL, NULL, NULL) ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id;

[joujousagem2006]

Intéressant, information précise, merci. Encore une chose, peux-tu poster le résultat de ces deux requêtes ?

Code :

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

1
2
3
4
5
6
SELECT TOP 10 * FROM dbo.mvtFabr;
GO
 
SELECT i.name, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count, ps.avg_fragmentation_in_percent, ps.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('mvtFabr'), NULL, NULL, NULL) ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id;

J'ai lancer la requête elle a tourné beaucoup sur le serveur et suite qu'on est en production j'ai annuler afin que je reçois pas de nouveau réclamation

Est ce que tu peux m'aider sur cette point dans quel cas je reçois ce message

Code :

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

 SQL Server a rencontré %d occurrence(s) de vidages de mémoire cache pour la mémoire cache '%s' (partie du cache du plan) en raison d'opérations de maintenance de base de données ou de reconfiguration

et pourquoi mon serveur ne le recréé pas automatiquement pourquoi a chaque fois je doit lancer un update des statistiques sur ce table ???

[rudib]

Je pense que la structure de ta table n'est vraiment pas idéale, et c'est pour ça qu'il serait utile d'avoir les résultats de la requête. Quelle est la volumétrie de ta table (combien y a-t-il de lignes ?). La colonne mf_dmvtdeb contient-elle des dates toujours incrémentales ? que donne un

Code :

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

SELECT TOP 10 * FROM dbo.mvtFabr;

?

[mikedavem]

Est ce que tu peux m'aider sur cette point dans quel cas je reçois ce message

SQL Server a rencontré %d occurrence(s) de vidages de mémoire cache pour la mémoire cache '%s' (partie du cache du plan) en raison d'opérations de maintenance de base de données ou de reconfigurationet pourquoi mon serveur ne le recréé pas automatiquement pourquoi a chaque fois je doit lancer un update des statistiques sur ce table ???

Visiblement l'analyse de fragmentation de ton index génère une pression mémoire sur ton serveur. Question bête mais as-tu bien lancé la requête que t'a donné Rudib dans le contexte de la base de données qui te concerne ? Le cas échéant tu risques de remonter tous les indexes de toutes les bases de données en mémoire. Avant de lancer la requête vérifie que DB_ID() et OBJECT_ID('mvtFabr') ne te retournent pas de résultat NULL.

SQL Server ne met à jour les statistiques qu'en fonction d'un seuil de modification de données supérieure à 20% du total de lignes dans ta table + 500 lignes. Par défaut, plus tu as de lignes dans ta table moins tu auras de mise à jour de statistiques. Si tu as des dates incrémentales, il y a de grandes chances qu'elles ne soient pas reflétées dans les statistiques de l'index concerné tant que l'update statistics n'a pas lieu pour l'index. Je pense que les questions de Rudib vont dans ce sens donc il est important que tu fournisses les informations qu'il t'a demandé.

++