Interrogation du schéma d’une instance de DB
MS a introduit, avec SQL-Server 2005 et les schémas de DB, de nouveaux vecteurs d’interrogation de la structure d’une instance de DB. Voici un petit état de lieu.

Les vues aux normes SQL2

Chaque instance est dorénavant affublée d’un nouveau schéma : INFORMATION_SCHEMA. Il permet d’interroger les informations en utilisant un point d’entrée central, puis en spécifiant l’information recherchée (tables, views, routines, ..), avec une syntaxe « utilisateur »

SELECT
    *
FROM
    INFORMATION_SCHEMA.TABLES

Les vues systèmes (DMV Dynamic Management Views)

Un deuxième schéma est automatiquement ajouté à chaque instance : SYS. L’interrogation se fait via des vues fournies par MS. Le niveau d’abstraction est moindre, mais ne nombre d’informations accessibles est supérieur.

SELECT
    *
FROM
    SYS.OBJECTS T1
WHERE
    T1.type = ‘U’

Les procédures stockées spécialisées

MS fournit un certain nombre de procédures stockés permettant d’interroger les informations, tels que sp_tables, sp_columns, …

EXEC sp_tables

Les tables systèmes

L’interrogation se fait cette fois au niveau le plus bas disponible. Les données sont brutes et relativement difficile à interpréter …

SELECT
    *
FROM
    SYSOBJECTS T1
WHERE
    T1.type = ‘U’

Conclusion

Les quatre méthodes de query de l’information retournent le même résultat, soit la liste des tables d’une DB.  Mais alors quelle méthode utiliser en priorité ? La réponse, et l’explication est relativement simple :
1)    Les vues aux normes SQL2
2)    Les vues systèmes (DMV Dynamic Management Views)
3)    Les procédures stockées spécialisées
4)    Les tables systèmes
Et pourquoi ?
1)    Vous l’aurez compris, chaque niveau encapsule le niveau suivant.  Les vues aux normes SQL2 sont donc plus faciles à utiliser car elles « cachent » la complexité  du bas niveau.
2)    Seuls les vues et les SPs sont compatibles de version en version. Il est donc  évident de les préférer pour des raisons de compatibilité.

Thursday, December 17, 2009 3:44:32 PM (GMT Standard Time, UTC+00:00)      Comments [0]  SQL-Server  | SQL-Server 2005

Mappage des types SQL et CLR (LINQ)

Un article msdn expliquant le mappage utilisé par LINQ entre les types SQL et CLR.

La partie la plus intéressant reste la matrice montrant quels types SQL est compatible avec quels type CLR. Le mappage n'est évidement pas de type 1:1.

Tuesday, February 24, 2009 12:51:08 PM (GMT Standard Time, UTC+00:00)      Comments [0]  .NET 3.5 | LINQ | SQL-Server  | SQL-Server 2005

Troubleshhoting problèmes avec Merge Replication

Récement, j'ai experimenté quelqeus soucis avec un POC Merge Replication :

80004005 : Failure to connect to SQL Server

Le Merge Replication n’abouti pas. L’erreur ‘OpenDB failed getting pub version 28627’ est loguée dans le fichier SQLCESA30.LOG.

Liste des contrôles à effectuer :

• S’assurer que le serveur de réplication fonctionne correctement :
  http://nomduserveur/ssce/sqlcesa30.dll?diag
• Contrôler dans le fichier log (C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\SSCE\SQLCESA30.LOG) que l’entrée ‘Hr=00000000 SQLCESA30.DLL loaded 0’

Vérifier le transport en effectuant des ‘ping’ entre Publisher, Distributor &Replication.

• Si un des serveur ne répond pas, vérifier que le firewall est à off.

Vérifier que les différents serveurs (Publisher, Distributor & Replication) communiquent correctement :

• depuis le serveur de réplication :
  o osql –S PUBLISHER –E
  o osql –S DISTRIBUTOR –E
• etc sur les autres serveurs ...

Si la communication ne fonctionne pas, vérifier dans ‘SQL Server Surface Area Configuration’ que les connections locales et distantes sont autorisées.

