5 comuni attività di amministrazione di SQL Server 2005

In questo articolo vi presentiamo alcuni script o stored procedure di SQL Server 2005 che uso regolarmente come parte della gestione del mio server. Questi script consentono alla rinfusa monitorare l'utilizzo di un server, modificare le dimensioni delle basi, l'uso di indici e la loro ricostruzione automatica o controllare la data di ultimo utilizzo di una tabella.

1. Attività di monitoraggio dei server

Il monitoraggio di SQL Server è forse uno dei compiti più importanti di amministratori di database. Per questo uso il sp_WhoIsActive procedura sviluppata da Adam Machanic. Questa procedura visualizzerà le richieste in attesa sul server e tutte le informazioni associate, come l'utente che esegue l'applicazione, la CPU, I / O o le applicazioni stesse.

Un parametro nella procedura per eseguire la procedura per due volte in un dato intervallo (ad esempio, 5s) per misurare l'attività di (quasi) immediata delle diverse applicazioni, che possono determinare facilmente che consuma la CPU più, l'uso tempdb o rende disco accessi.

La procedura visualizza anche informazioni sui tipi di query e Wait a causa del blocco.

Un click su ogni link verrà visualizzato il file XML associato alla richiesta, comodo per esaminare una domanda, ovviamente, anche di risorse.

Una più rapida, ma meno completa e che uso regolarmente è disponibile qui.

2. Monitorare l'utilizzo di spazio su disco

Io uso la procedura sp_SDS (necessaria la registrazione) scritta da Richard Ding per generare un rapporto delle dimensioni del database e registra un esempio. Lo spazio è inoltre calcolato per entrambi i tipi di file e tutto il corpo.

In alternativa a questa stored procedure per essere più facile per un copia / incolla in un foglio di calcolo Excel è la seguente:

  SELECT @ @ servername AS grado
 db.name come 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
 Master .. sysdatabases come DB
 INNER JOIN master .. sysaltfiles AS af af ON. [] Dbid = db. [Dbid]
 Db.name DOVE NON IN ( 'distribuzione', 'Risorse', 'maestro', 'tempdb', 'modello', 'msdb')
 GROUP BY db.name 

Se si desidera controllare le dimensioni del database, sarà probabilmente necessario creare una tabella specifica in una amministrazione del database. Questa tabella contiene tutti i dati che ritieni interessante da guardare. Per questo io uso il DBINFO script scritto da Tim Ford. E 'possibile registrare in una tabella le dimensioni dei file (database e log), spazio libero, il nome del file o il metodo di restauro.

Semplicemente creare un lavoro di SQL in esecuzione di questo script a intervalli regolari per memorizzare i dati nella tabella DBINFO Questo vi permetterà di creare relazioni e di prevedere l'evoluzione delle dimensioni delle basi, per esempio l'utilizzo di SQL Server Analysis Services.

3. Analisi dell'indice di prestazione

L'indice di gestione è un altro compito importante per l'amministrazione di server SQL. Per analizzare la pertinenza e l'utilizzo di questi indici nelle tabelle di un database, utilizzare una sceneggiatura sviluppata da Jason Strate. Questo script mostra tutti gli indici in un database (anche cumuli, vale a dire, le tabelle stesse) e una miriade di informazioni per valutare la loro utilità come il numero di scansioni e cerca, indicizzate le colonne, le dimensioni di indice o gli effetti (positivi) indice per gli utenti. Lo script mostra anche gli indici mancanti suggerito da SQL Server.

Se si desidera visualizzare cerca, ricerche e acquisisce rapidamente, è possibile utilizzare il seguente script:

  SELECT OBJECT_NAME (S. [object_id]) AS [OBJECT NAME]
 I. [NAME] AS [NOME INDICE]
 USER_SEEKS,
 USER_SCANS,
 USER_LOOKUPS,
 USER_UPDATES
 S COME DA sys.dm_db_index_usage_stats
 INNER JOIN AS I sys.indexes
 PER I. [object_id] = S. [object_id]
 E I. S. index_id = index_id
 DOVE OBJECTPROPERTY (S. [object_id], 'IsUserTable') = 1 

Di studiare l'impatto delle richieste di inserire, aggiornare e cancellare sull'indice, è possibile utilizzare questo script:

  SELECT OBJECT_NAME (A. [object_id]) AS [OBJECT NAME]
 I. [NAME] AS [NOME INDICE]
 A. LEAF_INSERT_COUNT,
 A. LEAF_UPDATE_COUNT,
 A. LEAF_DELETE_COUNT
 Sys.dm_db_index_operational_stats DA (NULL, NULL, NULL, NULL) A
 INNER JOIN AS I sys.indexes
 PER I. [object_id] = A. [object_id]
 E I. A. index_id = index_id
 DOVE OBJECTPROPERTY (A. [object_id], 'IsUserTable') = 1 

