検索

ようこそ！ここに新しいしている場合は、おそらくあなたはに興味があるでしょうRSSフィードの新しい記事の公開を通知する？

SQL Server 2005の5共通の管理タスク

投稿者：セブ

7月

この記事では私はサーバの管理の一環として定期的に使用するSQL Server 2005のいくつかのスクリプトやストアドプロシージャをご紹介します。 これらのスクリプトは、サーバの使用、データベースのサイズの進化、索引の使用とその自動再構築を追跡したり、テーブルの最後の利用日を確認するために放し。

1。サーバーアクティビティの監視

SQL Serverの監視は、間違いなくadministarteurデータベースの最も重要な責務の一つです。そのために私は、プロシージャの使用sp_WhoIsActiveアダムMachanicで開発した。この手順では、サーバおよびアプリケーションを実行しているユーザーは、CPU、I / Oまたはクエリ自体と関連付けられたすべての情報に未処理の要求を表示することができます。

sp_WhoIsActive：総消費量

簡単にほとんどのCPUを消費するか決定することができる活性を測定するために与えられた間隔（例えば5秒）（ほぼ）異なるクエリの瞬間、二回の手順を実行するプロシージャのパラメータは、使用されますtempdbまたはディスクアクセスを行います。

sp_WhoIsActive : Consommation sur un intervalle de 5s

sp_WhoIsActive：5Sの消費

また、この手順では、クエリの待機と原因ブロックの種類に関する情報が表示されます。

sp_WhoIsActive：待機し、要求

各リンクをクリックすると、要求に関連付けられているXMLファイルが表示され、あまりにも目に見えて、実際のアプリケーションリソースを集中的に検討してください。

高速ですが、以下の完全な、私は定期的に使用することを可能です、ここ。

2。ディスク領域の使用を監視する

私は、プロシージャの使用sp_SDS手続のデータベースサイズとログに関するレポートを生成するためにリチャード·丁によって書かれます（無料登録が必要）。空き容量は、両方のファイルの種類と手続の合計が計算されます。

sp_SDS

それは簡単にExcelスプレッドシートにコピー/ペーストできるように、このストアドプロシージャの代わりには、次のとおりです。

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;