Wednesday, January 30, 2008 3:43:54 PM (GMT Standard Time, UTC+00:00)      Comments [0]  Merge Replication | SQL-Server 2005 | Windows Mobile

SQL-Server et changement du nom de machine

Dans le cadre d’un POC (Proof Of Concept), j’ai décidé de changer le nom du serveur de base de donnée afin que ce laboratoire soit ‘auto-documenté’ (c’est plus claire quand les machines s’appellent ‘Directory Services’, ‘Publisher’ au lien de ‘WIN-1234’ et ‘WIN-1235 …). Cette modification intervient APRES l’installation de SQL-Server.

 

Le changement de nom s’effectue très simplement depuis l’administration de l’ordinateur (Poste de travail/ Gérer). Jusqu’ici, tout va bien.

 

Lors de la première connexion à la base de donnée via SQL-Server Management Studio, la mire propose toujours l’ancien nom de l’ordinateur. Pas de souci, il suffit de remplacer celui-ci par son nouveau nom et la connexion s’effectue correctement.

 

Un problème survient lorsque l’on désir gérer la réplication. En effet, pour une mystérieuse raison, la réplication (Replication.Utilities) cherche toujours à accéder au serveur avec l’ancien nom …

 

 

 

Un rapide coup d’œil sur les tables système permet de trouver la source du souci :

select * from sys.servers

Retourne des informations du serveur SGDB, et retourne surtout dans la colonne ‘NAME’ l’ancien nom de la machine L

 

Le souci sera rapidement résolut en supprimant le serveur et en le recréant à l’aide des procédures stockées suivantes :

sp_dropserver ‘WIN-1235’

sp_addserver @server=‘Publisher’, @local=’local’

 

Et tout rentre dans l’ordre.

Thursday, January 24, 2008 1:36:14 PM (GMT Standard Time, UTC+00:00)      Comments [0]  SQL-Server 2005 | Tips | Merge Replication

Serveur lié SQL-Server 2005 64 Bit / Oracle 9 32 Bit

Après quelques heures de recherche, voici la procédure à suivre afin de créer un linked server sous SQL-Server 2005 sous Windows Server 2003 x64 Edition et Oracle 9 sous Windows Server 2003 (32 Bit).

 

Par défaut, le provider pour Oracle ne se trouve pas dans l’Object Explorer de SQL Server Management Studio ( Server/ Server Object / Linked Servers / Providers). N’installez pas le client 32 bit en espérant le voir apparaître. En effet, SQL-Server étant 64 bit, seuls les providers 64 bit sont visibles …

 

Le premier pas est donc d’installer le client Oracle 64 bit (soit la version Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (x64)) et l’installer.

 

Il faut ensuite installer l’Oracle Data Access Componant (ODAC pour les intimes), toujours en version 64 bit (Oracle10g Release 2 ODAC (64-bit) 10.2.0.3 for Windows x64)

 

Redémarrer SQL-Server afin de voir le provider dans la lise des Providers (OraOLEDB.Oracle).

 

Configurer le provider afin d’autoriser l’inprocess (Server/ Server Object / Linked Servers / Providers / OraOLEDB.Oracle / Properties)

 

Configuer le TNS si cela n’est déjà fait (Démarrer / programs / Oracle - ODACHome1 / Outils de configuration et de migration / Net Manager)

 

On peut maintenant ajouter le server lié  (merci Chuck P):

EXEC sp_addlinkedserver   'tnsName',  'Oracle',  'OraOLEDB.Oracle',  'tnsName'

EXEC sp_addlinkedsrvlogin 'tnsName', false,null, 'username', 'password'

 

Dernier écueil …  Si lors du test de cette nouvelle connexion, le serveur vous retourne le message suivant :

ORA-12705 - Invalid or Unknow NLS parameter value specified

Il vous faut encore éditer la base de registre et changer les clé ‘NLS_LANG’ d’Oracle et leurs assigner la valeur ‘AMERICAN_AMERICA.WE8ISO8859P1’ . Tout ceci est documenté chez Oracle ici : ora-12705 - Invalid or unknown NLS parameter value specified

 

Voilà, j’espère que cela fera gagner du temps a certains …

 

