updates !

Version imprimable

05/10/2009, 13h09
Zep3k!GnO

CPU --> 100% sur des inserts / updates !

Bonjour à tous,

voilà je vous expose mon problème :

Lors de multiple insert/update dans ma BDD ( genre 1700 instructions ), il arrive qu'SQL Server fasse monter a 100% l'utilisation du CPU et celle ci y reste bloquée, donc ça met mon site en javel...

Pour débloquer la situation, souvent j'essaie de relancer le serv, de killer des requêtes qui restent en "awaiking command", mais je n'ai jamais trouvé exactement la marche a suivre pour qu'il se remmettre à tous les coups à un taux CPU "normal"...

Donc si vous savez ou avez une idée sur ce qui fait que mes insert ou updates font monter et bloque le CPU a 100% ...

N'hésitez pas a me poser des question, je ne suis pas un très grand habitué de SQL SERVER je suis à la base plutot MySQL donc j'ai p'tet oublié de vous dire des choses...

Merci à vous.
05/10/2009, 13h45
Pol63

combien de connexions à la seconde ?

et puis sans voir les requetes et les tables on ne peut pas dire grand chose je pense
05/10/2009, 14h02
SQLpro

A mon avis vous devez avoir des splits de page dans les index => mauvais index, fill factor pas maîtrisé...

Donnez nous le DDL des tables et index en jeu ainsi que des exemples de requêtes d'insertion.

A +

Bonjour et merci pour vos réponses !

Nom de l'index : PK_DataFeedExcludeProductList
Voici un screen de la fragmentation : http://img17.imageshack.us/img17/804...gmentation.jpg

Pour ce qui est des requêtes utilisées :
Code:

1 2 3 4 5 INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0001'); INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0002'); INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0003'); ... INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','1700');
J'espère que c'est bien tout ça que tu voulais !

Merci.

Zep3k!GnO

05/10/2009, 15h06
mikedavem

Bonjour,

Postez vos DDL de tables et indexes (CREATE TABLE ... CREATE INDEX etc ...) comme vous le suggère SQLPro.

A première vue (en regardant votre image) je dirais qu'il est probable que vous ayez un mauvais fillfactor pour votre index PK_DataFeedExcludeProductList et vous devez certainement engendrer pas mal de split (donc de I/O) lors de vos insertions de données.

Vous pouvez également le voir en posant certains compteurs de performances comme %CPU , %CPU Kernel, %CPU User ,% disque , % ecriture disque , File d'attente disque ainsi que SQL Server: Access method : Page split / s au moment de vos insertions

++

