Groupes de fichiers SQL Server - Localisation des objets (sysindexes)

Introduction

SQL Server introduit comme Oracle la notion de tablespaces avec les groupes de fichiers. Toutefois les méthodes pour localiser les objets sur les groupes de fichiers sont rares. Cet article montre les requêtes simples qui permettent de localiser les objets (tables, tables avec index clusterisé, indexes non clusterisés, indexes texte) sur les groupes de fichiers.

La procédure stockée sp__dba_getobjects simplifie la localisation des objets.

Contexte

Rappel sur les groupes de fichiers

La commande CREATE DATABASE ou ALTER DATABASE de Microsoft SQL Server permet d'introduire la notion de tablespace avec les groupes de fichiers (ou filegroups).

Un groupe de fichiers peut être assimilé à un tablespace et peut correspondre à un ou plusieurs devices.

Par défaut, le groupe de fichiers PRIMARY est automatiquement créé lors de la création d'une nouvelle base de données.

Exemple pratique

Dans l'exemple pratique, une base de données cgcam est créée sur plusieurs groupes de fichiers dont les caractéristiques sont résumées ci-dessous :

Groupe de fichiers Fichiers Taille min/max - Croissance
PRIMARY Primaire data_cgcam_pri_01.mdf data_cgcam_pri_02.mdf 10 Mb / 50 Mb - 15%
INDX Indexes non clusterisés data_cgcam_ind_01.mdf data_cgcam_ind_02.mdf 10 Mb / 50 Mb - 15%
HISTO Tables d'historique data_cgcam_his_01.mdf data_cgcam_his_02.mdf 10 Mb / 50 Mb - 15%
HISTO_INDX Indexes non clusterisés des tables d'historique data_cgcam_hsidx_01.mdf data_cgcam_hsidx_02.mdf 10 Mb / 50 Mb - 15%

La syntaxe de création de la base est donnée ci-dessous :