Stéphane.

Tuesday, November 13, 2007 4:20:25 PM (GMT Standard Time, UTC+00:00)      Comments [0]  Linked Server | Oracle | SQL-Server 2005

SQL Tips #7

Requêtes ayant été les plus exécutées

Les DMV sys.dm_exec_query_stats, en association avec sys.dm_exec_sql_text, permet de connaître les requêtes ayant été les plus exécutées.

SELECT TOP 50    
        SUM(T1.execution_count),
        T2.text
FROM  
    sys.dm_exec_query_stats T1 
CROSS APPLY sys.dm_exec_sql_text(T1.plan_handle) T2    
GROUP BY 
        T1.plan_handle,
        T2.text
ORDER BY 
    SUM(T1.execution_count) 
DESC

Thursday, October 25, 2007 10:50:55 AM (GMT Daylight Time, UTC+01:00)      Comments [0]  SQL-Server 2005 | Tips

SQL Tips #6

Requêtes ayant consommées le plus de cycle I/O

Les DMV sys.dm_exec_query_stats, en association avec sys.dm_exec_sql_text, permet de connaître les requêtes ayant consommées le plus de cycle I/O.

SELECT TOP 50    
        SUM(T1.total_physical_reads + T1.total_logical_reads + T1.total_logical_writes),
        T2.text
FROM  
    sys.dm_exec_query_stats T1 
CROSS APPLY sys.dm_exec_sql_text(T1.plan_handle) T2    
GROUP BY 
        T1.plan_handle,
        T2.text
ORDER BY 
    SUM(T1.total_physical_reads + T1.total_logical_reads + T1.total_logical_writes ) 
DESC

 

Thursday, October 25, 2007 10:49:40 AM (GMT Daylight Time, UTC+01:00)      Comments [0]  SQL-Server 2005 | Tips

SQL Tips #5

Requêtes ayant consommées le plus de cycle CPU

Les DMV sys.dm_exec_query_stats, en association avec sys.dm_exec_sql_text, permet de connaître les requêtes ayant consommées le plus de cycle CPU.

SELECT TOP 50
    SUM(T1.total_worker_time), 
    T2.text
FROM  
    sys.dm_exec_query_stats T1 
CROSS APPLY sys.dm_exec_sql_text(T1.plan_handle) T2    
GROUP BY 
        T1.plan_handle,
        T2.text
ORDER BY 
        SUM(T1.total_worker_time) 
DESC

Thursday, October 25, 2007 10:48:47 AM (GMT Daylight Time, UTC+01:00)      Comments [0]  SQL-Server 2005 | Tips

SQL Tips #4

Requêtes ayant souffert de blocages

Les DMV sys.dm_exec_query_stats, en association avec sys.dm_exec_sql_text, permet de connaître les requêtes ayant le plus souffert de blocages (le temps d’exécution est supérieur au temps ‘travaillé’, signifiant que la requête à été victime temporairement d’un blocage).

SELECT TOP 50    
        SUM(T1.total_elapsed_time - T1.total_worker_time),
    T2.text
FROM  
    sys.dm_exec_query_stats T1 
CROSS APPLY sys.dm_exec_sql_text(T1.plan_handle) T2    
GROUP BY 
        T1.plan_handle,
        T2.text
ORDER BY 
        SUM(T1.total_elapsed_time - T1.total_worker_time ) 
DESC

Thursday, October 25, 2007 10:47:21 AM (GMT Daylight Time, UTC+01:00)      Comments [0]  SQL-Server 2005 | Tips

SQL Tips #3

Les DMV dm_db_missing_index_* permettent de déterminer les indexes manquants. Ces informations sont basées sur l’analyse de l’exécution des requêtes depuis le démarrage de SQL-Server 2005.

Quatre DMV permettent d’obtenir des informations, d’un point de vue générale  jusqu’au détail (détail de colonnes à indexer).

La hiérarchie est la suivante :
dm_db_missing_index_group_stats          Information générale
-> dm_db_missing_index_groups             Information sur un groupe d’indexes
     -> dm_db_missing_index_details        Information sur un index
          -> dm_db_missing_index_columns Information des colonnes d’un index

