Optimisation des bases de données MS SQL Server : les requêtes

En théorie, quelle que soit la façon d'écrire une requête, le SGBDR doit être capable de trouver le moyen le plus efficace de traiter la demande, grâce à l'optimiseur. Mais parce qu'il y a loin de la théorie à la pratique, différentes écritures et différents styles de résolution, alliés à la qualité de l'indexation peuvent donner des temps d'exécution très variés.
C'est pourquoi la maîtrise de la conception de requêtes est un des points clefs de la performance d'une base de données bien conçue. Voyons quels en sont les principes basiques.
Dans un cours que je donne aux Arts & Métiers, je montre comment une bonne indexation alliée à la qualité de l'écriture des requêtes, peut faire varier dans une proportion de plus de 300 le temps d'exécution d'une requête. Je commence l'exercice par une demande simple : écrire une requête SQL permettant de répondre à une question basique, mais en prenant soin d'exprimer différentes solutions, même les plus bizarres.

Outre la solution simpliste, certains y arrivent par une union, d'autres avec des sous requêtes, d'autres encore avec des jeux de CASE... L'exécution brute de toutes ces solutions, donne un coût d'exécution allant du simple au triple. La pose d'un premier index trivial ne donne rien, par manque de sélectivité. Après élimination de cette première tentative, la pose d'un nouvel index montre que les requêtes les plus coûteuses au départ deviennent les plus rapides, alors que celles qui étaient les moins coûteuses à l'origine n'ont rien gagné. Un nouvel essai d'indexation remet toutes les requêtes au même rang qu'au départ, chacune gagnant dans la même proportion et plus que l'indexation précédente.

Enfin, la dernière tentative d'indexation étant la bonne, toutes les requêtes bénéficient d'un gain important, mais certaines bien plus que d'autres. Le clou est enfoncé lorsque l'on choisit de dénormaliser avec une vue. Là, le gain devient gigantesque. Il est voisin de 13 000. Mais il ne prend pas en compte l'effort supplémentaire à faire pour les mises à jour (INSERT, UPDATE, DELETE...).

Cet exercice nous apprend trois choses :
• différentes écritures d'une même requête ne donneront pas forcément les mêmes performances, bien que dans l’absolu, ce devrait être le cas [1]
• rien ne sert de poser un index s'il ne sert pas la requête
• une même requête écrite de différentes manières ne bénéficiera pas des mêmes gains lorsque l’on pose un index J’ajouterai que tout ceci évolue en fonction de la volumétrie des données et des données mêmes !

Tant est si bien qu’il est difficile de trouver de prime abord ce qu’est l’écriture d’une bonne requête. Affirmons cependant qu’une bonne requête est une requête qui sait tirer partie du moteur de requête pour le forcer à calculer un plan de requête dont les étapes sont les plus courtes à traiter.

[1] En fait, nous croyons souvent et naïvement que certaines écritures de requêtes sont identiques. Mais nous oublions souvent l’influence du marqueur NULL, dont le comportement particulier dans différents prédicats oblige le moteur SQL à des constructions parfois fort différentes.

Actu Windows Semaine 29

XML et SQL SERVER

Les informations échangées entre les différents systèmes, les différentes applications le sont de plus en plus auSQL Server, se sont adaptées. En effet, SQL Server à, entre autre, pour objectif de faciliter le travail avec les données. Pour cela, SQL Server dispose d'un ensemble d'instructions pour extraire des informations relationnelles au format XML ou bien l'opération inverse qui consiste à lire des données au format XML afin de stocker les informations dans une structure relationnelle classique.

Ces 2 étapes sont nécessaires et permettent de confier au moteur de base de données la plupart des transformations depuis ou vers le format XML à SQL Server. Mais SQL Server propose plus en offrant la possibilité de créer des colonnes de type XML. En intégrant directement les informations XML dans la structure relationnelle classique, SQL Server offre plus de souplesse en terme de stockage. Ce type XML est bien plus qu'un simple champ texte car SQL Server rend possible l'indexation des colonnes de type XML mais également la mise à jour, l'ajout et la suppression de données dans le document XML lui-même.

