Index filtrés SQL Server pour files de travail et données actives

Une table de travail peut contenir des millions de tâches terminées alors que les processus cherchent seulement quelques centaines de tâches en attente. Un index complet accélère parfois cette recherche, mais conserve aussi toute l'histoire. Un index filtré représente directement la petite partie utile à la requête active.

La taille pertinente est celle de l'ensemble actif. Cinquante millions de lignes dont 200 restent à traiter ne ressemblent pas à vingt millions de tâches en attente après une panne. Étudiez fonctionnement normal et reprise. Le comportement d'un index presque vide ne suffit pas à prévoir les plans lorsque le retard remplit la table.

Reproduire exactement le prédicat métier

Dans l'exemple, zéro signifie en attente et un signifie terminé. L'index trie uniquement les tâches ouvertes par date puis identifiant. CustomerId est inclus pour servir la réponse, sans participer à la recherche ni au tri. Les résultats attendus sont 2 et 3. Ce petit jeu valide la logique, pas un gain de performance mesurable.

CREATE TABLE #Work
(
    WorkId bigint NOT NULL PRIMARY KEY,
    Status tinyint NOT NULL,
    CreatedAt datetime2(0) NOT NULL,
    CustomerId int NOT NULL
);
INSERT #Work VALUES
(1,1,'2024-01-01',10),(2,0,'2024-01-02',20),
(3,0,'2024-01-03',10),(4,1,'2024-01-04',30);

CREATE INDEX IX_Work_Pending
ON #Work(CreatedAt, WorkId)
INCLUDE(CustomerId)
WHERE Status = 0;

SELECT TOP (20) WorkId, CreatedAt, CustomerId
FROM #Work
WHERE Status = 0
ORDER BY CreatedAt, WorkId;

SELECT i.name, p.row_count, p.used_page_count
FROM tempdb.sys.indexes AS i
JOIN tempdb.sys.dm_db_partition_stats AS p
  ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE i.object_id=OBJECT_ID('tempdb..#Work');

DROP TABLE #Work;

Sur des données réelles, comparez pages d'index, lectures logiques et lignes parcourues avec des files normales et saturées. Un index sur le seul état peut laisser un tri ou de nombreux accès à la table. Placez les colonnes d'ordre dans la clé et limitez les colonnes incluses. Un gros document JSON peut être récupéré après avoir identifié une petite liste d'identifiants.

Les options SET sont également importantes. Les connexions doivent respecter les exigences des index filtrés, notamment ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL et ARITHABORT activés, avec NUMERIC_ROUNDABORT désactivé. Si les écritures échouent depuis l'application alors que le test administrateur fonctionne, comparez les paramètres des connexions.

Comprendre les refus de l'optimiseur

L'optimiseur doit garantir que l'index contient toutes les lignes possibles du résultat. Une requête réutilisable avec Status = @Status ne peut généralement pas dépendre d'un index limité à Status = 0: le même plan pourrait servir ensuite aux tâches terminées. Tester une seule fois avec zéro ne rend pas ce plan sûr pour chaque valeur.

Une requête spécifique aux tâches en attente, avec un prédicat littéral, constitue souvent la solution la plus lisible. Une recompilation de l'instruction peut aussi convenir si son coût est acceptable. Évitez de commencer par forcer l'index. Un indice incompatible avec le filtre peut empêcher la génération du plan. Vérifiez le comportement avec la vraie paramétrisation de l'application, y compris celle imposée au niveau de la base.

Les statistiques filtrées décrivent le sous-ensemble et peuvent améliorer les estimations. Elles nécessitent toutefois une surveillance lorsque la population active change rapidement. Comparez lignes estimées et réelles, ainsi que les transitions d'état. Les statistiques globales ne décrivent pas automatiquement les tâches ouvertes. Pour un filtre IS NULL, vérifiez également si la colonne filtrée doit être incluse pour obtenir le chemin d'accès attendu.

Distinguer recherche rapide et réservation correcte

Passer une tâche à l'état terminé retire son entrée de l'index. La rouvrir réinsère cette entrée. La taille historique diminue, mais chaque transition peut encore générer des écritures. Mesurez la latence et les contentions lorsque plusieurs processus modifient les premières tâches simultanément.

Le SELECT présenté ne réserve rien. Deux processus peuvent lire les mêmes identifiants avant qu'un état ne change. Une vraie file nécessite une réservation transactionnelle, un propriétaire ou une durée de bail, des reprises et une règle pour les travailleurs interrompus. READPAST et les verrous de mise à jour doivent être étudiés avec le niveau d'isolation retenu.

Examinez le retard après incident, les tâches irrécupérables qui restent en tête et la vitesse de sortie de l'ensemble actif. La question opérationnelle est de savoir si la file se vide sans monopoliser la base. L'index doit rendre la bonne partie des données peu coûteuse à trouver, tout en laissant explicites réservation, reprise et conservation.

Références techniques: Microsoft Learn: Filtered indexes · Microsoft Learn: Index design.