Pages d’accès aux données et requêtes lentes

de compteurs, suivis de lignes
avec des tampons horodateurs et des
données de comptage.
Pour représenter ces données sous
forme graphique, une certaine manipulation
s’impose. Ainsi, nous avons
constaté l’impossibilité de créer des requêtes
efficaces qui traitent des données
directement des tables
CounterData et CounterDetails. L’exécution
de telles requêtes est lente et inefficace
pour plusieurs raisons, y compris
le manque d’index. (Pour plus
d’informations sur la manière dont
nous traitons ce problème, voir l’encadré
« Améliorer les performances de
requêtes ».) Pour être précis, le champ
CounterDateTime est un champ char
(24) non indexé, ce qui complique l’extraction
de données d’une plage de
dates.
En outre, pour manipuler Counter-
DateTime comme un champ datetime,
il faut tronquer le caractère ASCII
caché à  la fin du compteur datetime
avant de le convertir en un type de
donnée datetime pour éviter les erreurs de conversion. Tout d’abord,
nous avons créé une vue pour effectuer
la manipulation de données nécessaire.
Mais nous avons constaté que
le temps d’exécution de la vue était
trop long à  cause des tables sources
non indexées, de la conversion datetime,
et de la simple quantité des données
renvoyées. Pour accélérer l’extraction
des données, nous aurions aimé
que System Monitor crée des tables de
journalisation avec les index de clés
étrangères appropriés, crée des index
sur les colonnes qui sont utilisées
comme critères WHERE possibles, et
stocke l’information de date et d’heure
en format datetime plutôt qu’en format
caractère. Mais tout cela aurait
peut-être ralenti sensiblement les insertions
dans les tables de journalisation.
Une fois encore, nous vous
conseillons de ne pas modifier les
tables de journalisation que System
Monitor crée, particulièrement si vous
voulez bénéficier de l’assistance
Microsoft. Pour contourner ces lacunes
et insuffisances, nous avons créé
une procédure stockée appelée
ConvertCounterData – que montre le
listing 1 – dans la base de données
DBADMIN pour transporter les données
en format épuré vers une autre
table appelée MyCounterData. Le listing
2 montre le code qui crée la table
MyCounterData.
ConvertCounterData crée une
table dérivée pour placer les données
des compteurs de performances dans
des colonnes. Ensuite, la procédure
agrège les données en utilisant la clause GROUP BY. Pendant l’agrégation,
la procédure convertit les données flottantes
en numérique (18,2) pour arrondir
les décimales à  2, pour une visualisation
plus facile. Observez les variables
heure, minute et seconde, que nous utilisons
dans nos pages d’accès aux données
pour approfondir graphiquement
nos données par heure, minute et seconde.
Une page d’accès aux données
classique utilise les tables de faits heure,
minute et seconde, mais ces tables ne
sont pas compatibles avec le modèle de
page d’accès aux données à  trois dimensions
que nous utilisons. Dans une
page d’accès aux données classique, les
heures, minutes et secondes sont dupliquées
pour chaque jour que vous visualisez.
Donc, un graphique de 2 jours
montrerait 48 heures dans une ligne sur
l’axe X et un graphique de 3 jours montrerait
72 heures, y compris les minutes
et les secondes, au lieu de représenter
tous les jours dans le graphique en utilisant
une barre de temps de 24 heures
courante. Vous pouvez contourner ce
comportement indésirable en ajoutant
les variables heures, minutes et secondes
que montre le listing 2. De plus,
la procédure utilise la variable
RecordIndex pour vérifier qu’elle n’importe
que de nouvelles données dans
MyCounterData en provenance de
CounterData. Sachez que nos compteurs
pourraient ne pas être ceux que
vous voulez suivre. Dans la négative,
vous devez modifier la procédure
ConvertCounterData et la table My-
CounterData en fonction de vos besoins.

Le fin observateur que vous êtes a
probablement remarqué que nous
tronquons d’abord, puis convertissons
la valeur CounterDateTime de la table
CounterData en un champ datetime.
Comme je l’ai déjà  dit, en mettant en
place notre solution, nous avons
constaté que CounterDateTime est un
champ char(24) par défaut, pas un
champ datetime, ce qui était décevant
– particulièrement parce que Microsoft
décrit CounterDateTime comme étant
stocké en format UTC (Universal Time
Coordinate). De plus, la journalisation
System Monitor accole un caractère
ASCII supplémentaire au champ
CounterDateTime, ce qui rend la
conversion datetime impossible sauf à 
tronquer d’abord le champ à  23 caractères
– de manière à  supprimer le caractère
supplémentaire. Pour supprimer
le caractère caché, nous utilisons
la fonction LEFT(), comme le montre
le code du listing 1. En mettant
en oeuvre la procédure Convert-
CounterData, vérifiez que les numéros
CounterID de votre implémentation
correspondent aux noms de compteurs
appropriés, comme c’est le cas
dans notre exemple. (Pour plus d’informations
sur un autre bogue possible
dans les pages d’accès aux données,
voir l’encadré « Timeout de 30
secondes ».)

Après avoir créé ConvertCounter-
Data, utilisez un job SQL Server Agent
pour programmer son exécution
toutes les 5 minutes. Il est bon d’exécuter
d’abord la procédure manuellement
pour vérifier qu’elle importe les lignes de CounterData et de CounterDetails.
Vous pouvez d’ailleurs l’exécuter
autant de fois que vous le voulez
car elle n’importera pas de données redondantes.
Exécutez d’abord le job
manuellement et examinez son historique
pour confirmer qu’il fonctionne
comme prévu. Si vous créez des requêtes
à  exécuter sur les tables
MyCounterData, CounterData et
CounterDetails, vous constaterez que
vous pouvez extraire des données de
MyCounterData plus rapidement que
de CounterData et de CounterDetails,
parce que les données ont été agrégées,
consolidées et indexées.

Quand vous commencez à  stocker
des données dans la table MyCounter-
Data, il se pourrait bien que vous tiriez
un avantage autre que la simple accélération.
Si System Monitor corrompt ses
tables ou les recrée, les données historiques
restent intactes dans la table
MyCounterData comme une source secondaire
non touchée par System
Monitor.