Cerca

Benvenuto! Se sei nuovo qui, forse sarete interessati al feed RSS per essere informato della pubblicazione di un nuovo articolo?

5 attività amministrative comuni di SQL Server 2005

Inviato da: Seb

Luglio

In questo articolo vi presenterò alcuni script o le stored procedure di SQL Server 2005 che uso regolarmente come parte dell'amministrazione dei miei server. Questi script scatenato per monitorare l'utilizzo di un server, l'evoluzione delle dimensioni del database, l'uso di indici e la loro ricostruzione automatica o verificare la data dell'ultimo utilizzo di una tabella.

1. Monitoraggio attività del server

Monitoraggio dei server SQL è senza dubbio uno dei compiti più importanti di un database administarteur. Per questo io uso la procedura sp_WhoIsActive sviluppato da machanic Adam. Questa procedura consente di visualizzare le richieste in sospeso sul server e tutte le informazioni associate come l'utente che esegue l'applicazione, la CPU, I / O o query stesse.

sp_WhoIsActive: Total consumo

Un parametro in la procedura per eseguire la procedura due volte in un dato intervallo (es. 5s) per misurare l'attività (quasi) immediata di interrogazioni differenti, che può determinare facilmente che consuma il più CPU, utilizza tempdb o fa accessi al disco.

sp_WhoIsActive : Consommation sur un intervalle de 5s

sp_WhoIsActive: Consumo di 5s

La procedura consente di visualizzare anche le informazioni sui tipi di query e di attesa a causa del blocco.

sp_WhoIsActive: Waits e le richieste

Cliccando su ogni link viene visualizzato il file XML associato alla richiesta, esaminare la domanda di pratica visibilmente troppo alta intensità di risorse.

Un veloce ma meno completa e che io uso regolarmente è disponibile qui .

2. Monitorare l'uso dello spazio su disco

Io uso le procedure sp_SDS (registrazione gratuita richiesta) scritto da Richard Ding per generare un rapporto sulle dimensioni del database e log del procedimento. Lo spazio libero è inoltre calcolato per entrambi i tipi di file e il totale del procedimento.

sp_SDS

Un'alternativa a questa stored procedure per rendere più facile copiare / incollare in un foglio di Excel è la seguente:

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;