Fünf gemeinsame Verwaltungsaufgaben von SQL Server 2005

In diesem Artikel werde ich einige Skripte oder gespeicherten Prozeduren für SQL Server 2005, die ich regelmäßig Gebrauch im Rahmen der Verwaltung von meinem Server. Diese Skripte erlauben Großteil der Verfolgung der Verwendung eines Servers, Ändern der Größe der Datenbank, erstellen Indizes verwenden, und sie automatisch oder überprüfen Sie das Datum des letzten Einsatzes einer Tabelle.

1. Monitoring Server-Aktivität

Überwachung von SQL Server ist wohl eine der wichtigsten Aufgaben der Administratoren die Datenbanken. Für diese verwende ich das Verfahren sp_WhoIsActive Ausstellmechanik entwickelt von Adam. Dieses Verfahren wird die ausstehenden Anforderungen auf dem Server angezeigt und alle damit verbundenen Informationen, wie der Benutzer das Ausführen der Anwendung, die CPU, I / O oder die Anwendungen selbst.

sp_WhoIsActive: Gesamtverbrauch

Ein Parameter in der Prozedur, um den Zyklus zweimal laufen in einem bestimmten Intervall (zB 5s), die Aktivität (Maßnahme fast) instant verschiedener Anfragen, die sich leicht feststellen kann, was verbraucht die CPU verwenden tempdb oder macht Festplatte zugreift.

sp_WhoIsActive: Verbrauch 5s

Das Verfahren zeigt auch Informationen über die Arten von Abfragen Warten und verursacht Blocks.

sp_WhoIsActive: Waits und Abfragen

Ein Klick auf den jeweiligen Link zeigt die mit dem Antrag verbundene XML-Datei, in günstiger Nähe zu einem Antrag offensichtlich für zu aufwändig.

Eine schnellere Alternative, aber weniger vollständig ist und dass ich regelmäßig benutze ist verfügbar hier .

2. Überwachung der Nutzung von Speicherplatz

Ich benutze das Verfahren sp_SDS (kostenlose Registrierung erforderlich) Geschrieben von Richard Ding an Größe zu generieren einen Bericht über die Datenbank und die Protokolldateien einer Instanz. Der Raum wird auch für beide Arten von Dateien und den gesamten Körper berechnet.

sp_SDS

Eine Alternative zu dieser gespeicherten Prozedur, um leichter zu kopieren / in eine Excel-Tabelle einfügen wird:

  SELECT @ @ SERVERNAME AS Instanz
 db.name AS Base
 SUM (CASE WHEN 0 THEN af.groupid * = 0 ELSE 8192.0E af.size / 1048576.0E END) AS Taille_Base,
 SUM (CASE WHEN af.groupid = 0 THEN 8192.0E * / 1048576.0E ELSE 0 END) AS Taille_Log af.size,
 SUM (* 8192.0E af.size / 1048576.0E) AS Taille_Totale
 FROM master .. sysdatabases db AS
 INNER JOIN master .. sysaltfiles AS ON af af. [Dbid] = db. [Dbid]
 Db.name WHERE NOT IN ('Vertrieb', 'Resource', 'Master', 'tempdb', 'model', 'msdb')
 GROUP BY db.name 

Wenn Sie die Entwicklung der Größe Ihrer Datenbanken folgen wollen, wird es wahrscheinlich notwendig sein, eine bestimmte Tabelle in einer Datenbank-Administration zu schaffen. Diese Tabelle enthält alle Daten, die Sie als interessant zu beobachten. Für diese verwende ich das Skript DBINFO Ford geschrieben von Tim. Es kann in einer Tabelle die Größe der Dateien (Datenbanken aufnehmen und Protokolle), freien Speicherplatz, den Dateinamen oder den Recovery-Modus.

DBINFO

Erstellen Sie einfach einen SQL-Auftrag läuft dieses Skript in regelmäßigen Abständen, um diese Daten in der Tabelle DBINFO, mit denen Sie Berichte erstellen oder um die Entwicklung der Größe der Basen vorherzusagen speichert, zum Beispiel mithilfe von SQL Server Analysis Services.

3. Die Analyse der Performance-Index

Der Management-Index ist eine weitere wichtige Aufgabe der Verwaltung von SQL-Servern. Zur Analyse der Relevanz und Verwendung dieser Indizes für die Tabellen einer Datenbank, verwende ich ein Skript entwickelt Strate von Jason. Dieses Skript zeigt alle Indizes einer Datenbank (einschließlich Halden, das heißt, die Tische selbst) und eine Vielzahl von Informationen notwendig, um ihren Nutzen als die Zahl der zu bewerten ist Scans und versucht, indizierten Spalten, die Index-Größe oder die Auswirkungen (positive) Index auf Benutzer. Das Skript zeigt auch die fehlende Indizes von SQL Server vorgeschlagen.

Analysis Index

Wenn Sie die Anzeige sucht, scannt und Lookups schnell wollen, können Sie das folgende Skript:

  SELECT object_name (S. [object_id]) AS [Objektname]
 I. [NAME] AS [NAME INDEX]
 USER_SEEKS,
 USER_SCANS,
 USER_LOOKUPS,
 USER_UPDATES
 AS S FROM sys.dm_db_index_usage_stats
 INNER JOIN AS I sys.indexes
 ON I. [object_id] = S. [object_id]
 And I S. index_id index_id =
 WHERE OBJECTPROPERTY (S. [object_id], 'IsUserTable') = 1 

