Discussion :

[StringBuilder]

Bonjour,

J'utilise une application éditeur (donc pas la main sur ce qu'elle fait, ni le détail de ce qu'elle fait vraiment).

Sur deux machines (une de DEV et une de TEST) j'ai SQL Server 2014 Express.

Chacune des machines a 10 Go de mémoire. La DEV a 4 CPU, la TEST n'en a que deux.
Ce sont des VM hébergées sous KVM.

Les serveurs ne sont pas dédiés, à savoir que d'autres applications que SQL Server tournent dessus (IIS et module "Win" de l'application).

SQL Server Express étant fortement limité en capacité RAM et CPU, je doute très fortement que cela ait le moindre impact que d'autres applications tournent en même temps, d'autant que le gros goulot d'étranglement, selon moi, devrait être la baie de disque, qui est de toute façon commune pour toutes les VM (il y en a des dizaines, on est dans un datacenter).

Bref. On lance un programme d'import de données.
Classique : lecture d'un fichier CSV, vérification de règles de gestion, insertion de données/mises à jour dans la base.

En soit, la technologie utilisée par ce module d'import est merdique et contre-performante (ligne à ligne, triggers logiciels, etc.)

Le module d'import utilise entre 5 et 10% de CPU.
C'est lui qui fait le plus d'accès disques pour alimenter un fichier de log.

Ensuite, vient SQL Server, qui utilise entre 60 et 70% de CPU, et autant d'accès disque pour alimenter le journal des transactions.

Ce que je ne m'explique pas, c'est cette utilisation CPU de SQL Server : qu'est-ce qu'il fabrique bon dieu ?

Et au final, ça met 3 heures pour importer 60 000 lignes !

Chez d'autres clients, avec une configuration similaire (y compris sur une VM hébergée sur mon ordinateur portable bien moins performant) j'insère le même nombre de données (avec des traitements similaires et une base de taille similaire) en moins de 5 minutes.

Qu'est-ce qui peut expliquer une telle lenteur ?

Si je fait un copier/coller d'un fichier sur le disque, ça tourne à 400 Mo/s. Donc c'est pas un souci de disque (enfin, je pense pas). Pas de notion de réseau puisqu'on est 100% en local.

Occupation de la RAM à 50% au total.
La base fait 350 Mo (une broutille quoi...)

Bref, je sèche.

[StringBuilder]

Je viens de me rendre compte d'une chose étrange...

Par défaut, SQL Server ne réorganise pas automatiquement les index quand ils sont trop framentés ?

En effet, je viens de lancer une requête de contrôle de la fragmentation des index, et j'ai une centaine d'index avec une fragmentation suppérieure à 90% !

Du coup j'imagine que si je corrige ça, ça devrait aller mieux... Mais j'aimerais comprendre comment c'est possible ? Par défaut, il ne réorganise pas tout seul ???

[StringBuilder]

Plus ça va, plus je pige rien...

