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 gogroupid 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 gogroupname 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 :
|
|
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 :
|
|
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 PRIMARYLocaliser 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 :
|
|
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_INDXProcé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' goname 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' goname 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' goname 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