Ok dsl pour les DDL je ne connaissais pas le terme :S

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
28
29
USE [eStore]
GO
/****** Objet*:  Table [dbo].[DataFeedExcludeProductList]    Date de génération du script*: 10/05/2009 15:17:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataFeedExcludeProductList](
	[FeedId] [int] NOT NULL,
	[ProductId] [int] NOT NULL,
 CONSTRAINT [PK_DataFeedExcludeProductList] PRIMARY KEY CLUSTERED 
(
	[FeedId] ASC,
	[ProductId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[DataFeedExcludeProductList]  WITH CHECK ADD  CONSTRAINT [FK_DataFeedExcludeProductList_DataFeeds] FOREIGN KEY([FeedId])
REFERENCES [dbo].[DataFeeds] ([FeedId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DataFeedExcludeProductList] CHECK CONSTRAINT [FK_DataFeedExcludeProductList_DataFeeds]
GO
ALTER TABLE [dbo].[DataFeedExcludeProductList]  WITH CHECK ADD  CONSTRAINT [FK_DataFeedExcludeProductList_Products] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Products] ([ProductId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DataFeedExcludeProductList] CHECK CONSTRAINT [FK_DataFeedExcludeProductList_Products]

Merci pour vos réponses !

Remplace

Code:

1
2
3
4
5
 
INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0001');
INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0002');
INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0003');
...

par

Code:

1
2
3
4
5
6
7
8
 
INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) 
SELECT '47','0001'
UNION
SELECT '47','0002'
UNION
SELECT '47','0003'
...

Tu pourrais gagner énormément de rapidité.

05/10/2009, 16h15
Zep3k!GnO

Merci, ça marche j'essaierai !

Mais mon problème ne doit pas venir de la car des fois je fais la même chose et ça ne pose aucun problème...

Mais je note la syntaxe ;)

Zep3k!GnO

Citation:
Envoyé par Sergejack
Remplace
Code:

1 2 3 4 5 INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0001'); INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0002'); INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0003'); ...
par
Code:

1 2 3 4 5 6 7 8 INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) SELECT '47','0001' UNION SELECT '47','0002' UNION SELECT '47','0003' ...
Tu pourrais gagner énormément de rapidité.
ah oui ?? j'y avais pas pensé
faudra que je test à l'occasion aussi ...

@Zep3k!GnO
si tu as des infos de temps d'execution avant et après ca, je veux bien voir ce que ca donne

05/10/2009, 18h09
Zep3k!GnO

Au fait, je n'ai pas précisé mais j'ai le même problème lorsque je reboot mon SQL SERVER !

Je viens de faire des compteurs comme suggéré mais je n'ai pas vu le "fractionnement des pages / s " exploser ...

@Pol63 : Pour ce qui est des temps d'éxécution je testerai ça ... quand ça ne plantera plus trop de façon aléatoire car on fait du e-commerce et bloquer le site c'est pas super top ... :)

Zep3k!GnO
05/10/2009, 18h57
Pol63

et votre serveur il est conséquent ?
et t'as pas une idée du nombre de requetes qui passent ?
05/10/2009, 19h26
elsuket

Bonjour,

Les remarques de SQLPro et de Mikedavem ne sont pas à négliger : dans le DDL de vos tables, difficile de vous aider ...
Si vous faites des insertions perpétuelles dans cette table, il serait peut-être bon de baisser le FILLFACTOR de l'index de sorte à entraîner un minimum de splits de page.
Il peut s'agir de tout autre problème d'optimisation (schéma de données, requêtes, indexation, ...).
Bref, la source de votre problème reste ouverte ;)

@++ ;)
06/10/2009, 00h38
SQLpro

Vous avez 169 pages là ou 75 pages suffirait (les pages sont de 8 000 octets et vous avez 39765 lignes de 15 octets). La fragmentation est assez monstrueuse, ce qui signifie :
1) que vous ne défragmentez jamais
2) que vous n'avez pas mis en place de fill factor

Bref, défragmentez en plaçant un FILL FACTOR à 80 % et vous verrez la différence !
En sus, prétaillez votre fichier de JT et vos fichiers de données pour éviter les opérations de croissance

En principe la défrag, c'est comme la sauvegarde : tous les jours !

A +
06/10/2009, 16h03
Zep3k!GnO

Bonjour à tous,

Merci pour tous vos conseils ! !

@Pol63 : désolé mais la je ne peux pas trop te dire...c'est un site développé par des prestas et je n'ai pas pu mettre le nez dans le code et j'avoue que je n'ai pas sniffé toutes les requêtes...

@SQLpro : Ok je vais tester ça ! Je vous dirai si le problème persiste.

Encore un grand merci à vous pour vos contributions, je ne manquerai pas de vous dire si mon problème a été résolu ! Ca fait plaisir de pas se sentir tout seul...

A toute'

Zep3k!GnO
06/10/2009, 18h53
Zep3k!GnO
Bon, je viens de re-fragmenter ma table avec :
Code:

1 2 3 4 5 6 7 ALTER INDEX MON_INDEX ON MA_BDD.NOM_TABLE REBUILD WITH ( FILLFACTOR = 80, ONLINE = OFF )
J'ai du mettre ONLINE à OFF car nous n'avons pas la version entreprise :/

Ce qui est étrange c'est que ç a fait monter mon processeur à 100% et a mis mon serveur en javel...

Par contre une fois le serveur remis sur pied, il est vrai que le taux de fragmentation et tout le reste on bien diminué.
(j'ai pris les requêtes sur http://www.simple-talk.com/sql/datab...2005-and-2008/ qui me semblait bien )

Mais au final le problème se pose toujours lorsqu'on refait les inserts "de masse" :/

Ai-je malfait/oublié quelque chose ? ?

Merci pour l'coup de main !

Zep3k!GnO
07/10/2009, 15h07
SQLpro

Cela peut aussi être normal si :
1) fichier en auto grow
2) nombreux index...

A +
07/10/2009, 15h16
mikedavem

... Et éventuellement un auto_shrink sur la base ... j'ai déjà le cas ;)

++
07/10/2009, 15h25
Sergejack

As tu essayé ma suggestion Zep3k!GnO ?

Citation:
Envoyé par Sergejack
Remplace
Code:

1 2 3 4 5 INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0001'); INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0002'); INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) VALUES ('47','0003'); ...
par
Code:

1 2 3 4 5 6 7 8 INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) SELECT '47','0001' UNION SELECT '47','0002' UNION SELECT '47','0003' ...
Tu pourrais gagner énormément de rapidité.
Encore plus simple :
Code:

1 2 INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) ( '47','0001'), ('47','0002'), ('47','0003')...
De plus si FeedId et ProductId sont des nombres et non des chaines de caractères, alors spécifiez les valeurs à titre de nombre dons san les entourer d'apostrophe. Cela évitera un couteux transtypage...

A +

Citation:
Envoyé par SQLPro
Code:

1 2 INSERT INTO [eStore].[dbo].[DataFeedExcludeProductList] ([FeedId] ,[ProductId]) ( '47','0001'), ('47','0002'), ('47','0003')...
Possible si Zep3k!GnO utilise SQL Server 2008 ;)

@++ ;)

Fuseau horaire GMT +2. Il est actuellement 10h11.

Partenaire : Hébergement Web