Le calcul de la priorité (l’importance) de création d’un index est basé sur trois paramètres :
1. Le coût
2. l’impacte
3. le nombre de lecture

En effet, créer un index sur une table n’ayant qu’un index primaire aura un grand impact sur la performance. Par contre, si cette table n’est accédée qu’un fois pas année, la création de cet index ne devrait pas être un priorité. Mieux vaut alors insérer un index sur une table accédée de miliers de fois par minutes, même si l’impacte par requête est faible.

La requête suivante indique les indexes manquant a créer, par ordre de priorité (selon le calcule ci-dessus) :

SELECT 
    T1.avg_total_user_cost * T1.avg_user_impact * (T1.user_seeks + T1.user_scans) AS 'Priorite', 
        T3.*
FROM 
    sys.dm_db_missing_index_group_stats T1
INNER JOIN sys.dm_db_missing_index_groups T2 
    ON T2.index_group_handle = T1.group_handle
INNER JOIN sys.dm_db_missing_index_details T3 
        ON T3.index_handle = T2.index_handle
WHERE
        T3.database_id = 8 – A remplacer par l’ID de la base désirée
ORDER BY 
    avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)
DESC;

Note :
Le résultat des DMV est le reflet de la base lors de l’execution des DMV. Lors de la création d’indexes basé sur ces vues, prendre soins de sauvegarder les informations. En effet, suite à la création d’un index, les DMV vont réévaluer les informations.

Wednesday, October 24, 2007 2:36:44 PM (GMT Daylight Time, UTC+01:00)      Comments [0]  SQL-Server 2005 | Tips

SQL Tips #2

Un index est fragmenté lorsque l’ordre logique des informations stockées dans les pages (basée sur les clés de l’index) ne correspondent plus à l’ordre physique des informations stockées dans les fichiers de données.

SQL-Server 2005 offre deux possibilités :
- La réorganisation de l’index
   S’applique lorsque le taux de fragmentation de l’index se situe entre 5% et 30%.
- La reconstruction de l’index
   S’applique lorsque le taux de fragmentation de l’index est supérieur à  30%.

La DMV sys.dm_db_index_physical_stats nous renseigne sur le taux de fragmentation. La requête suivante liste tous les indexes d’un serveur (pour lister uniquement une base, changer le premier paramètre de la DMV), dont le taux de fragmentation est supérieur à 5%.

SELECT 
    T3.Name AS Table_Name, 
    T2.Name AS Index_Name,
    T1.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(null,null,null,null,null) AS T1 
INNER JOIN  sys.indexes T2 
    ON  T1.OBJECT_ID = T2.OBJECT_ID 
    AND T1.INDEX_ID = T2.INDEX_ID 
INNER JOIN  sys.tables T3 
    ON T3.OBJECT_ID = T2.OBJECT_ID 
WHERE 
    T1.avg_fragmentation_in_percent > 5 
    AND T2.Name IS NOT NULL 
ORDER BY 
    T1.avg_fragmentation_in_percent 
DESC

Wednesday, October 24, 2007 1:22:08 PM (GMT Daylight Time, UTC+01:00)      Comments [0]  SQL-Server 2005 | Tips

SQL Tips #1

Un index devient inutiles lorsque le nombre de mise à jour de l’index (update) est supérieur au nombre fois que cet index est utilisé pour une lecture (seeks, scans & lookup). Le moteur passe donc plus de temps à le maintenir à jour qu’il n’est réellement utilisé … C’est comme maintenir un annuaire téléphonique à jour alors que les abonnées utilisent d’autre moyen (carnet d’adresse local, …) pour se contacter.

La DMV sys.dm_db_index_usage_stats nous donne ces informations :

SELECT
     user_updates AS '#ECRITURE', 
    user_seeks + user_scans + user_lookups AS '#LECTURE'
FROM 
        sys.dm_db_index_usage_stats
WHERE
        user_updates > (user_seeks + user_scans + user_lookups)

Wednesday, October 24, 2007 1:05:25 PM (GMT Daylight Time, UTC+01:00)      Comments [0]  Tips | SQL-Server 2005