Ce sont ces trois fonctionnalités (extraire les informations au format XML, importer des données XML, stocker des données au format XML) de SQL Server qui sont exposées ci-dessous.

Le Guwiv présente Windows SteadyState

Le Guwiv présente un outil méconnu de Windows Vista, Windows SteadyState. L’équivalent de Shared Computer Toolkit sous Windows XP permet de sécuriser gratuitement un ordinateur accessible en libre service comme un PC dans un cybercafé par exemple. Ce produit fonctionne aussi avec Windows XP.

Le site revient aussi sur le Tech Ed 2008 et met à la disposition des internautes, les diaporamas PowerPoint de la présentation de J. Peter Bruzzese (MCSE et MCITP) sur les trucs et astuces de Vista, tels que les raccourcis clavier ou le réglage des différents menus.

Windows Server 200 Hyper-V

La firme de Redmond vient de rendre disponible au téléchargement le moteur de virtualisation Windows Server 200 Hyper-V. 

Véritable extension de Windows Server 2008, l’hyperviseur doit permettre aux entreprises de faire tourner plusieurs systèmes d’exploitation sur une seule machine physique.

Personnalisation de l’affichage dans Active Directory

Dès sa conceptionActive Directory est une base de données constituée d’attributs et de classes. Une classe est un regroupement d’attributs constituant un objet. Le schéma, qui est le squelette de la base de données Active Directory, n’est pas figé, celui-ci peut être facilement étendu. Cependant, beaucoup d’administrateurs se demandent comment afficher un attribut qui aurait été ajouté au schéma Active Directory. Voici comment personnaliser cet affichage.

Périmètre de sécurité

Voilà encore quelques années, une composante du périmètre de sécurité, lequel n’est qu’un élément de la sécurité. Voici les points importants d’un périmètre de sécurité, et une checklist, utiles pour planifier ledit périmètre dans un environnement moderne, de plus en plus connecté.

La haute disponibilité à  votre portée dans Exchange 2007

Exchange Server 2007, vous pouvez trouver des solutions qui ne vont pas « faire exploser » votre budget et n’exigeront pas de votre équipe Exchange qu’elle passe un doctorat en clustering. Nous allons commencer ici notre examen des solutions de haute disponibilité d’Exchange 2007 par une présentation générale. Dans les prochains articles, nous explorerons plus en détail ce sujet avec la configuration de la réplication locale en continu (LCR) et de la réplication continue en cluster (CCR) dans Exchange 2007.

actu Windows semaine 26

La société de conseil et de services spécialisée en Business Intelligence et technologies objet et Web, Homsys Group, a atteint le niveau de certification Gold Certified Partner dans le programme de partenariat Microsoft. Ce niveau d’engagement leur donne un accès privilégié aux ressources et support techniques, commerciaux et marketing de la firme de Redmond. Ils […]

Windows Server 2003 SP2

La dernière contribution de Microsoft à la période de fêtes fut Windows Server 2003 Service Pack 2 (SP2). Comme les packs de service Microsoft nous y ont habitués, l’administrateur IT.

La Cadim relaie l’engagement de Microsoft sur l’interopérabilité.

La CADIM propose un lien vers le communiqué de presse de Microsoft.

Le DUG aux TechDays 2008

Le DUG a annoncé sa présence au salon des Microsoft TechDays 2008

Devenez incolable sur Windows Server 2008

Le GUWISE vous propose de devenir incollable sur le nouveau Windows Server 2008, à travers le site Technet. Au programme : Webcast, présentations, et téléchargements de la « Release Candidate ».

Connexion sécurisé intersites

L’un des blogs d’ISAServer (celui de Thierry Nyembo) évoque les problèmes d’incompatibilité lors de la mise en place d’une connexion sécurisé intersites.

Webcast consacré à  la migration sur Exchange 2007

Le GU-CU vous propose d’assister à un webcast le jeudi 20 mars. Cette réunion web sera animée par le MVP Laurent Teruin qui reviendra sur la migration d'Exchange Server 2000 à Exchange Server 2007. Plus d’informations sur cette page

Du nouveau pour le GuVirt !

