5 tâches courantes d’administration de SQL Server 2005

Dans cet article je vais présenter quelques scripts ou procédures stockées pour SQL Server 2005 que j’utilise régulièrement dans le cadre de l’administration de mes serveurs. Ces scripts permettent en vrac de suivre l’utilisation d’un serveur, l’évolution de la taille des bases, l’utilisation des index ainsi que leur reconstruction automatique ou encore de vérifier la date de dernière utilisation d’une table.

1. Surveillance de l’activité du serveur

Le monitoring des serveurs SQL fait sans doute partie des plus importantes responsabilités d’un administarteur de bases de données. Pour cela j’utilise la procédure sp_WhoIsActive développée par Adam Machanic. Cette procédure permet d’afficher les requêtes en cours sur le serveur ainsi que toutes les informations associées comme l’utilisateur exécutant la requête, la consommation CPU, les entrées/sorties ou encore les requêtes elles-mêmes.

Un paramètre dans la procédure permet d’exécuter la procédure deux fois selon un intervalle donné (par exemple 5s) afin de mesurer l’activité (quasi) instantanée des différentes requêtes, ce qui permet de déterminer facilement laquelle consomme le plus de processeur, utilise la base tempdb ou procède à des accès disques.

La procédure affiche également des informations sur les types de Wait et les requêtes provoquant des Blocks.

Un clic sur chaque lien permet d’afficher le fichier XML associé à la requête, pratique pour étudier une requête visiblement trop gourmande en ressources.

Une alternative plus rapide mais moins complète et que j’utilise régulièrement est disponible ici.

2. Surveillance de l’utilisation de l’espace disque

J’utilise la procédure sp_SDS (inscription gratuite nécessaire) écrite par Richard Ding pour générer un rapport sur la taille des bases et des logs d’une instance. L’espace libre est également calculé pour les deux types de fichiers ainsi que le total de l’instance.

Une alternative à cette procédure stockée permettant de pouvoir plus facilement faire un copier/coller dans une feuille Excel est la suivante :

