Cinque funzioni amministrative comuni 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 grosso del monitoraggio l'uso di un server, modificare le dimensioni del database, l'uso e gli indici a ricostruire automaticamente o controllare la data di ultimo utilizzo di una tabella.

1. Monitoraggio attività del server

Monitoraggio di SQL Server è forse una delle più importanti responsabilità di amministratori di database. Per questa procedura io uso sp_WhoIsActive sviluppato dalla Meccanica Adamo. 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 le applicazioni stesse.

Consumo sp_WhoIsActive: Totale

Un parametro nella procedura per eseguire il ciclo di due volte in un determinato intervallo (ad esempio, 5s) per misurare l'attività (quasi) immediata di query differenti, che possono determinare facilmente che consuma più CPU, uso tempdb o fa disco accessi.

sp_WhoIsActive: 5s Consumo

La procedura visualizza anche le informazioni sui tipi di query Aspetta e causando blocchi.

Waits sp_WhoIsActive: e query

Un click su ogni link per visualizzare il file XML associato alla richiesta, conveniente di considerare la domanda, ovviamente, anche risorse.

Un alternativa più veloce, ma meno completo e che uso regolarmente è disponibile qui .

2. Monitorare l'utilizzo di spazio su disco

Io uso la procedura sp_SDS (registrazione gratuita obbligatoria) scritto da Richard Ding per generare una relazione sulla dimensione del database ei file di registro di un'istanza. Lo spazio è inoltre calcolato per entrambi i tipi di file e tutto il corpo.

sp_SDS

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

  SELECT @ @ servername AS grado
 db.name come base
 SUM (CASE WHEN 0 allora af.groupid = 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
 Da maestro .. db sysdatabases AS
 INNER JOIN master sysaltfiles .. COME ON af af. [DBID] = db. [DBID]
 Db.name WHERE NOT IN ('distribuzione', 'risorsa' 'maestro', 'tempdb', 'modello', 'msdb')
 GROUP BY db.name 

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

DBINFO

Basta creare un lavoro di SQL di eseguire questo script su uno regular base di archiviare questi dati in tabella la DBINFO, which si permettono di creare reports or per predire la evolution della dimensione di basi, ad esempio con SQL Server Analysis Services.

3. Analisi della performance dell'indice

L'indice di gestione è un altro importante compito della gestione del server SQL. Per analizzare la rilevanza e l'utilizzo di questi indici nelle tabelle di un database, io uso un script sviluppato da Jason Strate. Questo script visualizza tutti gli indici di un database (incluso i cumuli, vale a dire, le stesse tabelle) e una miriade di informazioni necessarie per valutare la loro utilità come il number di analizza e cerca, colonne indicizzate, la dimensione indice o l'impatto (positivo) indice sugli utenti. Lo script mostra anche gli indici mancanti suggerito da SQL Server.

Analisi Index

Se si desidera visualizzare cerca, scansioni e ricerche velocemente, è possibile utilizzare il seguente script:

  Object_name SELECT (S. [object_id]) AS [Nome oggetto],
 I. [NAME] AS [] nome di indice
 USER_SEEKS,
 USER_SCANS,
 USER_LOOKUPS,
 USER_UPDATES
 AS S 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 

Per studiare l'impatto delle richieste di Insert, Update e Delete su l'indice, è possibile utilizzare questo script:

 Object_name SELECT (A. [object_id]) AS [Nome oggetto], I [NAME] AS [Nome indice] A. LEAF_INSERT_COUNT, A. LEAF_UPDATE_COUNT, A. LEAF_DELETE_COUNT DA SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL, NULL, NULL, NULL) A INNER JOIN AS I ON I. SYS. INDICI [object_id] = [A. object_id] E I = A. index_id index_id DOVE ObjectProperty (A. [object_id], 'IsUserTable') 1 = 

