BI experience

8 août 2016
par maryam khiali
0 Commentaires

SQL server Index et statistiques

Je voudrai faire cet article pour revenir sur des concepts basiques mais important.

Une base de données est constituée notamment de tables. Ces tables sont constituées notamment de colonnes, d’index et de statistiques. Jusqu’ici rien de nouveau…

L’une des choses qu’il faut étudier pour faire en sorte que votre base de données soit optimisée est l’exploitation de ces index et statistiques.

Rappel :

Les index :

  • Les index permettent sur une ou plusieurs colonnes de faire un trie sur les données (comme dans un annuaire). Le parcours des données n’en sera que plus facile pour le SGBD.
  • Lors de la création d’une PRIMARY KEY, un index est automatiquement créé : un index cluster. Il est unique pour chaque table.
  • D’autres index pourront être créés : des index « non-cluster »
  • Un index aura forcément une statistique associée.
  • Lors d’une requête si une fonction est appliquée sur le champ possédant un index, ce dernier ne sera pas utilisé (exemple : substring, YEAR(), etc.) pareil pour l’opérateur LIKE.
  • De plus, pour un index sur plusieurs colonnes, si dans une requête, l’ensemble de ces colonnes n’est pas appelé alors cet index ne sera pas utilisé

Les statistiques :

  • Les statistiques permettent d’avoir le nombre d’occurrences de chaque valeur d’une colonne. Elles représentent la distribution des valeurs.
  • SQL server se base sur ces statistiques pour évaluer le coût du plan d’exécution. En effet, lors de l’exécution d’une requête si le nombre de lignes avec le critère demandé est beaucoup plus petit que le nombre de lignes total de la table alors SQL server utilisera l’index sinon il parcourra toute la table. Le nombre de lignes avec le critère demandé correspondant au nombre d’occurrences  défini par les statistiques.
  • Lors de la création d’un index, une statistique est automatiquement créée
  • Des statistiques peuvent être créées sur des champs ne possédant pas d’index.
  • Les statistiques peuvent être créées automatiquement par SQL server grâce à l’option AUTO UPDATE STATISTICS

Evaluez votre base de données : les index de vos tables ainsi que les statistiques avec la date de leurs dernières mises à jour :
SELECT
SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]
, [sObj].[name] AS [ObjectName]
, CASE
WHEN [sObj].[type] = ‘U’ THEN ‘Table’
WHEN [sObj].[type] = ‘V’ THEN ‘View’
END AS [ObjectType]
, [sIdx].[index_id] AS [IndexID] — 0: Heap; 1: Clustered Idx; > 1: Nonclustered Idx;
, ISNULL([sIdx].[name], ‘N/A’) AS [IndexName]
, CASE
WHEN [sIdx].[type] = 0 THEN ‘Heap’
WHEN [sIdx].[type] = 1 THEN ‘Clustered’
WHEN [sIdx].[type] = 2 THEN ‘Nonclustered’
WHEN [sIdx].[type] = 3 THEN ‘XML’
WHEN [sIdx].[type] = 4 THEN ‘Spatial’
WHEN [sIdx].[type] = 5 THEN ‘Reserved for future use’
WHEN [sIdx].[type] = 6 THEN ‘Nonclustered columnstore index’
END AS [IndexType]
, [sCol].[name] AS [ColumnName]
, CASE
WHEN [sIdxCol].[is_included_column] = 0×1 THEN ‘Yes’
WHEN [sIdxCol].[is_included_column] = 0×0 THEN ‘No’
WHEN [sIdxCol].[is_included_column] IS NULL THEN ‘N/A’
END AS [IsIncludedColumn]
, [sIdxCol].[key_ordinal] AS [KeyOrdinal]
, [sObj].create_date
, [sObj].modify_date
,PhysicalStats.page_count as [Page_Count],
CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Size (MB)],
CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Frag %],
ParititionStats.row_count AS [Row Count],
CASE WHEN ParititionStats.row_count =0 THEN 0 ELSE CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024)
/ ParititionStats.row_count)END AS [Index Size/Row (Bytes)]
, STATS_DATE([sIdx].object_id, [sIdx].index_id) as derniereDateMAJStatistique
FROM
[sys].[indexes] AS [sIdx]
INNER JOIN [sys].[objects] AS [sObj]ON [sIdx].[object_id] = [sObj].[object_id]
LEFT JOIN [sys].[index_columns] AS [sIdxCol]ON [sIdx].[object_id] = [sIdxCol].[object_id] AND [sIdx].[index_id] = [sIdxCol].[index_id]
LEFT JOIN [sys].[columns] AS [sCol]ON [sIdxCol].[object_id] = [sCol].[object_id] AND [sIdxCol].[column_id] = [sCol].[column_id]
LEFT JOIN sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL) AS PhysicalStats ON
PhysicalStats.index_id = [sIdx].Index_id and PhysicalStats.object_id = [sIdx].object_id
LEFT JOIN sys.dm_db_partition_stats ParititionStats ON ParititionStats.index_id = [sIdx].index_id
and ParititionStats.object_id = [sIdx].object_id
WHERE
[sObj].[type] = ‘U’
ORDER BY [sObj].modify_date, [sObj].create_date , SCHEMA_NAME([sObj].[schema_id]), ColumnName

