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.
sp_WhoIsActive : Consommation totale
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.
sp_WhoIsActive : Consommation sur 5s
La procédure affiche également des informations sur les types de Wait et les requêtes provoquant des Blocks.
sp_WhoIsActive : Waits et requêtes
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.
sp_SDS
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.
DBInfo
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.
Analyse des Index
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
