Présentation

 
Bonjour,

freelance, administrateur de base de données et expert sur la plateforme SQL Server, j'interviens auprès de PME ou de Grands Comptes pour auditer leurs serveurs et en améliorer performances, robustesse et sécurité.

Vous pouvez faire appel à mes services pour des missions ponctuelles ou des projets plus longs.

Pascal MESSAGER
mail messager(point)pascal(point).pro(arobase)wanadoo(point)fr
06-63-94-56-97

 

Navigation

Page d'accueil

Présentation et contact : CV  

Une méthode remarquable : Audit SQL

Liens suggérés :

Un Séminaire de haut niveau http://www.sqlpass.org

 

Articles récents

liste complète

 

Trous de date en SQL 2005

Recherche de trous ou d'anomalies de date dans SQL 2005
 
L'activité enregistrée dans une base de données est généralement datée. Selon l'activité de l'entreprise, on se retrouvera chaque jour avec un certain nombre de transactions relativement stable , aux variations métier près : pour une grande surface par exemple, pic de consommateurs le samedi, chute le mardi, peu le dimanche à cause de l'ouverture sur une plage horaire restreinte...
 
Mais sauf cas de grève ou arrêt d'activité, il n'y a pas de raison de n'avoir AUCUNE transaction. Si cela arrive c'est plutôt un bug quelque part dans l'alimentation de la base de données.
 
Plus généralement, nous constatons que toute table contenant des dates a tendance à se remplir suivant une certaine logique quotidienne. Une anomalie d'alimentation se caractérisera rapidement par un nombre anormal d'enregistrements ce jour là : pas du tout, trop peu ou deux fois trop.
 
Dans le cadre d'un audit de base de données, nous souhaitons vérifier rapidement la fiabilité des données. L'article ci-dessous présente une méthode semi-automatique de recherche de trous ou d'anomalies de date dans les tables.
 
Principe
Nous allons simplement compter pour chaque table son nombre d'enregistrements par jour, et l'afficher de façon à faire ressortir les incohérences.
L'instruction de base est donc un select coldate, count(*) from matable group by coldate.
 
-         Rechercher toutes les tables utilisateurs candidates
-         Sélectionner la colonne date la plus adaptée au comptage.
-         Boucler sur toutes les tables, générer et exécuter la requête, insérer le résultat en table.
-         Faire pivoter cette table pour obtenir un affichage en tableau : dates en ligne, une colonne pour chaque table.
-         Générer une table calendrier exhaustive pour repérer les trous.
-         Afficher le tout prêt à exporter dans une feuille excel.
 
Sélection des tables
Dans un gros serveur de prod, on voudra peut-être limiter la recherche à certaines tables pertinentes. En particulier éliminer des grosses tables dont on sait que le comptage par date n'a pas de sens : tables système, temporaires, tampons d'importation...
Il est bon aussi de regarder au préalable la volumétrie globale des plus grosses tables. On ne lancera pas sans précaution le script final sur des bases de plusieurs centaines de millions de lignes.
 
 
Figure n : extrait code sql
 
select top 1000 o.name, max(rows) rows
from sysindexes i
join sysobjects o on o.id = i.id
where o.name not like 'sys%'
group by o.name
order by 2 desc
 
 
Sélection de la colonne date
Toutes les tables n'ont pas forcément une colonne de type datetime (system_type_id = 61) ou smalldateime (58). Nous ne pourrons pas faire de comptage rétroactif sur celles-ci.
 
Pour celles qui ont plusieurs dates, il faut choisir la meilleure colonne :
-         Privilégier d'abord une colonne date faisant partie d'un index, et en tête de celui-ci. Le group by sera alors optimisé et le résultat très rapide quelle que soit la taille de la table. Ce point est important sur un serveur de prod !
-         A défaut prendre la colonne date dans un index en position 2 puis 3...
-         A défaut d'index, SQL Server sera obligé de scanner toute la table. Ce qui peut être long si elle contient quelque millions d'enregistrements.
-         Prendre la première colonne trouvée dans l'ordre. En général, c'est la plus pertinente pour ce type de comptage, le concepteur de la base l'ayant mise au début. D'autres colonnes date en fin de table peuvent n'avoir qu'une moindre signification métier ou non renseignées.
 