Ouvert début juin, le site du GuVirt a pour objectif d’atteindre une centaine de membres d’ici un an. De plus, une réunion sera organisée à la rentrée prochaine et portera sur Hyper-V, l’hyperviseur de Microsoft.

Plus récemment, un guide sur les « Best Practices » pour la virtualisation avec Active Directory vient d’être mis en ligne sur le site.

GUE : « faire tourner Exchange sur Windows Server 2008 » !

Le briefing d'été du L'article complet à cette adresse.

Congrès européen du Common

Le congrès européen du Common s’est tenu à Barcelone du 17 au 20 mai 2008. Plus d’information sur cette page

Tout sur IE8

Le Guwiv propose de vous préparer à Internet Explorer 8 et vous donne un ensemble de liens pour tout savoir sur le prochain explorateur.
Dans les autres news, le site revient sur la journée du 26 mai dédiée à la « journée Windows » et présente la Beta de SysInternals Live qui permet d'exécuter les outils SysInternals directement depuis le Web.

Réunion du GUSS : le décisionnel à  l’honneur !

Fin mai, s’est tenue la dernière réunion du GUSS (Groupe des utilisateurs de SQL Server) à Paris. Cette rencontre trimestrielle, qui est toujours l’occasion d’échange d’informations techniques, a rencontré un très vif succès, puisque plus de 50 personnes étaient présentes dans la salle.

Cette réunion a mis à l’honneur un sujet d’actualité : le décisionnel ! D’abord un petit retour sur le « pourquoi » du décisionnel, avant de s’engouffrer au fur et à mesure dans son évolution et l’offre de Microsoft. Cette session – panorama découverte de la Business Intelligence avec SQL Server - fut animée par Arian Papillon, président du GUSS et Sébastien Madar, Mcnext / GUSS.
L’offre décisionnelle de Microsoft s’est fortement développée dès 1999.

Les objectifs : mieux piloter l’activité en disposant d’une information simple, ciblée, pertinente, disponible, synthétique et détaillée ; répondre aux contraintes qualité en fournissant des indicateurs ; enfin, rendre l’information accessible grâce à des accès variés et en permettant une certaine autonomie !

Quant à son évolution, on peut parler de 3 générations. La 1ère génération, avec SQL Server 7.0, offre OLAP Services, un nouvel ETL (DTS) et une nouvelle philosophie « le décisionnel pour tous ». La 2ème génération, avec SQL Server 2000, s’améliore : OLAP Services devient Analysis Services (ajout du datamining) et Reporting Services (add-on) est téléchargeable. Enfin, la 3ème génération, avec SQL Server 2005 dans un premier temps, offre une refonte  d’Analysis Services, plus de datamining, une nouvel ETL : SSIS, Reporting Services intégré désormais à l’offre, un nouvel environnement de développement (Visual Studio 2005) et de nouveaux outils de restitution. Avec l’arrivée de SQL Server 2008, cette génération se modifie, on parle de « continuité et amélioration » de SQL Server 2005 avec une amélioration des outils et des performances pour SSAS, SSRS, et une nouvelle interface orientée utilisateur.

Le décisionnel s’installe donc en force ! La business intelligence s’adapte ainsi aux rôles et aux missions des collaborateurs (contributeurs, analystes, management). Chacun a droit d’avoir des indicateurs (alertes). Pour cela, le reporting structuré a sa place  et représente 80% des besoins d’accès à l’information dans l’entreprise. Les rapports sont donc constitués selon des règles et formats prédéfinis.

Enfin, il faut permettre aux utilisateurs de concevoir eux-mêmes des rapports simples. La navigation dans les données n’est pas en reste, grâce à OLAP, et les mesures (quantité, CA, marges, délais..) sont ainsi croisées avec les paramètres clients, temps, produits, géographie. La datamining prend également toute sa valeur : ce processus d’analyse d’un grand volume de données pour en extraire les tendances ou corrélations avec les différents algorithmes est primordial ! Quant aux tableaux de bord et portail décisionnel, le choix s’affine et est bien réel (indicateurs de performances, plusieurs sources, ERP, CRM, OLTP…).
Concevoir une base de données dédiée à l’analyse est essentielle ! La base de données OLTP est mal adaptée à l’analyse, e