Cette requête  permet d’avoir une vision des indexs et de leurs statistiques  :

  • la taille de l’index
  • Le nombre de pages utilisé par cet index
  • La fragmentation de l’index
  • au regard du nombre de lignes total présent dans la table
  • La dernière mise à jour de la statistique qui lui est associée
  • repérer les tables sans index : table avec un type d’index ‘HEAP’

Pour des index et statistiques à jour, il ne faut pas oublier de mettre en place un plan de maintenance avec les deux étapes de :

  • Rebuild des index
  • Update des statistiques

Avez vous des requêtes d’analyses à nous faire partager?

 

30 mars 2016
par maryam khiali
1 Commentaire

Cube SSAS dynamique

Quand on peut rendre nos développements dynamiques, il ne faut pas hésiter.

Le but est d’avoir des cubes avec une volumétrie (une profondeur) constante.

On peut obtenir cela en :

    • faisant appelle à des vues au niveau de la DSV qui auront un filtre temps en fonction de la GETDATE()
    • en mettant des partitions filtrées en fonction aussi de la GETDATE() (voir ce post)
    • Même démarche pour la dimension temps
    • et pour les slices? Et bien non, elles ne peuvent pas être rendues dynamiques.(voir MSDN). Même dans la version SQL server 2014…Peut être que se sera possible un jour mais en attendant si votre cube est dynamique, vous ne pourrez pas utiliser les slices avec une variable…
    • Petit bonus, voici ce petit lien qui explique vraiment bien l’intérêt de cette propriété.

Avez vous d’autres astuces pour rendre nos cubes autonomes et performants?

20 janvier 2015
par maryam khiali
1 Commentaire

La plateforme Microsoft

On m’a transmis un document avec ces 3 représentations ci-dessous. J’ai trouvé qu’elles étaient vraiment intéressantes car elle synthétise les outils mis en place par Microsoft et sa vision de la BI.

1er slide : Vision où le service informatique est garant de la BI dans l’entreprise

2eme slide : Vision d’une plateforme où les métiers sont garants de la BI dans l’entreprise

3eme slide : Vision d’une plateforme de collaboration entre le service informatique et les métiers. Les deux sont garants de la BI dans l’entreprise

Qu’en pensez vous? Dans quel cas êtes-vous? N’hésitez pas à partager votre expérience!

20 janvier 2015
par maryam khiali
0 Commentaires

Les compétences d’un consultant Microsoft BI d’aujourd’hui

Je ne vous apprends rien en vous disant qu’il y a de plus en plus d’outils BI qui sortent sur le marché, de plus en plus de fonctionnalités, de scénarios possibles pour faire de la BI.

Cette multiplication d’outils, nous pouvons aussi la constater au sein d’un même éditeur de logiciel comme Microsoft.

Voici un petite liste de ce qu’on attend aujourd’hui d’un consultant MSBI :

Niveau 1 : les basiques

Niveau 2 :  tous les autres scénarios que propose Microsoft qu’il faut connaitre

Comme on peut le constater, il y a de quoi faire…Alors bon courage à tous!

Qu’en pensez vous? Voyez vous d’autres briques Microsoft qu’un consultant BI aurait besoin de maitriser? N’hésitez pas à me laisser un petit commentaire…

29 octobre 2014
par maryam khiali
0 Commentaires

DB Comparer

Aujourd’hui, j’ai fait la connaissance avec un petit outil sympa : DB Comparer

Cet outil comme son nom l’indique permet de faire la comparaison entre  2 bases de données. Le voici, ci-dessous en action :

Comme vous pouvez le voir les lignes surlignées mettent en lumière, les endroits ou une différence est constatée.

Connaissez vous d’autres logiciels de ce type qui simplifie la vie? N’hésitez pas à partager!