Je lance cette requête, et j'exécute les commandes générées :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT avg_fragmentation_in_percent, 'ALTER INDEX ' + b.name + ' ON ' + c.name + ' REORGANIZE;'
FROM sys.dm_db_index_physical_stats (DB_ID(N'CRM'), NULL, NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
	join sys.tables as c on c.object_id = a.object_id
	where avg_fragmentation_in_percent between 5 and 30 and b.name is not null and c.name is not null
union all
SELECT avg_fragmentation_in_percent, 'ALTER INDEX ' + b.name + ' ON ' + c.name + ' REBUILD;'
FROM sys.dm_db_index_physical_stats (DB_ID(N'CRM'), NULL, NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
	join sys.tables as c on c.object_id = a.object_id
	where avg_fragmentation_in_percent > 30 and b.name is not null and c.name is not null
order by avg_fragmentation_in_percent desc
;

L'ordre des lignes change d'une exécution à l'autre, mais le taux de fragmentation reste très élevé sur la plupart des tables !
J'ai pas de message d'erreur... (???)

[Invité]

Si tes tables sont de petites tailles, c'est sûr que tes indexes n'atteindront jamais 100 %.
Il faudrait rajouter un critère sur ta requête pour prendre les tables de plus de 10000 lignes, par exemple.

[mikedavem]

Je dirais plutôt 1000 ou 10000 pages à la rigueur mais je pense que c'était une erreur ...

Autre chose tu peux aussi enlever les unités d'allocations de type LOB si tu en as pour la partie REBUILD. La reconstruction d'index n'opère pas sur les LOBS dans ton cas et le fait de les inclure ne ferait que fausser tes résultats de récupération de fragmentation.

Code :

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

1
2
3
4
5
6
7
8
SELECT avg_fragmentation_in_percent, 'ALTER INDEX ' + b.name + ' ON ' + c.name + ' REBUILD;'
FROM sys.dm_db_index_physical_stats (DB_ID(N'CRM'), NULL, NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
	join sys.tables as c on c.object_id = a.object_id
	where avg_fragmentation_in_percent > 30 and b.name is not null and c.name is not null
            and page_count > 1000 
                 and alloc_unit_type_desc  <> 'LOB'
order by avg_fragmentation_in_percent desc

[Invité]

Je dirais plutôt 1000 ou 10000 pages à la rigueur mais je pense que c'était une erreur ...

Disons que j'ai pris un raccourci.

[mikedavem]

Disons que j'ai pris un raccourci.

[StringBuilder]

Je vous remercie pour vos réponses

Sinon, j'ai constaté aussi un autre problème...

Autant sur la PROD, c'est moi qui ai fait l'installation, et j'ai donc fait attention à ça dès la création de la base... Autant sur la DEV et TEST, ça a été mal paramétré dès le départ : les bases ont été créées avec une taille de 10 Mo avec une croissance auto de 1 Mo. Idem pour le log, 1 Mo avec une croissance de 10%...

Résultat, les fichiers étaient arrivés à une taille de 600 Mo, évidement avec autant de fragments...

J'ai forcé la taille de la base à 2048 Mo et du log à 1024 Mo, avec croissance auto à grand coups de 1024 Mo tous les deux (y'a peu de chance que ça n'arrive avant un moment).

Sauf que ça le réduit pas les fragments existants. Une solution autre que :
- Couper le programme
- Démonter la base
- La zipper (ou la déplacer sur un autre disque)
- Puis la remettre à sa place

Afin de réduire la fragmentation physique ?

J'ai pensé aussi à créer un nouveau fichier et déplacer les tables dans le nouveau, mais n'ayant pas la main sur le code de l'application, qui fait des CREATE/DROP/ALTER de tables à tout va, j'ai peu qu'une référence en dur soit faite au fichier d'origine, et que ça déconne si je change les tables de fichier...

Et à l'intérieur des fichiers, est-ce que ça a un impact qu'elle soit fragmentée physiquement ?

[mikedavem]

Afin de réduire la fragmentation physique ?

Quel type de stockage pour ton SQL Server? Disques locaux, SAN ?

++

[StringBuilder]

C'est un NAS.
Après, j'ai pas le détail.

En fait, on accumule les ennuis sur ces serveurs.
Plus ça va, et plus on ne sais plus où chercher.

La piste SQL Server, j'y crois pas vraiment. La base est ridiculement petite par rapport à ce que peut gérer SQL Server, donc même sans paramétrage aux petits oignons, je vois pas comment SQL Server, dans de bonnes conditions, pourrait être aussi lent.

Et on a d'autres clients, hébergés sur leurs propres serveurs, avec des caractéristiques moindres, une volumétrie similaire, et qui ont des temps de réponse de 10 à 100 fois plus rapides : un import de plusieurs centaines de milliers de lignes ne dure que quelques minutes (moins de 10) chez un client, alors que sur notre serveur, on met 3 heures à importer 17 000 lignes... Dans une base plus petite, avec 2 fois plus de CPU et 4 fois plus de RAM...

Et vu que certaines opérations basiques, totalement hors SQL Server sont lentes, j'ai l'impression qu'on a surtout un souci avec la machine hôte...