SELECT      @@SERVERNAME AS Instance,
db.name AS Base,
SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS Taille_Base,
SUM(CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS Taille_Log,
SUM(8192.0E * af.size / 1048576.0E) AS Taille_Totale
FROM        master..sysdatabases AS db
INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE       db.name NOT IN('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb')
GROUP BY    db.name

Si vous souhaitez pouvoir suivre l’évolution de la taille de vos bases, il sera probablement nécessaire de créer une table spécifique dans une base d’administration. Cette table pourra contenir l’ensemble des données que vous jugez intéressant de surveiller. Pour cela j’utilise le script DBInfo écrit par Tim Ford. Il permet d’enregistrer dans une table la taille des fichiers (bases ou logs), l’espace libre, le nom du fichier ou encore le mode de restauration.

Il suffit de créer un job SQL exécutant ce script à intervalle régulier pour stocker ces données dans la table DBInfo, ce qui pourra permettre de créer des rapports ou encore de prédire l’évolution de la taille des bases, par exemple au moyen de SQL Server Analysis Services.

3. Analyse des performances des index

La gestion des index est une autre tâche importante de l’administration de serveurs SQL. Pour analyser la pertinence et l’utilisation des index présents sur les tables d’une base, j’utilise un script développé par Jason Strate. Ce script affiche l’ensemble des index d’une base (y compris les Heaps, c’est-à-dire les tables elles-mêmes) ainsi qu’une myriade d’informations permettant de juger de leur utilité, comme le nombre de Seeks et de Scans, les colonnes indexées, la taille de l’index ou encore l’impact (positif) de l’index sur les utilisateurs. Le script affiche également les index manquants suggérés par SQL Server.

Si vous souhaitez afficher les Seeks, Scans et Lookups rapidement, vous pouvez utiliser le script suivant :

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

Pour étudier l’impact des requêtes en Insert, Update et Delete sur les index, vous pouvez utiliser ce script :

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = A.[OBJECT_ID]
AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

Pour lister les index inutilisés et déterminer si oui ou non ils peuvent être supprimés, vous pouvez utiliser le script suivant :

DECLARE @dbid INT
, @dbName VARCHAR(100);

SELECT @dbid = DB_ID()
, @dbName = DB_NAME();

WITH partitionCTE (OBJECT_ID, index_id, row_count, partition_count)
AS
(
SELECT [OBJECT_ID]
, index_id
, SUM([ROWS]) AS 'row_count'
, COUNT(partition_id) AS 'partition_count'
FROM sys.partitions
GROUP BY [OBJECT_ID]
, index_id
)

SELECT OBJECT_NAME(i.[OBJECT_ID]) AS objectName
, i.name
, CASE
WHEN i.is_unique = 1
THEN 'UNIQUE '
ELSE ''
END + i.type_desc AS 'indexType'
, ddius.user_seeks
, ddius.user_scans
, ddius.user_lookups
, ddius.user_updates
, cte.row_count
, CASE WHEN partition_count > 1 THEN 'yes'
ELSE 'no' END AS 'partitioned?'
, CASE
WHEN i.type = 2 And i.is_unique_constraint = 0
THEN 'Drop Index ' + i.name
+ ' On ' + @dbName
+ '.dbo.' + OBJECT_NAME(ddius.[OBJECT_ID]) + ';'
WHEN i.type = 2 And i.is_unique_constraint = 1
THEN 'Alter Table ' + @dbName
+ '.dbo.' + OBJECT_NAME(ddius.[OBJECT_ID])
+ ' Drop Constraint ' + i.name + ';'
ELSE ''
END AS 'SQL_DropStatement'
FROM sys.indexes AS i
INNER Join sys.dm_db_index_usage_stats ddius
ON i.OBJECT_ID = ddius.OBJECT_ID
And i.index_id = ddius.index_id
INNER Join partitionCTE AS cte
ON i.OBJECT_ID = cte.OBJECT_ID
And i.index_id = cte.index_id
WHERE ddius.database_id = @dbid
ORDER BY
(ddius.user_seeks + ddius.user_scans + ddius.user_lookups) ASC
, user_updates DESC;

Enfin pour lister les index manquants suggérés par SQL Server, vous pouvez utiliser ce script :

SELECT t.name AS 'Table'
, 'Create NonClustered Index IX_' + t.name + '_missing_'
+ CAST(ddmid.index_handle AS VARCHAR(10))
+ ' On ' + ddmid.STATEMENT
+ ' (' + IsNull(ddmid.equality_columns,'')
+ CASE WHEN ddmid.equality_columns IS Not Null
And ddmid.inequality_columns IS Not Null THEN ','
ELSE '' END
+ IsNull(ddmid.inequality_columns, '')
+ ')'
+ IsNull(' Include (' + ddmid.included_columns + ');', ';'
) AS sql_statement
, ddmigs.user_seeks
, ddmigs.user_scans
, CAST((ddmigs.user_seeks + ddmigs.user_scans)
* ddmigs.avg_user_impact AS INT) AS 'est_impact'
, ddmigs.last_user_seek
FROM sys.dm_db_missing_index_groups AS ddmig
INNER Join sys.dm_db_missing_index_group_stats AS ddmigs
ON ddmigs.group_handle = ddmig.index_group_handle
INNER Join sys.dm_db_missing_index_details AS ddmid
ON ddmig.index_handle = ddmid.index_handle
INNER Join sys.tables AS t
ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE ddmid.database_id = DB_ID()
And CAST((ddmigs.user_seeks + ddmigs.user_scans)
* ddmigs.avg_user_impact AS INT) > 100
ORDER BY CAST((ddmigs.user_seeks + ddmigs.user_scans)
* ddmigs.avg_user_impact AS INT) DESC;

4. Défragmentation des index

Au fur et à mesure que des données sont ajoutées, mises à jour ou supprimées, les index se fragmentent et peuvent dégrader les performances de manière non négligeable. Il est donc important d’avoir une politique de surveillance de la fragmentation des index et des actions à entreprendre en cas de fragmentation trop élevée.

Dans mon cas je fais tourner un job le weekend qui exécute la procédure dba_IndexDefrag_sp de Michelle Ufford. Cette procédure permet de vérifier tous les index de toutes les bases d’un serveur et, selon le niveau de fragmentation, de les réorganiser ou de les regénérer complètement. Des options dans la procédure permettent de changer les niveaux de fragmentation déclenchant les deux actions, de spécifier une base ou une table unique à vérifier ou encore de définir le nombre de processeurs à utiliser.

5. Dernière utilisation des tables

Selon votre environnement de production, il peut arriver qu’un très grand nombre de tables soit créé par des utilisateurs qui les laissent ensuite à l’abandon. Cela peut se produire par exemple pour les imports de fichiers qu’il peut alors être intéressant d’archiver ailleurs que sur le serveur SQL. Reste cependant à savoir si ces tables sont encore utilisées ou non avant de pouvoir les archiver. Pour cela, vous pouvez utiliser le script suivant qui vous donnera les dernières dates de lecture et d’écriture des tables contenues dans une base donnée :

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,2;

Tags : SQL Server 2005