Tant qu'on n'a pas de connaissance sur le modèle de données cette approche 'en aveugle' est la plus rapide. Il est évident que l'observation d'un échantillon de chaque table pour vérifier la pertinence du choix est hautement souhaitable. Pour un contrôle de cohérence de données, un comptage sur une date de naissance parce que la colonne est indexée est moins représentatif qu'une date d'adhésion, même non indexée, dans la même table.
 
Cette requête ci-dessous renvoit pour chaque table les colonnes dates par ordre de préférence décroissante. Ordreindex à 255 signifie qu'il n'y a aucun index utilisable.
 
 
Figure n : extrait code sql
 
select t.name nomtable, c.name nomcolonne, c.column_id, i.index_id, i.name nomindex, isnull(key_ordinal, 255) ordreindex
into #date_pref
from sys.tables t
join sys.columns c on c.object_id = t.object_id
join sys.types y on y.system_type_id = c.system_type_id
left outer join sys.index_columns ic on ic.object_id = t.object_id and ic.column_id = c.column_id
left outer join sys.indexes i on i.object_id = t.object_id and i.index_id = ic.index_id -- and i.column_id = ic.column_id
where c.system_type_id in (58,61)
and t.name not like 'sys%'
and t.name not like '%tampon' -- Elimination de tables non souhaitées
order by t.name, ordreindex, c.column_id
 
 
Nous voulons maintenant récupérer la première ligne pour chaque nomtable. C'est la colonne sur laquelle nous ferons le group by. Nous gardons l'ordre dans l'index pour choisir éventuellement de ne traiter que les tables avec un index en première position pour des raisons de performance.
 
Cette requête sera la directrice du curseur
 
Figure n : extrait code sql
 
