Rechercher dans ce blog

Mes publications

lundi 16 janvier 2017

SQL Server : les performances ?

 

Vous les connaissez peut-être déjà :

image

Nous serons présents chez ORSYS du 20 au 24 mars 2017 pour animer un séminaire exceptionnel sur SQL Server du point de vue des performances.

  • Lundi : Frédéric BROUARD : Modéliser pour la performance et l'optimisation sémantique
  • Mardi : Christophe LAPORTE : Choisir le matériel, réaliser une installation efficace et réussir la virtualisation
  • Mercredi : Arian PAPILLON : Maîtriser l'indexation, les statistiques et la supervision
  • Jeudi : Rudi BRUCHEZ : Comprendre et analyser les plans d'exécution, tracer avec les événements étendus
  • Vendredi : Dominique VERRIERE : Programmer efficacement, optimiser Entity Framework et comprendre le verrouillage

 

Tous les détails et inscriptions ici : Master class SQL Server

image

mercredi 23 novembre 2016

Enfin !

 

Voilà des années que je constatais que Microsoft dédiait les fonctionnalités avancées à la seule édition Enterprise (qui vaut tout de même 9 fois le prix de la standard), le SP1 de la version 2016 remet les choses en place !

 

  Enterprise Standard
Column store Illimité Limité à 32 Go
Partitionnement OUI OUI
Compression de données OUI OUI

 

Ok le column store est limité à 32 Go, mais cela permettra toutefois de faire des maquettes et autres ‘Pokes’…

Le partitionnement est trés intéressant car il n’est plus rare de trouver des tables avec plus de 100 millions de lignes (mes clients se reconnaitront!)

Quand à la compression de données cela permettra à certains consultants pressés (ce n’est pas mon cas ) de cacher la misère d’une base de données vraiment trop mal conçue …

 

En bref que des bonnes nouvelles !

jeudi 3 novembre 2016

Gestion des pools de connexions, étude de problèmes

 

 

Qu’est ce que le pool de connexions ?

Les applications Web se caractérisent par leur aspect ‘fugitiff’ par rapport à la base de données, au contraire des applications en client lourd qui peuvent maintenir une connexion sur une longue période.

 

Afin de garantir les meilleures performances ADO.Net gère un pool de connexions tournantes : en effet ouvrir une connexion peut être une opération longue.

Les paramètres de ce pool peuvent être forcés dans la chaine de connexion sous la forme (Clé,Valeur)

Nous avons principalement :

  • Max Pool Size : la valeur par défaut est de 100
  • Min Pool Size ; la valeur par défaut est de 0.

Pourquoi changer ces valeurs ?

Sur un site très chargé, on peut considérer que 100 est une valeur un peu faible.

A l’inverse, le minimum à 0 fera que l’on perdra la dernière connexion après une certaine inactivité : le temps de réponse de la 1ère requête pourrait s’en ressentir.

Comment voir ce qui est en cours ?

Le plus simple est de lancer une requête sur le serveur SQL pour connaitre les activités actuelles :

with CurrentSessions as
(
select t1.session_id,t1.host_name,t1.program_name,t1.login_time,
t1.login_name from sys.dm_exec_sessions t1
    where host_name is not null and
    program_name not like 'Microsoft SQL Server%' and
    program_name not like 'SQLAgent%' and
    program_name not like 'Repl-LogReader%'
)
select '' as TypeRow,ltrim(str(t1.session_id)) as SessionId,t1.host_name as HostName,
t1.program_name as ProgramName, t1.login_time as LoginTime,
(select Text from sys.dm_exec_sql_text ((select sql_handle from sys.sysprocesses
    where spid = t1.session_id)))
as LastSqlText,
1 as NbeSessions from CurrentSessions t1

union all
select * from
(
select 'All-->' as TypeRow,''as SessionId,host_name,program_name,max(login_time) as LoginTime,'' as LastSqlText,
    count(*) as NbeSessions
from CurrentSessions
group by host_name,program_name
)t
order by TypeRow, login_time desc

 

(Les filtres sont à adapter à votre besoin)

Qui donne ce genre de résultat :

image

 

Eviter l’erreur Insuficient pool connexions

 

Si bien sûr elle ne vient pas d’une charge excessive… il est probable que les open/close ne soient pas appariés..

En C# le mieux est d’utiliser le mot clé using qui appelera dispose en fin d’action : on est ainsi sûr de libérer les connexions.

Avant :

image

Après :

image

 

Un point d’arrêt dans cette méthode permet de voir la réallocation du pool :

image

Je suis arrêté dans la méthode…

