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


1- 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.

2- Contexte

2-1- 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.

2-2- 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 initiale Taille maxi. Croissance
PRIMARY
Tablespace primaire
data_cgcam_pri_01.mdf 10Mb 50Mb 15%
data_cgcam_pri_02.mdf 10Mb 50Mb 15%
INDX
Tablespace des indexes non clusterisés
data_cgcam_ind_01.mdf 10Mb 50Mb 15%
data_cgcam_ind_02.mdf 10Mb 50Mb 15%
HISTO
Tablespace des tables d'historique
data_cgcam_his_01.mdf 10Mb 50Mb 15%
data_cgcam_his_02.mdf 10Mb 50Mb 15%
HISTO_INDX
Tablespace des indexes non clusterisés des tables d'historique
data_cgcam_hsidx_01.mdf 10Mb 50Mb 15%
data_cgcam_hsidx_02.mdf 10Mb 50Mb 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%)

2-3- 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 permet d'obtenir quant à elle le nombre de devices associé à un groupe de fichiers ainsi que le groupid d'un groupe de fichiers :

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

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

select filegroup_id('HISTO')

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

select filegroup_name(3)

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.

3-1- 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 table sysindexes :

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

3-2- 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

3-3- 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 sysobjets. 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 INDX
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

4- Procédure stockée sp__dba_getobjects

4-1- Usage

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

Syntaxe :

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

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

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

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

4-2- Code source

    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
    sp_MS_MarkSystemObject sp__dba_getobjects
    go

Annexe

Historique

Version Date Commentaires
1.0 11/2004 Version initiale

Liens

MSDN Books Online, SQL Server 2000, Transact-SQL reference, sp_helpfilegroup
MSDN Books Online, SQL Server 2000, Transact-SQL reference, Tables systèmes, sysindexes
MSDN Books Online, SQL Server 2000, Transact-SQL reference, Tables systèmes, sysfilegroups