Per elencare gli indici inutilizzati e determinare se essi possono essere rimossi, è possibile utilizzare il seguente script:

 INT DECLARE @ DBID, VARCHAR dbName @ (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 ([ ]) AS 'row_count righe', COUNT (partition_id) 'partition_count AS' dal Gruppo sys.partitions BY [object_id], index_id) object_name SELECT (i. [object_id]) objectName AS, i.name, CASE WHEN i.is_unique = 1 THEN 'unico' 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 'sì' ELSE 'No END AS 'partizionato?', CASE WHEN i.type E i.is_unique_constraint 2 = 0 THEN 'DROP INDEX i.name' On + + '' + @ = dbName + '. dbo'.  + Object_name (ddius. [] object_id) + ',' QUANDO E = 2 i.type i.is_unique_constraint = 1 THEN 'ALTER TABLE' + @ dbname + '. Dbo'.  + Object_name (ddius. [] object_id) + 'Drop vincolo' i.name + + ',' ELSE''END AS 'SQL_DropStatement' DA sys.indexes AS INNER JOIN sys.dm_db_index_usage_stats ddius ON i.OBJECT_ID = ddius.OBJECT_ID E i.index_id = ddius.index_id INNER JOIN CTE partitionCTE COME ON i.OBJECT_ID = cte.OBJECT_ID E i.index_id = cte.index_id ddius.database_id WHERE DBID = @ ORDER BY (ddius.user_seeks ddius.user_scans + + ddius.user_lookups ) user_updates DESC ASC; 

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 (AS VARCHAR ddmid.index_handle (10)) + 'On' ddmid.STATEMENT + + '(' + IsNull ( ddmid.equality_columns,'') CASE + quando non è null e ddmid.inequality_columns ddmid.equality_columns non è NULL THEN''ELSE''END + IsNull (ddmid.inequality_columns,'') + ')' + IsNull ('Includi ('+ + 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 ',' * sys.dm_db_missing_index_groups ddmigs.last_user_seek AS DA INNER JOIN ddmig ddmigs sys.dm_db_missing_index_group_stats COME ON ddmigs.group_handle = ddmig.index_group_handle INNER JOIN sys.dm_db_missing_index_details ddmid COME ON Iscriviti ddmig.index_handle interno = sys.tables ddmid.index_handle come T ON ddmid.OBJECT_ID = = t.OBJECT_ID DOVE 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

Come e quando i dati vengono aggiunti, aggiornati o cancellati, gli indici sono frammentati e possono peggiorare le prestazioni in modo significativo. E 'quindi importante avere una politica di controllo l'indice di frammentazione e le azioni da intraprendere in caso di frammentazione troppo elevata.

Nel mio caso sto correndo un posto di lavoro durante il fine settimana in esecuzione la procedura di dba_IndexDefrag_sp Michelle Ufford. Questa procedura permette di controllare tutti gli indici in tutti i database e server, a seconda del livello di frammentazione, di riorganizzazione o di rigenerarsi completamente. Opzioni di permettere alla procedura di modificare i livelli di frammentazione grilletto entrambe le azioni, specificare una base o una singola tabella per verificare o impostare il numero di processori da utilizzare.

5. Ultimo MERGE

A seconda del vostro ambiente di produzione, può accadere che un numero molto elevato di tabelle è creato dagli utenti che poi lasciano andare in pensione. Ciò si può verificare per l'importazione da file di esempio che può essere interessante per il check-in se non sui server SQL. Resta da vedere se queste tabelle sono ancora utilizzati o meno prima di archiviazione. Per questo si può utilizzare il seguente script, che ti darà la data ultima di leggere e scrivere tabelle di un database dato:

  ANSI_WARNINGS SET OFF;
 SET NOCOUNT ON;
 GO

 Agg CON SA
 (
 SELECT
 [Object_id]
 last_user_seek,
 last_user_scan,
 last_user_lookup,
 last_user_update
 DA
 sys.dm_db_index_usage_stats
 DOVE
 DB_ID database_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 dalle agg
 UNION ALL
 SELECT [object_id], last_user_scan, NULL dalle agg
 UNION ALL
 SELECT [object_id], last_user_lookup, NULL dalle agg
 UNION ALL
 SELECT [object_id], NULL dalle agg last_user_update
 ) Per x ([object_id], last_read, last_write)
 GROUP BY
 OBJECT_SCHEMA_NAME ([object_id]),
 Object_name ([object_id])
 ORDER BY 1,2;