Forschung

Willkommen! Wenn Sie neu hier, vielleicht wirst du in RSS interessiert sein Feeds benachrichtigt werden, wenn ein neuer Artikel veröffentlicht wird?

5 allgemeinen administrativen Aufgaben von SQL Server 2005

Geschrieben von: Seb

Juli

In diesem Artikel werde ich einige Skripte vorhanden oder gespeicherten Prozeduren für SQL Server 2005, die ich benutze regelmäßig als Teil der Verwaltung meiner Server. Diese Skripte erlauben Bulk überwachen die Verwendung eines Servers, Ändern der Größe der Datenbanken, die Verwendung von Indizes und deren automatische Erneuern 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 zu Datenbanken. Dazu dient das Verfahren sp_WhoIsActive von Adam Mechaniker-entwickelt. Dieses Verfahren zeigt die ausstehenden Anforderungen auf dem Server und alle zugehörigen Informationen wie die Benutzer die Anwendung ausführt, die CPU, I / O oder die Anwendungen selbst.

sp_WhoIsActive: Gesamtverbrauch

Ein Parameter, der in dem Verfahren, das Verfahren zweimal in einem bestimmten Intervall ausgeführt werden (zB 5s) auf die Aktivität zu messen (fast) sofort von verschiedenen Anwendungen, die man leicht feststellen kann, welche die CPU, verwenden Sie verbraucht tempdb oder auf die Festplatte zugreift.

sp_WhoIsActive : Consommation sur un intervalle de 5s

sp_WhoIsActive: Verbrauch 5s

Das Verfahren zeigt auch Informationen über die Art der Anfragen warten und verursacht Blocks.

sp_WhoIsActive: Waits und Abfragen

Ein Klick auf den jeweiligen Link zeigt die XML mit der Bitte zugehörige Datei, bequem zu prüfen, ein Antrag offensichtlich zu aufwändig.

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

2. Die Überwachung der Nutzung von Speicherplatz

Ich benutze das Verfahren sp_SDS (kostenlose Registrierung erforderlich) von Richard Ding geschrieben, um einen Bericht über die Größe der Datenbanken erstellen und Protokolle einer Instanz. Der Raum ist auch für beide Arten von Dateien und den gesamten Körper berechnet.

sp_SDS

Eine Alternative zu dieser gespeicherten Prozedur auch leichter zu Kopieren / Einfügen in eine Excel-Tabelle ist wie folgt:

  SELECT @ @ SERVERNAME AS Instanz
 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 * / 1048576.0E af.size) AS Taille_Totale
 FROM master .. sysdatabases AS db
 INNER JOIN master .. sysaltfiles AS ON af af. [Dbid] = db. [Dbid]
 Db.name WHERE NOT IN ( 'Verteilung', 'Ressourcen', 'master', 'tempdb', 'Modell', 'msdb')
 GROUP BY db.name

Wenn Sie die Größe Ihrer Datenbanken überwachen möchten, wird es wahrscheinlich erforderlich sein, um eine bestimmte Tabelle in einer Datenbank Verwaltung zu schaffen. Diese Tabelle enthält alle Daten, die Sie halten interessant zu beobachten. Aus diesem verwende ich das Skript DBINFO von Tim Ford geschrieben. Er kann in einer Tabelle die Größe der Dateien (Datenbanken und Protokolle), freien Speicherplatz, den Dateinamen oder die Methode zur Wiederherstellung.

DBINFO

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

3. Analyse von Performance-Index

Der Management-Index ist eine weitere wichtige Aufgabe für die Verwaltung von SQL Server. Um die Relevanz und die Verwendung dieser Indizes für die Tabellen einer Datenbank zu analysieren, verwende ich ein Skript von Jason Strate entwickelt. Dieses Skript zeigt alle Indizes in einer Datenbank (einschließlich Halden, das heißt, die Tabellen selbst ist) und eine Vielzahl von Informationen, um ihre Nützlichkeit als die Zahl der Richter Scans und Sucht, indizierte Spalten, die Index-Größe oder die Auswirkungen (positiv) Index für Benutzer. Das Skript zeigt auch die fehlenden Indizes von SQL Server vorgeschlagen.

Analyse Index

Wenn Sie Sucht anzeigen möchten, Lookups und scannt schnell, können Sie das folgende Skript:

  SELECT OBJECT_NAME (S. [OBJECT_ID]) AS [OBJECT NAME]
 I. [NAME] AS [NAME INDEX]
 User_seeks,
 User_scans,
 USER_LOOKUPS,
 User_updates
 AS S VON sys.dm_db_index_usage_stats
 AS I INNER JOIN sys.indexes
 ON I. [OBJECT_ID] = S. [OBJECT_ID]
 UND I S. index_id = index_id
 WO OBJECTPROPERTY (S. [OBJECT_ID], 'IsUserTable') = 1