La connexion en cours (le SPID 65) vient de faire ma requête :

image

Après sortie du using :

image

La connexion 65 a été réutilisée pour autre chose….cool !

jeudi 28 janvier 2016

Prise en main de Power Bi Desktop

 

Voici plusieurs mois déjà que Microsoft a sorti ce composant d’analyse des données. Il est temps pour moi de regarder un peu ce qui s’y passe …

Comme dans PowerPivot (dont il hérite sur pas mal d’aspects) tout commence par la connexion aux sources de données :

image_thumbimage_thumb1image_thumb2

Impressionnant ! Un très grand nombre de connecteurs est disponible…

Extraction des données

Mes données venant d’un bon vieux serveur SQL , c’est ce connecteur que je vais utiliser.

image_thumb51

Où l’on voit qu’il y a du PowerPivot derrière :

image_thumb52

Je choisis ici de stocker mes propres données… ce qui chacun en conviendra a des avantages et des inconvénients (notamment la sécurité)

On reconnait le process de PowerPivot :

image_thumb53

Il est possible de revenir à mes requêtes (je n’ose pas dire à ma DSV mais c’est bien de cela dont il s’agit)

image_thumb54

On pourra notamment y faire le ménage et renommer les choses telles que nécessaire :

image_thumb55

J’ai ainsi pu alléger beaucoup la table produit :

image_thumb56

On note que, en fonction des relations héritées du modèle relationnel, l’outil propose de voir les valeurs (lien vers un) ou les tables (line vers plusieurs) associées.

Le tout pouvant être annulé, ce qui est une option sympathique si on s’est trompé.

Après ces opérations, un process des données est de nouveau réalisé…

Visualisation des données

Nous trouvons ici des choses vues dans d’autres outils (les régions ne vous rappellent rien ?)

image_thumb57

Je peux choisir un simple tableau pour démarrer :