CREATE DATABASE cgcam
ON PRIMARY
( NAME=data_cgcam_pri_01,
  FILENAME='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\Data\data_cgcam_pri_01.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=15%),
( NAME=data_cgcam_pri_02,
  FILENAME='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\Data\data_cgcam_pri_02.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=15%),
FILEGROUP INDX
( NAME=data_cgcam_ind_01,
  FILENAME='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\Data\data_cgcam_ind_01.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=15%),
( NAME=data_cgcam_ind_02,
  FILENAME='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\Data\data_cgcam_ind_02.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=15%),
FILEGROUP HISTO
( NAME=data_cgcam_his_01,
  FILENAME='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\Data\data_cgcam_his_01.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=15%),
( NAME=data_cgcam_his_02,
  FILENAME='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\Data\data_cgcam_his_02.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=15%),
FILEGROUP HISTO_INDX
( NAME=data_cgcam_hsidx_01,
  FILENAME='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\Data\data_cgcam_hsidx_01.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=15%),
( NAME=data_cgcam_hsidx_02,
  FILENAME='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\Data\data_cgcam_hsidx_02.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=15%)
LOG ON
( NAME=log_cgcam_01,
  FILENAME='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\Data\log_cgcam_01.ldf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=15%)

sysfilegroups, sp_helpfilegroup, filegroup_name et filegroup_id

Les informations sur les groupes de fichiers sont stockées dans la table système sysfilegroups :

select groupid, groupname from sysfilegroups
go

groupid  groupname
-------  ---------------------------------------------------------
3        HISTO
4        HISTO_INDX
2        INDX
1        PRIMARY

La procédure stockée système sp_helpgroup donne le nombre de devices associé à un groupe de fichiers ainsi que le groupid d'un groupe de fichiers :

execute sp_helpfilegroup
go
groupname  groupid  filecount
---------  -------  -----------
PRIMARY        1        2
INDX           2        2
HISTO          3        2
HISTO_INDX     4        2

La fonction filegroup_id retourne l'identifiant groupid d'un groupe de fichiers à partir de son nom :

select filegroup_id('HISTO')

La fonction filegroup_name retourne le nom d'un groupe de fichiers à partir d'un identifiant groupid :

select filegroup_name(3)

Localiser des objets sur des groupes de fichiers

La table sysindexes recense tous les objets de type table, indexes et champs texte dans une base de données. La table sysindexes contient par ailleurs la colonne groupid qui permet de réaliser une jointure avec la table système sysfilegroups, ce qui permet de déterminer aisément la localisation des tables, indexes et champ texte sur les groupes de fichiers.

Ce paragraphe montre comment localiser ces objets sur les groupes de fichiers en fonction de leur type : tables sans indexe clusterisé, tables avec index clusterisé, indexes non clusterisés et champs texte.

Localiser les tables et les tables avec index clusterisé

Toutes les tables avec ou sans index clusterisé peuvent être repérées et localisées dans les groupes de fichiers grâce aux tables sysindexes, sysfilegroups et sysobjets.

La colonne indid vaut 0 pour les tables sans index clusterisé et 1 pour les tables avec index clusterisé dans la tablesysindexes :

SELECT a.name,
       CASE a.indid
           WHEN 255 THEN 'TEXT,nTEXT,IMAGE'
           WHEN 0 THEN 'TABLE'
           WHEN 1 THEN 'CLUSTERED INDEX'
           ELSE 'NONCLUSTERED INDEX'
       END AS type,
       object_name(a.id) as 'Object Name',
       c.groupname

FROM   sysindexes a,
       sysobjects b,
       sysfilegroups c

WHERE  a.id = b.id
  AND  b.type = 'U'
  AND  b.name != 'dtproperties'
  AND  a.groupid = c.groupid
  AND  a.indid IN (0,1)
SELECT a.name,
       CASE a.indid
           WHEN 255 THEN 'TEXT,nTEXT,IMAGE'
           WHEN 0 THEN 'TABLE'
           WHEN 1 THEN 'CLUSTERED INDEX'
           ELSE 'NONCLUSTERED INDEX'
       END AS type,
       object_name(a.id) as 'Object Name',
       filegroup_name(a.groupid) as 'groupname'

FROM   sysindexes a,
       sysobjects b


WHERE  a.id = b.id
  AND  b.type = 'U'
  AND  b.name != 'dtproperties'
  AND  a.indid IN (0,1)

Exemple :


Name            Type              Object Name      groupname
-------------   ---------------   --------------   -------------------
T_CGC           TABLE             T_CGC            PRIMARY
T_CGC_BLOB      TABLE             T_CGC_BLOB       PRIMARY
T_CGC_HISTO     TABLE             T_CGC_HISTO      HISTO
T_CGCAM         TABLE             T_CGCAM          PRIMARY
T_CGCAM_HISTO   TABLE             T_CGCAM_HISTO    HISTO
IDX_T_CGCU      CLUSTERED INDEX   T_CGCU           PRIMARY

Localiser les champs texte

Tous les champs texte peuvent être repérés et localisés dans les groupes de fichiers grâce aux tables sysindexes, sysfilegroups et sysobjets. La colonne indid vaut 255 pour les champs textes :

SELECT a.name,
       CASE a.indid
           WHEN 255 THEN 'TEXT,nTEXT,IMAGE'
           WHEN 0 THEN 'TABLE'
           WHEN 1 THEN 'CLUSTERED INDEX'
           ELSE 'NONCLUSTERED INDEX'
       END AS type,
       object_name(a.id) as 'Object Name',
       c.groupname

FROM   sysindexes a,
       sysobjects b,
       sysfilegroups c

WHERE  a.id = b.id
  AND  b.type = 'U'
  AND  b.name != 'dtproperties'
  AND  a.groupid = c.groupid
  AND  a.indid = 255
SELECT a.name,
       CASE a.indid
           WHEN 255 THEN 'TEXT,nTEXT,IMAGE'
           WHEN 0 THEN 'TABLE'
           WHEN 1 THEN 'CLUSTERED INDEX'
           ELSE 'NONCLUSTERED INDEX'
       END AS type,
       object_name(a.id) as 'Object Name',
       filegroup_name(a.groupid) as 'groupname'

FROM   sysindexes a,
       sysobjects b
       
       
WHERE  a.id = b.id
  AND  b.type = 'U'
  AND  b.name != 'dtproperties
  AND  a.indid = 255

Exemple :


Name            Type              Object Name      groupname
-------------   ---------------   --------------   -------------------
tT_CGC          TEXT,nTEXT,IMAGE  T_CGC            PRIMARY
tT_CGC_HISTO    TEXT,nTEXT,IMAGE  T_CGC_HISTO      HISTO
tT_CGCU         TEXT,nTEXT,IMAGE  T_CGCU           PRIMARY

Localiser les indexes non clusterisés

Tous les indexes non clusterisés (nonclustered indexes) peuvent être repérés et localisés dans les groupes de fichiers grâce aux tables sysindexes, sysfilegroups et sysobjet. La colonne indid a une valeur comprise entre 2 et 255 pour les indexes non clusterisés :

SELECT a.name,
       CASE a.indid
           WHEN 255 THEN 'TEXT,nTEXT,IMAGE'
           WHEN 0 THEN 'TABLE'
           WHEN 1 THEN 'CLUSTERED INDEX'
           ELSE 'NONCLUSTERED INDEX'
       END AS type,
       object_name(a.id) as 'Object Name',
       c.groupname

FROM   sysindexes a,
       sysobjects b,
       sysfilegroups c
       
WHERE   a.id = b.id
  AND   b.type = 'U'
  AND   b.name != 'dtproperties'
  AND   a.groupid = c.groupid
  AND   a.indid not in (0,1,255)
SELECT a.name,
       CASE a.indid
           WHEN 255 THEN 'TEXT,nTEXT,IMAGE'
           WHEN 0 THEN 'TABLE'
           WHEN 1 THEN 'CLUSTERED INDEX'
           ELSE 'NONCLUSTERED INDEX'
       END AS type,
       object_name(a.id) as 'Object Name',
       filegroup_name(a.groupid) as 'groupname'

FROM   sysindexes a,
       sysobjects b


WHERE  a.id = b.id
  AND  b.type = 'U'
  AND  b.name != 'dtproperties'
  AND  a.indid not in (0,1,255)

Exemple :


Name                 Type                 Object Name      groupname
-------------        ------------------   --------------   -------------------
IDX_T_CGC            NONCLUSTERED INDEX   T_CGC            PRIMARY
IDX_T_CGC_HISTO      NONCLUSTERED INDEX   T_CGC_HISTO      HISTO_INDX
IDXC_T_CGCAM         NONCLUSTERED INDEX   T_CGCAM          INDX
IDXC_T_CGCAM_HISTO   NONCLUSTERED INDEX   T_CGCAM_HISTO    HISTO_INDX

Procédure stockée sp__dba_getobjects

Usage

La procédure sp__dba_getobjects permet d'obtenir rapidement la localisation des objets sur les groupes de fichiers.

Syntaxe :

execute sp__dba_getobjects [ @objname=<objname> ],
              [ @objtype=<TABLE | CLUSTERED INDEX | NONCLUSTERED INDEX | TEXT,nTEXT,IMAGE> ],
              [ @filegroup=<filegroupname> ]

Exemple :

Pour retrouver les tables sur le groupe de fichiers HISTO

execute sp__dba_getobjects @objtype='TABLE', @filegroup='HISTO'
go

name            type    Object Name     groupname
-------------   -----   -------------   ---------
T_CGC_HISTO     TABLE   T_CGC_HISTO     HISTO
T_CGCAM_HISTO   TABLE   T_CGCAM_HISTO   HISTO

Pour retrouver les objets sur le groupe de fichiers HISTO

execute sp__dba_getobjects @filegroup='HISTO'
go

name            type               objectname      groupname
-------------   ----------------   -------------   ---------
T_CGC_HISTO     TABLE              T_CGC_HISTO     HISTO
tT_CGC_HISTO    TEXT,nTEXT,IMAGE   T_CGC_HISTO     HISTO
T_CGCAM_HISTO   TABLE              T_CGCAM_HISTO   HISTO
go

Pour retrouver les indexes non clusterisés sur tous les groupes de fichiers

execute sp__dba_getobjects @objtype='NONCLUSTERED INDEX'
go

name                 type                 objectname      groupname
------------------   ------------------   -------------   ---------
IDX_T_CGC_HISTO      NONCLUSTERED INDEX   T_CGC_HISTO     HISTO_INDX
IDXC_T_CGCAM_HISTO   NONCLUSTERED INDEX   T_CGCAM_HISTO   HISTO_INDX
IDX_T_CGC            NONCLUSTERED INDEX   T_CGC           INDX
IDXC_T_CGCAM         NONCLUSTERED INDEX   T_CGCAM         INDX

Code source sp__dba_getobjects

    use master
    go
    
    IF OBJECT_ID('sp__dba_getobjects') IS NOT NULL
    BEGIN
        DROP PROCEDURE sp__dba_getobjects
    END
    go
    
    CREATE PROCEDURE sp__dba_getobjects 
        @objname sysname=NULL,
        @objtype varchar(20)=NULL,
        @filegroup sysname = NULL
    AS
    
    /**
    * Get objects on filegroups, by type and object name
    *
    * Usage : sp__dba_getobjects @objname (valid table),
                                 @objtype (TABLE | CLUSTERED INDEX | NONCLUSTERED INDEX | TEXT,nTEXT,IMAGE),
                                 @filegroup (valid filegroup)
    * Date : 10.2004
    * Author : Stephane PAQUOT
    *
    * Version : 1.0
    */
    
    DECLARE 
        @idtype smallint,
        @idfilegroup smallint,
        @idobj integer

    SET NOCOUNT ON

    CREATE TABLE #tmp_rst
    (
        name sysname,
        id int,
        indid smallint,
        type varchar(20),
        objectname sysname,
        groupid smallint,
        groupname sysname
    )

    /** Check de l'object type */
    IF @objtype IS NOT NULL
    BEGIN
        IF @objtype NOT IN ('TABLE','CLUSTERED INDEX','NONCLUSTERED INDEX','TEXT,nTEXT,IMAGE')
        BEGIN
            PRINT 'Invalid Object type ' + @objtype + ' specified : <TABLE> <
                           CLUSTERED INDEX> <NONCLUSTERED INDEX> <TEXT,nTEXT,IMAGE>'
            RETURN -1
        END
    ELSE
      BEGIN
          SELECT @idtype = CASE @objtype
              WHEN 'TABLE' THEN 0
              WHEN 'CLUSTERED INDEX' THEN 1
              WHEN 'TEXT INDEX' THEN 255
              WHEN 'NONCLUSTERED INDEX' THEN -1
            END
      END
    END
    
    /** Check du filegroup */
    IF @filegroup IS NOT NULL
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM sysfilegroups WHERE groupname = @filegroup)
        BEGIN
            PRINT 'Invalid filegroup ' + @filegroup + ' specified. This one does not exist. '
            RETURN -1
        END
    ELSE
      BEGIN
          SELECT @idfilegroup=groupid FROM sysfilegroups where groupname=@filegroup
      END
    END
    
    /** Check du filegroup */
    IF @objname IS NOT NULL
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = @objname AND type='U')
        BEGIN
            PRINT 'Invalid object name ' + @objname + ' specified. This one does not exist. '
            RETURN -1
        END
    ELSE
      BEGIN
          SELECT @idobj=id FROM sysobjects where name=@objname and type='U'
      END
    END
    
    INSERT INTO #tmp_rst
    
    SELECT a.name,
      a.id,
      a.indid,
      CASE a.indid
          WHEN 255 THEN 'TEXT,nTEXT,IMAGE'
          WHEN 0 THEN 'TABLE'
          WHEN 1 THEN 'CLUSTERED INDEX'
      ELSE 'NONCLUSTERED INDEX' END AS type,
      object_name(a.id),
      c.groupid,
      c.groupname
      
    FROM sysindexes a, sysobjects b, sysfilegroups c
    
    WHERE a.id = b.id
    AND b.type = 'U'
    AND b.name != 'dtproperties'
    AND a.groupid = c.groupid
    
    /** Filter on the filegroup */
    IF @idfilegroup IS NOT NULL
    BEGIN
        DELETE FROM #tmp_rst WHERE groupid != @idfilegroup
    END
    
    /** Filter on the object */
    IF @idobj IS NOT NULL
    BEGIN
        DELETE FROM #tmp_rst WHERE id != @idobj
    END
    
    /** Filter on the object type */
    IF @idtype IS NOT NULL
    BEGIN
        IF @idtype NOT IN (0,1,255)
        BEGIN
            DELETE FROM #tmp_rst WHERE indid IN (0,1,255)
        END
        ELSE
          BEGIN
            DELETE FROM #tmp_rst WHERE indid != @idtype
          END
    END
    
    /** Final Result Sets */
    SELECT name,
           type,
           objectname,
           groupname
    
    FROM #tmp_rst
    ORDER BY groupname, objectname
    
    DROP TABLE #tmp_rst
    go
    
    execute sp_MS_MarkSystemObject sp__dba_getobjects
    go