Zur Untersuchung der Auswirkungen von Anträgen auf Insert, Update und Delete auf dem Index, können Sie dieses Skript verwenden:

  SELECT object_name (A. [object_id]) AS [Objektname]
 I. [NAME] AS [NAME INDEX]
 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 AS I sys.indexes
 ON I. [object_id] = A. [object_id]
 I. UND A. index_id = index_id
 WHERE OBJECTPROPERTY (A. [object_id], 'IsUserTable') = 1 

Zur Liste ungenutzte Indizes und festzustellen, ob sie entfernt werden kann, können Sie das folgende Skript verwenden:

  INT DECLARE @ dbid
 , @ Dbname VARCHAR (100);

 SELECT @ dbid DB_ID = ()
 , @ Dbname = DB_NAME ();

 MIT partitionCTE (object_id, index_id, row_count, partition_count)
 AS
 (
 SELECT [object_id]
 , Index_id
 , SUM ([Zeilen]) 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
 WANN i.is_unique = 1
 THEN "unique"
 ELSE''
 + I.type_desc END AS 'indextype "
 , Ddius.user_seeks
 , Ddius.user_scans
 , Ddius.user_lookups
 , Ddius.user_updates
 , Cte.row_count
 , CASE WHEN partition_count> 1 dann "Ja"
 ELSE 'Nein' END AS 'partitioniert? "
 , CASE
 WANN i.type = 2 = 0 und i.is_unique_constraint
 THEN 'Drop Index' + i.name
 + 'On' + @ dbname
 + '. Dbo. "  + OBJECT_NAME (ddius. [object_id]) + ';'
 WANN i.type = 2 = 1 und i.is_unique_constraint
 THEN 'ALTER TABLE' + @ dbname
 + '. Dbo. "  + OBJECT_NAME (ddius. [object_id])
 + 'Drop-Einschränkung' + + i.name ';'
 ELSE''
 END AS 'SQL_DropStatement "
 Wie ich aus sys.indexes
 INNER JOIN sys.dm_db_index_usage_stats ddius
 ON = i.OBJECT_ID ddius.OBJECT_ID
 Und i.index_id = ddius.index_id
 INNER JOIN partitionCTE AS CTE
 ON = i.OBJECT_ID cte.OBJECT_ID
 Und i.index_id = cte.index_id
 WHERE dbid = @ ddius.database_id
 ORDER BY
 (Ddius.user_seeks ddius.user_scans + + ddius.user_lookups) ASC
 , User_updates DESC; 

Schließlich, um eine Liste der fehlenden Indizes von SQL Server vorgeschlagen, können Sie dieses Skript verwenden:

  SELECT t.name AS 'Table'
 'Create Index NONCLUSTERED ix_' + + t.name "_missing_"
 + CAST (AS VARCHAR ddmid.index_handle (10))
 + 'On' + ddmid.STATEMENT
 + '(' + IsNull (ddmid.equality_columns,'')
 Ddmid.equality_columns + CASE WHEN Ist Nicht Null
 Und ddmid.inequality_columns Ist Nicht Null dann''
 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
 AS FROM sys.dm_db_missing_index_groups 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
 AS t INNER JOIN sys.tables
 ON = ddmid.OBJECT_ID t.OBJECT_ID
 WHERE ddmid.database_id DB_ID = ()
 Und 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. Defragmentieren Indizes

Wie und wann Daten hinzugefügt, aktualisiert oder gelöscht werden, die Indizes fragmentiert sind und kann die Leistung erheblich beeinträchtigen. Es ist daher wichtig, eine Politik der Überwachung der Fragmentierung Index und Aktionen müssen im Falle der Fragmentierung zu hoch genommen werden.

In meinem Fall bin ich einen Job ausführen am Wochenende läuft das Verfahren dba_IndexDefrag_sp Michelle Ufford. Dieses Verfahren erlaubt es, alle Indizes in allen Datenbanken und Servern zu überprüfen, nach dem Grad der Fragmentierung, zu reorganisieren oder vollständig zu regenerieren. Optionen ermöglichen das Verfahren zur Fragmentierung Ebenen wechseln beide Aktionen auslösen, um eine Basis-oder eine einzelne Tabelle angeben, zu überprüfen oder die Anzahl der Prozessoren zu verwenden.

5. Letzte MERGE

Abhängig von Ihrer Produktionsumgebung, kann es passieren, dass eine sehr große Anzahl von Tabellen, die von Benutzern, die dann in den Ruhestand gehen wird erstellt. Dies kann zum Beispiel die Einfuhr von Dateien auftreten, dass es interessant sein, in einem anderen Ort als auf dem SQL Server überprüfen kann. Es bleibt abzuwarten, ob diese Tabellen noch verwendet werden oder nicht vor der Archivierung werden. Aus diesem können Sie das folgende Skript gibt Ihnen die letzten Tage des Lesens und Schreibens Tabellen in einer gegebenen Datenbank:

  ANSI_WARNINGS SET OFF;
 SET NOCOUNT ON;
 GO

 Mit AS agg
 (
 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 FROM agg last_user_update
 ) AS x ([object_id], last_read, last_write)
 GROUP BY
 OBJECT_SCHEMA_NAME ([object_id]),
 OBJECT_NAME ([object_id])
 ORDER BY 1,2;