Alla lista degli indici utilizzati e di determinare se o non possono essere rimossi, è possibile utilizzare il seguente script:

 DECLARE @ INT dbid, @ dbname VARCHAR (100) SELECT @ dbid = DB_ID (), @ dbname = DB_NAME (); CON partitionCTE (OBJECT_ID, index_id, row_count, partition_count) AS (SELECT [object_id], index_id, SUM ([ ROWS]) row_count COME ', COUNT (partition_id) sys.partitions partition_count COME' DA GROUP BY [object_id], index_id) SELECT OBJECT_NAME (I. [object_id]) AS objectName, i.name, CASE WHEN i.is_unique = 1 THEN 'ELSE''END UNIQUE' + COME i.type_desc 'ddius.user_seeks INDEXTYPE', ddius.user_scans, ddius.user_lookups, ddius.user_updates, cte.row_count, QUALORA partition_count> 1 THEN 'sì' ELSE 'No END 'AS' partizionato? ', QUALORA i.type E INDEX = i.is_unique_constraint 2 = 0 THEN' drop '+ + i.name' On '+ @ dbname +'. dbo '.  + OBJECT_NAME (ddius. [object_id]) + ',' QUANDO i.type E i.is_unique_constraint = 2 = 1 THEN 'ALTER TABLE' + @ dbname + '. Dbo'.  + OBJECT_NAME (ddius. [object_id]) + 'DROP CONSTRAINT' i.name + + ',' ELSE''END AS 'SQL_DropStatement' DA sys.indexes AS i INNER JOIN ON sys.dm_db_index_usage_stats ddius i.object_id = ddius.OBJECT_ID e i.index_id = INNER JOIN ddius.index_id CTE ON AS partitionCTE i.object_id = cte.OBJECT_ID E i.index_id = cte.index_id WHERE dbid = @ ORDER BY ddius.database_id (ddius.user_seeks + ddius.user_scans + ddius.user_lookups ) ASC DESC user_updates; 

Infine, per elencare gli indici mancanti suggerito da SQL Server, è possibile utilizzare questo script:

 SELECT t.name AS 'Table', 'Crea indice non cluster 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 E 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 sys.dm_db_missing_index_groups A PARTIRE DA INTERNO ddmig sys.dm_db_missing_index_group_stats aderire come ddmigs ON ddmigs.group_handle = ddmig.index_group_handle INNER JOIN AS sys.dm_db_missing_index_details ddmid ON ddmig.index_handle = ddmid.index_handle sys.tables INNER JOIN AS t ON ddmid.OBJECT_ID = t.OBJECT_ID WHERE ddmid.database_id DB_ID = () e 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. Deframmentazione degli indici

A poco a poco, in quanto i dati più vengono aggiunti, aggiornati o cancellati, gli indici sono frammentati e possono degradare le prestazioni in modo significativo. E 'quindi importante avere una politica di controllo l'indice di frammentazione e di intervento in caso di frammentazione troppo.

Nel mio caso sto eseguendo un lavoro week-end che esegue la dba_IndexDefrag_sp procedura di Michelle Ufford. Questa procedura consente di verificare tutti gli indici di tutti i database e server, a seconda del livello di frammentazione, di riorganizzazione o di rigenerarsi completamente. Le opzioni permettono la procedura per modificare i livelli di frammentazione scattare le due azioni, specificare una base o una singola tabella per verificare o identificare il numero di processori da utilizzare.

5. Ultimo tabelle utilizzate

A seconda del vostro ambiente di produzione, può accadere che un gran numero di tabelle è stato creato da utenti che poi abbandonati. Ciò può accadere per esempio per l'importazione di file che può essere quindi interessante verificare in altre parti sul server SQL. Resta da vedere se queste tabelle sono ancora utilizzati o meno prima di archiviazione. Per questo è possibile utilizzare il seguente script che vi darà le ultime date di leggere e scrivere le tabelle contenute in un database specifico:

  ANSI_WARNINGS OFF SET;
 SET NOCOUNT ON;
 GO

 CON agg AS
 (
 SELECT
 [object_id]
 last_user_seek,
 last_user_scan,
 last_user_lookup,
 last_user_update
 DA
 sys.dm_db_index_usage_stats
 DOVE
 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)
 DA
 (
 SELECT [object_id], last_user_seek, NULL agg DA
 UNION ALL
 SELECT [object_id], last_user_scan, NULL agg DA
 UNION ALL
 SELECT [object_id], last_user_lookup, NULL agg DA
 UNION ALL
 SELECT [object_id], last_user_update NULL DA 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