select a.nomtable, A.nomcolonne, min(A.ordreindex) ordreindex
from #date_pref a
where A.nomcolonne = (select top 1 nomcolonne from #date_pref B where B.nomtable = A.nomtable order by ordreindex, column_id)
group by a.nomtable, A.nomcolonne
order by a.nomtable
 
 
 
Requête de comptage
Pour chaque table, la requête est de la forme select coldate, count(*) from matable group by coldate
 
Une colonne de type date peut aussi contenir une heure, nous allons donc arrondir à minuit. Pour ne pas perdre l'index, la méthode d'arrondi suivante fonctionne :
 
Figure n : extrait code sql
 
select dateadd(dd,0, datediff(dd,0,coldate)), count(*) from matable group by dateadd(dd,0, datediff(dd,0,coldate))
 
 
Il faut aussi tenir compte des tables ou colonnes dont le nom contient des caractères non standard. Typiquement un espace. Nous utilisons quotename() pour gérer ce point. MAIS, car il y a un mais, nous verrons plus loin que la syntaxe pivot n'aime pas ça !
 
Remarque injection sql. Nous allons générer une requête exécutée par exec(). Rien n'interdit à un pirate ayant juste assez de droits locaux pour créer une table de lui donner comme nom
create table [' CREATE LOGIN crac WITH PASSWORD = 'x' --] (a int)
Sa « table » sera probablement exécutée par un compte ayant plus de droits que lui. Il y a donc potentiellement un risque de sécurité.
 
 
Figure n : extrait code sql
 
select @req =
'insert into #comptage select dateadd(dd,0, datediff(dd,0,' + quotename(@nomcolonne) + '))'
+ ', ''' + @nomtable + ''', count(*) from ' + quotename(@nomtable)
+ ' group by dateadd(dd,0, datediff(dd,0,' + quotename(@nomcolonne) + ')) '
 
 
 
Table de comptage
Nous créons une table dans laquelle nous allons insérer les comptages bruts.
 
Figure n : extrait code sql
 
create table #comptage
(jour smalldatetime
, nomtable sysname
, nbenreg int
)
 
 
Boucle par curseur
 
A l'aide d'un curseur simple, nous générons la requête de calcul pour chaque table.
 
Figure n : extrait code sql
 
declare @nomtable sysname, @nomcolonne sysname, @ordreindex tinyint
declare @i smallint
declare @req nvarchar(1000)
declare @listepivot nvarchar(1000)
set @i = 0
set @listepivot = ''
set @i = 0
 
declare cpt_cur cursor             
for
       select a.nomtable, A.nomcolonne, min(A.ordreindex) ordreindex
       from #date_pref a
       where A.nomcolonne = (select top 1 nomcolonne from #date_pref B where B.nomtable = A.nomtable order by ordreindex, column_id)
       group by a.nomtable, A.nomcolonne
       order by a.nomtable
 
open cpt_cur
fetch next from cpt_cur
into @nomtable, @nomcolonne, @ordreindex
 
WHILE @@FETCH_STATUS = 0
begin
       -- comptage par group by
       select @req = 'insert into #comptage select dateadd(dd,0, datediff(dd,0,' + quotename(@nomcolonne) + '))'
              + ', ''' + @nomtable + ''', count(*) from ' + quotename(@nomtable)
              + ' group by dateadd(dd,0, datediff(dd,0,' + quotename(@nomcolonne) + ')) '
       print @req
       exec (@req)
       -- Construit la liste pour le pivot
       set @listepivot = @listepivot + case when @i = 0 then '' else ', ' end + '"' + @nomtable + '"'
       set @i = @i + 1
       -- Suivant
       fetch next from cpt_cur
       into @nomtable, @nomcolonne, @ordreindex
end
close cpt_cur
deallocate cpt_cur
 
 
 
Construction de la requête pivot
L'instruction PIVOT permet de transformer une table 'verticale' en un tableau à deux dimensions. Nous allons utiliser cette instruction pour générer un tableau avec les jours en ligne, le nom de chaque table en colonne et bien sûr le nombre d'enregistrements à l'intersection. PIVOT renvoit null s’il n’y a pas d’entrée correspondante dans la table source.
 
Jour
Table1
Table2
Table3
01-01-2008
12
5484
17620
02-01-2008
15
6827
Null
03-01-2008
33
34084
94853
 
La syntaxe est de la forme :
 
Figure n : extrait code sql
 
select jour, Table1, Table2, Table3
from #comptage
pivot (       sum(#comptage.nb) for tablename in (Table1, Table2, Table3)
       ) as p
order by jour
 
 
où Table1, Table2, Table3 représentent des valeurs distinctes dans la colonne nomtable
 
Dans la boucle du curseur ci-dessus, nous parcourons chaque table. Nous allons en profiter pour construire la liste de pivot en rajoutant dans le coeur de la boucle :
 
Figure n : extrait code sql
 
set @listepivot = @listepivot + case when @i = 0 then '' else ', ' end + '"' + @nomtable + '"'
set @i = @i + 1
 
 
Dans la syntaxe de PIVOT, le contenu d'une colonne devient un nom de colonne. Il lui faut donc une syntaxe acceptable. Si une table contient un espace ex : create table [ma table] (a int) , encadrer le nom de table par des crochets, à l'aide quotename par exemple ne fonctionne pas. PIVOT renvoit systématiquement null. Avec des quillements doubles, ça marche si on active set QUOTED_IDENTIFIER ON. Comme quoi l'usage de caractères non standards dans les noms sysname est toujours une source de difficutés cachées, de trop nombreuses applications ne les gérant pas.
 
On génère le résultat pivoté dans une table temporaire globale, parce qu'une tempo locale disparaitrait à la fin du exec. Comme on ne connait pas la structure de la table, le nombre de colonnes étant variable, il vaut mieux la laisser créer automatiquement.
 
Figure n : extrait code sql
 
select @req =
'select jour,
' + @listepivot
+ '
 into ##pivcompt from #comptage
 pivot (       sum(#comptage.nbenreg) for nomtable in ( '
+ @listepivot
+ '   
)) as p     
order by jour desc '
 
 
Table calendrier
A ce stade, nous avons notre tableau pivoté. Mais si aucune table ne contient d'enregistrement un jour donné, il n'y a pas d'entrée dans la table comptage ni dans la table pivotée. Visuellement, le trou n'apparait pas facilement.
 
Nous créons donc une table calendrier contenant tous les jours compris entre les deux dates extrêmes de notre comptage.
 
Figure n : extrait code sql
 
create table #calendrier (jour smalldatetime)
declare @deb smalldatetime 
declare @fin smalldatetime 
select @deb = min(jour), @fin = max(jour) from #comptage
declare @d smalldatetime
set @d = @deb
while @d <= @fin
begin
       insert into #calendrier (jour) values ( @d )
       set @d = dateadd(day, 1, @d)
end
 
 
Avant de générer ce calendrier, vérifier les dates extrêmes. Si une activité apparente a eu lieu le 31/12/9999, outre le remplissage intempestif de la table calendrier (8000 lignes), il faut s’interroger sur la raison d’être de cette date. Idem le 01/01/1900.
Table finale
 
Enfin une jointure externe entre le calendrier et la table pivotée nous donne pour chaque jour le nombre d'enregistrements de chaque table, avec null si pas d'enregistrement.
 
Figure n : extrait code sql
 
select #calendrier.jour, datepart(dw, #calendrier.jour) joursemaine , ##pivcompt.*
from #calendrier
left outer join ##pivcompt on ##pivcompt.jour = #calendrier.jour
order by #calendrier.jour desc
 
 
Effet de bord imprévu mais pratique. Si aucune table n'a d'enregistrement pour un jour donné, la deuxième colonne 'jour' correspondant à la table pivotée est null. Ce qui saute aux yeux.
On rajoute aussi le jour de la semaine, pratique pour vérifier les tables dont l'activité varie hebdomadairement.
 
Export vers Excel
 
En exécutant la requête finale en mode texte séparé par des tabulation, on copie-colle le résultat dans excel. Un simple formatage conditionnel pour mettre les null en couleur fait mieux apparaître les trous.
 
L'idéal étant ensuite de créer des courbes graphiques. Les anomalies sautent aux yeux :
-         Absence totale de données : Activité nulle ou échec d'importation ce jour là.
-         Doublement brutal du nombre d'enregistrements : un fichier a été importé deux fois sans gestion de dédoublonnage (clés primaires, index uniques, réentrance du processus...)
-         Variation importante : incident de type métier ? Voir avec les utilisateurs si c'est un comportement normal.
-         Date hors limite : c’est quoi ces transactions à la date du 01/01/1900 ?
-         Dates spéciales : surveiller les dates du 1er au 12 de chaque mois. Elles sont susceptibles de recevoir des données intervertissant le mois et le jour en cas de conflit de format français et US (jj-mm-aaaa ou mm-dd-yyyy)
-         Tendance : Est-ce qu'une table enfle de plus en plus vite ou au contraire diminue doucement. Qu'en déduire ?
-         Obsolescence : une table figée depuis des mois n'est probablement plus utile.
 
Automatisation
 
Pour un DBA de production, il est souhaitable de surveiller chaque jour le nombre d’enregistrements de toutes ses tables. C’est un indicateur peu coûteux du bon déroulement des processus. Moyennant quelques adaptations, et une table comptage permanente, le script ci-dessus sera placé en job quotidien, avec rétroactivité de quelques jours pour les tables avec un certain décalage. On peut aussi créeer toutes sortes d’alertes sur comportement anormal en fonction du métier.
 
Pascal MESSAGER
Copyright 2008 – Tous droits réservés.
 
Blog : Guides d'achat sur over-blog.com - Contact - C.G.U. - Rémunération en droits d'auteur avec TF1 Network - Signaler un abus