Um die Auswirkungen der Anträge für Insert, Update und Delete auf der Index-Studie, können Sie dieses Skript verwenden:

  SELECT OBJECT_NAME (A. [OBJECT_ID]) AS [OBJECT NAME]
 I. [NAME] AS [NAME INDEX]
 A. LEAF_INSERT_COUNT,
 A. LEAF_UPDATE_COUNT,
 A. LEAF_DELETE_COUNT
 AUS sys.dm_db_index_operational_stats (NULL, NULL, NULL, NULL) A
 AS I INNER JOIN sys.indexes
 ON I. [OBJECT_ID] = A. [OBJECT_ID]
 UND I: A. index_id = index_id
 WO OBJECTPROPERTY (A. [OBJECT_ID], 'IsUserTable') = 1

Um ungenutzten Indizes Liste und festzustellen, ob sie nicht entfernt werden kann, können Sie das folgende Skript werden:

  DECLARE @ dbid INT
 , @ 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 ([rows]) AS 'row_count "
 , COUNT (partition_id) AS 'partition_count "
 AUS sys.partitions
 GROUP BY [OBJECT_ID]
 , Index_id
 )

 SELECT OBJECT_NAME (I. [OBJECT_ID]) AS objectName
 , I.name
 , CASE
 WENN 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 THEN 'ja'
 ELSE 'No' END AS 'partitioniert? "
 , CASE
 WENN i.type Und i.is_unique_constraint = 2 = 0
 THEN 'DROP INDEX' + i.name
 + 'On' + @ dbname
 +. "Dbo."  + OBJECT_NAME (ddius. [OBJECT_ID]) + ";"
 WENN i.type Und 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 "
 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 cte AS partitionCTE
 ON i.object_id = cte.OBJECT_ID
 Und i.index_id = cte.index_id
 Ddius.database_id WHERE dbid = @
 ORDER BY
 (ddius.user_seeks + ddius.user_scans + ddius.user_lookups) AUC
 , User_updates DESC;

Schließlich, um die fehlenden Indizes von SQL Server vorgeschlagenen Liste, können Sie dieses Skript verwenden:

  SELECT t.name AS 'Table'
 "Gruppierten Index erstellen ix_ '+ t.name +' _missing_"
 + CAST (AS VARCHAR ddmid.index_handle (10))
 + 'On' + ddmid.STATEMENT
 + '(' + IsNull (ddmid.equality_columns,'')
 + CASE WHEN IS ddmid.equality_columns Nicht Null
 Und 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
 VON AS 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 AS sys.dm_db_missing_index_details ddmid
 ON ddmig.index_handle = ddmid.index_handle
 INNER JOIN sys.tables AS t
 ON ddmid.OBJECT_ID = t.OBJECT_ID
 WO 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

Nach und nach, je mehr Daten hinzugefügt, aktualisiert oder gelöscht wird, werden die Indizes fragmentiert und können die Leistung beeinträchtigen erheblich. Es ist daher wichtig, eine Politik zur Überwachung der Fragmentierung Index und Handeln im Falle von zu viel Fragmentierung.

In meinem Fall habe ich ein Wochenende Arbeit, dass das Verfahren dba_IndexDefrag_sp führt Michelle Ufford laufenden bin. Dieses Verfahren ermöglicht es Ihnen, alle Indizes aller Datenbanken und Servern zu überprüfen, je nach dem Grad der Fragmentierung, zu reorganisieren oder vollständig zu regenerieren. Die Optionen erlauben das Verfahren zur Fragmentierung Ebenen Auslösung der beiden Maßnahmen zu ändern, geben Sie einen Basis-oder eine einzelne Tabelle zu überprüfen oder die Anzahl der Prozessoren ermitteln zu verwenden.

5. Letzte Gebraucht Tabellen

Abhängig von Ihrer Produktionsumgebung kann es vorkommen, dass eine sehr große Anzahl von Tabellen, die von den Benutzern aufgegeben dann links erstellt wird. Dies kann zum Beispiel für die Einfuhr von Dateien passieren, dass kann dann interessant, in einer anderen Stelle auf dem SQL-Server zu überprüfen. Bleibt, ob diese Tabellen noch verwendet werden oder nicht, vor der Archivierung. Hierfür können Sie das folgende Skript verwenden, die Ihnen die letzten Tage des Lesens und Schreibens Tabellen in einer bestimmten Datenbank enthalten:

 SET AUS ANSI_WARNINGS; SET NOCOUNT ON; GO MIT agg AS (SELECT [object_id], last_user_seek, last_user_scan, last_user_lookup, last_user_update sys.dm_db_index_usage_stats FROM 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 AUS agg) AS x ([object_id], last_read, last_write) GROUP BY OBJECT_SCHEMA_NAME ([object_id]), object_name ([object_id]) ORDER BY 1 , 2;