Un simple glisser-déposer permet d’apporter les champs dans le tableau (y compris venant des tables associées par du plusieurs à un.

La mise en forme de ce tableau est largement simplifiée par une palette d’outils à droite de l’écran :

image_thumb58image_thumb59

Tout ceci manque un peu d’agrégats …

Deux onglets permettent de les définir, ainsi que revoir les relations…

image_thumb60

On peut ajouter par exemple :

image_thumb61

On constate avec bonheur que le := a disparu au profit du simple égal (comme dans les expression de colonnes) mais … il s’agit toujours de DAX.

Le concepteur de formules est très agréable à utiliser et les mesures apparaissent tout de suite dans la liste des champs (beaucoup mieux qu’en powerpivot)

image_thumb62

Voyons tout cela :

image_thumb63

Parfait c’est le but recherché !

Une fois ces données de base extraite, il va être très facile de les afficher sous différents rendus :

image_thumb17

Parlons des filtrages

image_thumb20

Très intéressant : même des tables en relation à plusieurs peuvent servir !

Attention toutefois, le modèle sous-jacent étant du PowerPivot, la relation unique entre deux tables est toujours de mise :

image_thumb23

Ici, sur 3 liens possibles avec la table de temps, un seul est actif, la sémantique de mon filtre précédent est donc ‘Pour les années de commande 2006 et 2007….’

Je profite d’être ici pour montrer le masquage qui peut toujours être utilisé :

On peut masquer des colonnes de table :

image_thumb64

Ce qui aura pour effet de simplifier l’extraction de données dans le rapport mais aussi de le fiabiliser !

image_thumb65

Dans le même ordre d’idées, on peut aussi ‘mettre à plat des tables ‘ pour en masquer la complexité et mieux gérer le cas des jointures externes…

image_thumb30

On notera avec plaisir que les bugs de renommage de Powerpivot sont de l’histoire ancienne….

Ceci permet de faire apparaitre facilement les produits sans catégorie :

image_thumb33

Le début du jeu de données pourrait laisser penser qu’il y a un bug… mais la fin confirme que les formules DAX fonctionnent correctement :

image_thumb36

Il est maintenant temps de masquer les deux tables qui ne serviront plus (Catégorie et sous catégorie)

image_thumb39

Notre jointure externe est-elle opérationnelle ?

Un petit forçage des données dans la source va le montrer :

image_thumb42

Le produit 1 n’a pas de catégorie…

image_thumb45

Ce qui est correct… la catégorie N/A créée précédemment étant justement ce que l’on cherche….

On peut noter que le tri alphabétique peut poser problème, la bonne pratique étant d’éviter les produits sans catégories…

On peut résoudre cela par une colonne de tri supplémentaire…

image_thumb67

et trier la colonne catégorie selon cette colonne que l’on masquera bien sûr…

image_thumb49

Impeccable …. exactement le but recherché !

Ce genre d’astuce pourra permettre de changer l’ordre naturel des choses et faire , par exemple, Paris,Agen,Bordeaux,Compiègne….

1ère conclusion

Que du bon , je constate que les points désagréables de PowerPivot disparaissent…la manipulation des données devient un jeu d’enfant (dans le cas des extractions simples….bien sûr!)

Je pense poster d’autres approches sur ce produit intéressant.

samedi 27 juin 2015

MDX : comptages à la volée

 

Un des participants d’une session de formation (Gaël se reconnaitra) me pose la question d’aouter un membre calculé de nombre de clients sur un cube classique de suivi des ventes.

Dans les modèles multi-dimensionnels, une mesure doit être associée à une table de faits (contrairement au modèle tabulaire) … c’est pourquoi je pense qu’il  y a une bonne solution et des moins bonnes…

 

La bonne solution :

Il suffit de créer une mesure de comptage distinct sur un identifiant significatif, ici IDClient.

image

image

Classiquement, cela crée un groupe de mesure séparé (les count distinct ne sont pas additifs).

L’usage est alors très simple et performant :

// La bonne solution
// Requête sans l'axe client
select {
[Measures].[Montant Ventes Internet],
[Measures].NbeClients
} on 0,
[Produit].[Categorie].[Categorie] * [Date Commande].[Mois].[Mois]
on 1
from [AnalyseVentes]
where [Date Commande].[Annee].&[2003]
;

image

 

// Requête avec l'axe client
select {
[Measures].[Montant Ventes Internet],
[Measures].NbeClients
} on 0,
non empty [Produit].[Categorie].[Categorie] *
[Date Commande].[Mois].[Mois] *
[Client].[Client].[Client]
on 1
from [AnalyseVentes]
where [Date Commande].[Annee].&[2003]
;

image

Le cross join categorie X mois X Client donne un grand nombre de lignes ,mais mon cube gère bien cette volumétrie :

image

La requête a demandé 4 secondes ce qui est excellent au vu du nombre de cellules….

Une autre solution

Si on ne peut créer cette mesure, il va falloir la calculer à la volée… attention la sémantique est ici : “Le nombre de clients ayant acheté quelque chose sur Internet”

// Requête sans l'axe client dynamique
with member
[Measures].NbeClientsDynamique as
count 
    (
    existing
    nonempty([Client].[Client].[Client] *[Measures].[Montant Ventes Internet] )
    )
select {
[Measures].[Montant Ventes Internet],
[Measures].NbeClientsDynamique
} on 0,
[Produit].[Categorie].[Categorie] * [Date Commande].[Mois].[Mois]
on 1
from [AnalyseVentes]
where [Date Commande].[Annee].&[2003]

image

Où l’on retrouve, heureusement, les mêmes chiffres qu’avec notre mesure sur la table de faits.

La clause existing est là pour réduire le comptage à la requête sous jacente , pas à l’ensemble du cube.

Ajoutons maintenant l’axe client :

// Requête avec l'axe client dynamique V1
with member
[Measures].NbeClientsDynamique as
count 
    (
    existing
    nonempty([Client].[Client].[Client] *[Measures].[Montant Ventes Internet] )
    )
select {
[Measures].[Montant Ventes Internet],
[Measures].NbeClientsDynamique
} on 0,
[Produit].[Categorie].[Categorie] *
[Date Commande].[Mois].[Mois] *
[Client].[Client].[Client]
on 1
from [AnalyseVentes]
where [Date Commande].[Annee].&[2003]

image

Eh oui ! Le nombre de combinaisons à étudier est trop important, plantant le moteur OLAP…

 

Il va falloir optimiser cela :

Je vais déjà réduire le sous cube sur lequel je travaille pour me permettre d’étudier différents scénarios (sans attendre deux minutes à chaque run)

from
(
select [Date Commande].[Annee].&[2003] on 0,
filter ([Client].[Client].[Client],
[Client].[Client].currentmember.name >= "Adams" and
[Client].[Client].currentmember.name < "AdamsZZ"
)on 1
from [AnalyseVentes]

 

Ici les clients dont le nom commence par Adams et l’année 2003…

// Requête avec l'axe client dynamique V2
with member
[Measures].NbeClientsDynamique as
case
    when isempty( [Measures].[Montant Ventes Internet]) then null
    else
        count 
            (
            existing
            nonempty([Client].[Client].[Client] *[Measures].[Montant Ventes Internet] )
            )
    end
select {
[Measures].[Montant Ventes Internet],
[Measures].NbeClientsDynamique
} on 0,
non empty [Produit].[Categorie].[Categorie] *
[Date Commande].[Mois].[Mois]
* [Client].[Client].[Client]
on 1
from
(
select [Date Commande].[Annee].&[2003] on 0,
filter ([Client].[Client].[Client],
[Client].[Client].currentmember.name >= "Adams" and
[Client].[Client].currentmember.name < "AdamsZZ"
)on 1
from [AnalyseVentes]
)
;

L’optimisation du membre consiste à ne compter que si on a une intersection entre client et fait (je rappelle que ce membre signifie le nombre de clients ayant acheté sur Internet)

image

Il serait bien que cette optimisation fonctionne dans tous les cas :

 

Cas 1 sans invocation de l’axe client :

// Requête sans l'axe client dynamique V2
with member
[Measures].NbeClientsDynamique as
case
    when isempty( [Measures].[Montant Ventes Internet]) then null
    else
        count 
            (
            existing
            nonempty([Client].[Client].[Client] *[Measures].[Montant Ventes Internet] )
            )
    end
select {
[Measures].[Montant Ventes Internet],
[Measures].NbeClientsDynamique
} on 0,
non empty [Produit].[Categorie].[Categorie] *
[Date Commande].[Mois].[Mois]
on 1
from
(
select [Date Commande].[Annee].&[2003] on 0
from [AnalyseVentes]
)
;

image

Cas 2 avec invocation de l’axe client :

with member
[Measures].NbeClientsDynamique as
case
    when isempty( [Measures].[Montant Ventes Internet]) then null
    else
        count 
            (
            existing
            nonempty([Client].[Client].[Client] *[Measures].[Montant Ventes Internet] )
            )
    end
select {
[Measures].[Montant Ventes Internet],
[Measures].NbeClientsDynamique
} on 0,
non empty [Produit].[Categorie].[Categorie] *
[Date Commande].[Mois].[Mois] *
[Client].[Client].[Client]
on 1
from
(
select [Date Commande].[Annee].&[2003] on 0
from [AnalyseVentes]
)
;

image

image

La requête a demandé 3 minutes 24 ce qui n’est évidemment pas sympathique…mais le nombre de cellules à examiner était important.

 

Conclusion :

Une bonne mesure de la table de faits est moins risquée du point de vue sémantique et performances !

mardi 23 juin 2015

SQL Server les versions

 

Le moment est venu de faire le point sur les toutes dernières versions de SQL Server, en effet ce produit évolue de plus en plus rapidement.

C'est ainsi que, en France, nous pouvons trouver au moins quatre versions en exploitation courante :

  • La version SQL Server 2005
  • La version SQL Server 2008 et son clone SQL Server 2008 R2
  • La version SQL Server 2012
  • La version SQL Server 2014

Et , Microsoft annonce déjà la version SQSL Server 2016 !

La version SQL Server 2012 :

Le moteur relationnel :

De nombreuses fonctionnalités ont été ajoutés au langage SQL, telles que

THROW (pour la gestion des exceptions), PARSE (Parsing), TRY_PARSE,DATEFROMPARTS( constructeur de datetime),
FORMAT (Comme dans .Net).

Il est maintenant possible de créer des rôles de serveur paramétrables, ce qui a du sens quand on connait le nombre de prestataires et d’intervenants sur un serveur de base de données.

Les audits de suivi d’activité complètent agréablement les anciennes gestion d’évènements et de profiler : il est possible de tracer dans une table, ou dans un fichier des évènements que l'on veut surveiller au niveau serveur ou base de données.

Les Index Column store : c'est la grande nouveauté de cette version, issue de la technologie Vertipaq. Il s'agit de stocker les données non plus en ligne, mais en colonnes. Ceci donne de très bonnes performances lorsque les données sont redondantes, ce qui est le cas couramment en Business Intelligence (Enterprise Edition).

Les groupes de haute disponibilité (Enterprise Edition) font leur apparition : c'est un mélange des anciennes technologies de mirroring et de réplication. Vous pouvez gérer jusqu'à quatre réplicas secondaires, accessibles en lecture seule, ce qui est un point très fort par rapport aux mirroring. Par ailleurs il est possible de faire du routage des requêtes en lecture seule; c'est le cas des rapports de BI.

 

Integration Services :

De nombreuses améliorations concernant le déploiement ont été développées dans cette version :

. Déploiement sur SSISDB
. Lancement par procédures stockées
. Paramètres de packages
. Connexions de projets

L'outil ETL en arrive donc ainsi à une version tout à fait majeure.

Analysis Services :

Voici une des grandes nouveautés de cette version 2012 : le modèle tabulaire; ce modèle est basé sur la technologie Vertipaq déjà évoquée plus haut. Un nouveau langage de requêtes fait son apparition le Dax.(Enterprise et BI Edition)

Reporting Services :

A part l'intégration de SSDT avec Visual studio, très peu de choses à noter sur cette version : que fait Microsoft ?

La version SQL Server 2014 :

Le moteur relationnel :

C'est dans cette partie que les ingénieurs de Microsoft se sont exprimés le plus :

Gestion des tables en RAM : il s'agit bien sûr d'obtenir les meilleures performances, toutefois au détriment de la durabilité (Enterprise Edition).
Index Column store modifiables : c’était l'une des grandes limites de la version précédente, ces données sont désormais modifiables: attention il ne s'agit toutefois pas d'en faire du transactionnels mais plutôt de gérer des données à variation lente telles que celles que l’on rencontre en BI (Enterprise Edition)..
Extension du cache sur disque SSD : il s'agit de permettre aux différents buffers du moteur relationnel de gérer des extensions sur des disques SSD ce qui permettra bien sûr d'augmenter considérablement la mémoire utilisée.
Transaction à durabilité différée : on privilégie ici les performances au détriment de la fiabilité.
Les groupes de disponibilité Always On passent à 8 réplicas et intègrent maintenant SQL Azure (Enterprise Edition).
Backups et restore sur Azure : il est désormais possible de faire des sauvegardes sur une url pointant dans le cloud.

Gestion incrémentale des statistiques : ceux qui ont à gérer des gros volumes avec beaucoup de mises à jour apprécieront cette fonctionnalité qui est un des problèmes classiques à résoudre par le DBA : les statistiques de l'optimiseur doivent être le plus à jour possible.

Integration Services:

Pas de changement.

Analysis Services:

Pas de changement sauf pour PowerView.

Reporting Services :

Pas de changements sur le support de Chrome.

 

La version SQL Server 2016 :

Au moment où j'écris ce post, cette version est encore en développement (CTP) aussi je ne peux que reporter ici les annonces faite par l'éditeur :

. Stockage extensible sur Azure : l’idée est ici de stocker des données ‘mortes’ dans un lieu de stockage moins couteux que votre baie SAN.
. Chiffrement des données : un pas de plus vers la protection de vos données qui attirent les hackers comme du miel !
. Utilisation massive de la technologie stockage en mode colonne (Vertipaq évoqué plus haut)
. Stockage des requêtes et de leurs plan d'exécution : effectivement les changements de réglages de l’optimiseur peuvent générer des temps de réponse très différents, ce qui est nuisible à une production sereine!
. Sécurité au niveau ligne (Row Level Security) : jusqu’à présent on devait utiliser des vues pour faire cela.
. Amélioration de la gestion des tables In Memory

 

vendredi 22 mai 2015

De l’utilité des clustered index

 

Je constate souvent que les progiciels basés sur SQL Server n’utilisent pas toujours les clustered index …. quand ce n’est pas l’absence totale de clé primaire !

Dommage pour eux, et pour leurs clients car le contexte de clustered index est vital dans le monde SQL Server; pour mémoire une table avec clustered index est rangée dans l’ordre de cet index : c’est donc le moyen le plus efficace de défragmenter une table.

En effet, au fil des insertions, suppressions et mises à jour les données vont bouger et il est de bonne pratique de réorganiser tout cela de temps en temps (un des buts des fameux plans de maintenance)

Voyons cela en pratique :

Créons une table avec un seul index non clustered :

image

Pour ceux qui me connaissent.. le char n’est pas là par hasard mais bien pour montrer une mauvaise pratique!

Puis insérons un jeu de données :

image

 

Il est temps de mesurer la volumétrie de cette table :

image

qui donne :

 

 

image

16 pages dont 15 de données.

Supprimons de manière ‘aléatoire’ des lignes :

image

Et mesurons de nouveau notre volumétrie :

image

16 pages dont 15 de données.

Le nombre est inchangé (les pages ont été compactées en lieu et place)

Voyons une réorganisation :

 

image

Hélas la mesure est la même :

image

16 pages dont 15 de données.

Notre table est maintenant fragmentée !

 

Les mêmes mesures avec un index clustered

 

image

 

image

16 pages dont 15 de données

Passons aux suppressions

image

image

16 pages dont 15 de données

Réorganisons cet index clustered :

image

Et là :

image

10 pages dont 9 de données…

Ceux qui ont des volumes de plusieurs centaines de Go apprécieront la différence !