[SAS] Monitoring SSAS with DMVs

Speaker : Tim Peterson – Niveau : 400 – Durée : 75mn

Objectif de la session

Identifier et résoudre les problèmes de performances sur les cubes OLAP

SSAS Perfomance

Les buts

2 axes de considérer  :

  • Query performance
  • Process performance

Considérations

  • Dimension & cube desing
  • MDX calculations and queires
  • Partition and aggregation strategy
  • Utilisation du cache (soyer attentif aux 3 types de caches mis en jeux : AS, MDX, Windows)

N.B : Sur de larges cubes vous aurez besoins d’agrégation et/ou de caching

Stratégies pour optimiser les process

  • Utiliser toutes les ressources sur le serveur : process en parallel => utilisation des partitions
  • Optimiser la récupération de données dans vos bases relationnelles
  • Séparer les étapes de process data et d’index au lieu de faire un process full
  • Isoler les etapes de process et de requêtage (processer lorsque le cube n’est pas utiliser, processer sur un serveur non utilisé pour le requêtage puis synchroniser)

SQL Server Profiler

On l’utiliser pour analyser des requêtes ou des opérations de façon indviduelle, bien ciblé

Les infos qu’il faut récupérer

  • La requête exécutée
  • Le temps d’exécution
  • Est-ce que le cache est utilisé ou non ?
  • Est-ce que des aggregations sont utilisée ou non ?
  • Combien de partition sont requêtées ?
  • Combien du sous-cube ont été générés par la requêtes ?
  • La part dans le temps d’exécution du formula engine et du storage engine ?

Les évènements à sélectionner lorsque vous exécuter une trace SSAS :

Mise en place de 2 templates :

  • query text and duration : les events sont Query Begin, Query End, Execute MDX Script Begin, Execute MDX Script End, Query Cube Begin, Query Cube End
  • cache and agregation subcube template : Query Begin, Query End, Execute MDX Script Begin, Execute MDX Script End, Query Cube, Query Cube End, Get Data Form Aggregation, GetDate From Cache, Query Subcube Verbose, Resource Usage)

Pour comparer Formula Engine Vs Storage Engine Time

  • Sauvegarder votre trace en base
  • Executer une requête qui calcule le Formula Engine Time et le Storage Engine Time)
    • Si pb sur formula Engine Time => optimisation à faire sur vos calculations MDX
    • Si pb sur Storage Engine Time => opitmisation à faire sur le stockage (partitions)

Démos

1ère démo

Avant tout chose videz le cache afin d’avoir des tests efficaces. Bon tout le monde connait la requête XMLA qui permet de vider le cache SSAS.

N.B : vous pouvez récupérer le composant codeplex ASSP qui permet de vider le filesystem cache windows.

Le test est effecuté sur un cube sans aggregation avec un cross join => 55 secs, si on la reéexecute elle ne prend qu’1 sec avec le cache AS et le filesystem cache et 7 sec si on vide le cache SSAS sans vide le filesystem cache windows (d’où l’importance de bien vider les caches avant vos tests)

On analyse les traces duration Execute MDX Script End + duration Query Cube End = duration Query end

On compare la même requête sur le même cube avec des aggrégations, en vidant tous les caches ont arrive à 4sec contre 55sec auparavant => les aggrégations ont permis de gagner 51 sec.

Pour récupérer les traces sur les agrégations, on relance le test avec le 2nd scénario de trace.

On note au passeage que l’event Get Data Form Aggregation remonte en ObjetPath l’agregation ID at pas le nom idem pour la partion (attention donc à comment vous nommer vos agrégations lors de la création une convention de nommage s’impose donc pour analyser les traces plus facilement).

On relance la requête, on se rend compte qu’AS n’utilise pas les agrégations mais que le cache (ce dernier étant plus rapide que les agrégations)

Dans le Query Subcube Verbose on a depuis la version 2012 des infos sur le cache (Entries, Hits, …)

Le resource usage est là depuis la 2008 R2 eet permet de voir l’utilisation des ressource (reads/writes in #/Kb, CPU_TIME …)

2eme démo

Analyse d’une requête executée sous Excel

La clé est de voir les mesures qui sont utilisée dans la requête imbitable que nos a envoyé Excel et d’indentifier la ou les mesures qui posent problème.

Une fois la mesure identifiée qui pose pb (en règle générale) ce sont des calculation, on la récupère et on la colle dans SSMS.

On constate l’utilisation d’un iif pour éviter de faire une division par 0, une partie de la calculation est utilisée 2 *. L’optimisation consiste à séparer les calculs pour utiliser des mesures individuelles de façon à se que AS puisse évaluer et optimiser chaque calculation. Le temps d’execution passe de 2’30 à 55 sec => faites attention de bien déclarer des mesures de façon unitaires afin que le moteur n’est pas à évaluer un même calcul plusieurs fois dans une seule et même mesure.

3eme démo

Identifier d’ou provient la faille Formula Engine ou Storage Engine :

  • La trace est enregistrée en base
  • On lance ensuite un petite requête magique qui permet de récupérer les stats don’t nous avons besoin.

Don’t worry  la requête est fournis dans le code sample de la présentation dispo sur sqlpass.org.

Perf Monitor

On l’utilise pour analyser la performance global de SSAS.

Les compteurs positionnés sont  : ¨% Processor Time, Memory limit Hard, High, Low in Kb

On récupère les traces du perf mon qu’on remonte dans un cube. Ce qui nous permet d’analyser les traces.

On peut récupérer les sources de son cube sur le site du PASS, avec les slides de la présentation.

Automating Collection of Perf Information with ResMon

Les sources du cube ResMon sont à récupérer sur CodePlex, il utilise les DMVs pour récupérer les compteurs de perfs.

Mais çà je l’avais déjà vu dans une autre présentation.

Conclusion

Session interressante, surtout orientée sur le performance monitor avec les évènements à tracer les causes et les conséquences.

Dommage qu’on ai pas vu d’optimisation sur des bottleneck dû aux CROSSJOIN …

Frédéric

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s