Problème de performance sys.dm_db_index_physical_stats

Bonjour,

Je vous écris ce message car j'ai des problèmes de performance sur une requête.

Contexte : Grosse DB de plusieurs TO
Shelduled Jobs : Rebuild des index si une table est trop fragmentée (plus de 12%)
SELECT utilisé en entrée :

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT object_id, index_id, partition_number, avg_fragmentation_in_percent INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 12.0 AND index_id > 0;

---

Seulement voilà, en général ce select met une 20aine de secondes à donner un résultat mais parfois il faut plusieurs heures!

Je ne comprends pas ce qui pose ces problèmes de performances... Quand j'exécute le SELECT il peut ne pas me donner de résultat au point de devoir killer le select, si je relance quelques minutes après il me sort le résultat en quelques secondes. Tout ça en désactivant les autres requêtes susceptible de tourner, rien d'autre ne tourne sur le server.

Le soucis lors de l'exécution normale de cette tâche Schedulée est que si elle prend trop de temps, elle bloque alors un/des autre(s) processus Schedulés quelques heures après... Au point que les processus se bloquent entre-eux et un kill manuel est obligatoire...

J'ai donc plusieurs questions :
1. Quelle pourrait-être la cause de cette différence de temps entre 2 exécutions?
2. Est-ce qu'on peut améliorer la performance de cette query? (En tout cas s'assurer qu'elle prenne toujours autant de temps)
3. Sinon, existe-t-il une option à ajouter à la query pour ne pas qu'elle bloque l'exécution des suivantes?
4. D'autres idées? ^^

Citation:

---

Microsoft SQLServer 2012
Microsoft SQL Server Management Studio 11.0.3128.0
Microsoft Analysis Services Client Tools 11.0.3128.0
Microsoft Data Access Components (MDAC) 6.2.9200.16384
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.10.9200.16635
Microsoft .NET Framework 4.0.30319.18051
Operating System 6.2.9200

---

Merci.
Cordialement.

La fonction sys.dm_db_index_physical_stats nécessite un verrou de table IS (Intent-Shared), et ce, quel que soit son mode d'exécution (LIMITED, SAMPLED ou DETAILED).

Le verrou IS (Intent-Shared) est par essence incompatible avec les verrous de type X (Exclusive)

Si pour une raison ou une autre un verrou Exlusif (X) est posé sur une des table ( exemple un SELECT avec l'indicateur with (TABLOCKX) effectué à l'intérieur d'une très très longue transaction, ayant pour niveau d'isolation REPEATABLE READ, ou SERIALIZABLE), vous pouvez attendre très très longtemps ! (des heures !)

- Essayez dans un premier temps de limiter l'étendu du résultat de la fonction sys.dm_db_index_physical_stats. Pour cela, essayez de traiter les tables une par une (voire même les indexes un par un), et ce en précisant le deuxième paramètre (Object_id de la table) voire même le troisième (object_id de l'index).
Exemple :

Code:

---

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.MaTable') , NULL, NULL , 'LIMITED');

---

- Rajoutez au besoin un SET LOCK_TIMEOUT avant et après l'instruction

Code:

---

1 2 3 4 5 6 7 8

SET LOCK_TIMEOUT nnnnnn -- nnnnn exprimé en millisendes GO SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.T_CLIENT_CLI') , NULL, NULL , 'LIMITED') ... GO -- revenir à la valur par défaut SET LOCK_TIMEOUT -1 -- valeur par défaut c.à.d attente infinie ! GO

---

A+

C'est normal, pour avoir les statistiques il faut aller lire toutes les pages. Pour cela il faut qu'elles soient en mémoire et libre d'accès. Des verrous sont donc posés, même de manière transitoire. Essayez faire une lecture sale (SET TRANSACTION READ UNCOMMITTED ou NOLOCK).

A +

Bonjour,

Merci pour vos solutions rapides et précises.
Je vais essayer cette lecture "sale" pendant la phase de développement de la solution plus propre du parcours d'indexes isolés (nécessite une procédure de lecture différente).

J'éditerai ce message pour en donner le résultat, mais je pense que ces solutions permettent de mettre ce sujet à résolu. ;)