CSV File Merger - schneller Weg

Nachdem ich vor kurzem mit meinem CSV File Merger Skript Laufzeitprobleme hatte (beide Files im GB Bereich), musste ich mir eine andere bzw schnellere Lösung überlegen.

Diese habe ich nun mit dem System.IO.StreamReader/Writer umgesetzt. Läuft für zwei Stück CSV Files mit je 500 MB nur wenige Sekunden lang. Somit um ein vielfaches schneller als meine alte Lösung und auch ressourcensparender. Die alte Version ist bei zu großen Files irgendwann wegen zuwenig Speicher abgestürzt. Hoffentlich läuft die neue Version besser.

Syntax:

<#
.VERSION
   Produced By   : Stefan Perner
   URL           : www.sqlwithpleasure.blogspot.co.at
   Author        : Stefan Perner
   Date          : 2015-09-23
   Purpose       : CSV mergen
   Modifications : no changes, as perfect           

.DESCRIPTION
Das Skript kopiert 1-n CSV-Dateien zusammen, nimmt den Header-Satz jedoch nur einmal mit
        
.EXAMPLE
.\CSVMerger Inpfile1,Inpile2,Inpfile3 Outputfile

#>
         
param(            
    [string[]]$CSVFiles,            
    [string]$OutputFile = "c:\merged.csv"            
)            
$i = 0
$outs = New-Object System.IO.StreamWriter $OutputFile
            
foreach($CSV in $CSVFiles) {            
    $ins = New-Object System.IO.StreamReader $CSV

    while( !$ins.EndOfStream ) {
        $line = $ins.ReadLine();
        if( $i -ne 0 ) {
            $outs.WriteLine($line);
        }
        $i = $i+1;
    }
    $ins.Close();
            
}            

$outs.Close();     

CSV-File mergen

Ja, eigentlich heißt der Blog "sqlwithpleasure", aber manchmal muss man auch über den Tellerrand blicken. Deshalb - und weil ich in letzter Zeit mehr mit Control-M etc verbringe - heute wieder ein kleiner Post bzgl PowerShell.

Wir hatten ein recht simples Problem, dass wir tourlich verschiedene CSV-Files angeliefert bekommen, die jedoch dann auf eines zusammengeführt werden sollen. Natürlich wieder mal eine kleine Powershell-Lösung. Vielleicht benötigt ja jemand mal was ähnliches.

Syntax:

<#
.VERSION
   Produced By   : Stefan Perner
   URL           : www.sqlwithpleasure.blogspot.co.at
   Author        : Stefan Perner
   Date          : 2015-09-23
   Purpose       : CSV mergen
   Modifications : no changes, as perfect           

.DESCRIPTION
Das Skript kopiert 1-n CSV-Dateien zusammen, nimmt den Header-Satz jedoch nur einmal mit
        
.EXAMPLE
.\CSVMerger Inpfile1,Inpile2,Inpfile3 Outputfile

#>
         
param(            
    [string[]]$CSVFiles,            
    [string]$OutputFile = "c:\merged.csv"            
)            
$Output = @();            
foreach($CSV in $CSVFiles) {            
    if(Test-Path $CSV) {                               
        $temp = Import-CSV -Path $CSV             
        $Output += $temp                   
    } else {            
        Write-Warning "$CSV : No such file found"            
    }            
            
}            
$Output | Export-Csv -Path $OutputFile -NoTypeInformation            
Write-Output "$OutputFile successfully created"      

Statistik über aufgetretene Wait Types

Wenn dieser Artikel gepostet wird, befind ich mich schon im Urlaub und somit auf meiner Reise in den Kaukasus.
Aber ich habe schon vorab ein interessantes Skript vorbereitet:
Dieses Skript summiert die aufgetretenen Waits nach Typ auf und zeigt an, wie oft sie auftreten, wie lange gewartet wurde, stellt sie in prozentualer Relation dar und berechnet auch die durchschnittliche Wartezeit.
Anhand dieser Liste bekommt man oft einen Überblick, wo es am MS SQL Server kränkelt. Einen Artikel über die diversen Wait-Types, welche gut und welche böse sind, werde ich nach meiner Rückkehr verfassen.

Syntax:

WITH [Waits] AS
    (SELECT [wait_type]
           ,[wait_time_ms] / 1000.0 AS [WaitS]
           ,([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS]
           ,[signal_wait_time_ms] / 1000.0 AS [SignalS]
           ,[waiting_tasks_count] AS [WaitCount]
           ,100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage]
           , ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER'             
       ,N'BROKER_RECEIVE_WAITFOR'
       ,N'BROKER_TASK_STOP'               
       ,N'BROKER_TO_FLUSH'
       ,N'BROKER_TRANSMITTER'              
       ,N'CHECKPOINT_QUEUE'
       ,N'CHKPT'                           
       ,N'CLR_AUTO_EVENT'
       ,N'CLR_MANUAL_EVENT'                
       ,N'CLR_SEMAPHORE'
       ,N'DBMIRROR_DBM_EVENT'              
       ,N'DBMIRROR_EVENTS_QUEUE'
       ,N'DBMIRROR_WORKER_QUEUE'           
       ,N'DBMIRRORING_CMD'
       ,N'DIRTY_PAGE_POLL'                 
       ,N'DISPATCHER_QUEUE_SEMAPHORE'
       ,N'EXECSYNC'                        
       ,N'FSAGENT'
       ,N'FT_IFTS_SCHEDULER_IDLE_WAIT'     
       ,N'FT_IFTSHC_MUTEX'
       ,N'HADR_CLUSAPI_CALL'               
       ,N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
       ,N'HADR_LOGCAPTURE_WAIT'            
       ,N'HADR_NOTIFICATION_DEQUEUE'
       ,N'HADR_TIMER_TASK'                 
       ,N'HADR_WORK_QUEUE'
       ,N'KSOURCE_WAKEUP'                  
       ,N'LAZYWRITER_SLEEP'
       ,N'LOGMGR_QUEUE'                    
       ,N'ONDEMAND_TASK_QUEUE'
       ,N'PWAIT_ALL_COMPONENTS_INITIALIZED'
       ,N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
       ,N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
       ,N'REQUEST_FOR_DEADLOCK_SEARCH'     
       ,N'RESOURCE_QUEUE'
       ,N'SERVER_IDLE_CHECK'               
       ,N'SLEEP_BPOOL_FLUSH'
       ,N'SLEEP_DBSTARTUP'                 
       ,N'SLEEP_DCOMSTARTUP'
       ,N'SLEEP_MASTERDBREADY'             
       ,N'SLEEP_MASTERMDREADY'
       ,N'SLEEP_MASTERUPGRADED'            
       ,N'SLEEP_MSDBSTARTUP'
       ,N'SLEEP_SYSTEMTASK'                
       ,N'SLEEP_TASK'
       ,N'SLEEP_TEMPDBSTARTUP'             
       ,N'SNI_HTTP_ACCEPT'
       ,N'SP_SERVER_DIAGNOSTICS_SLEEP'     
       ,N'SQLTRACE_BUFFER_FLUSH'
       ,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
       ,N'SQLTRACE_WAIT_ENTRIES'           
       ,N'WAIT_FOR_RESULTS'
       ,N'WAITFOR'                         
       ,N'WAITFOR_TASKSHUTDOWN'
       ,N'WAIT_XTP_HOST_WAIT'              
       ,N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
       ,N'WAIT_XTP_CKPT_CLOSE'             
       ,N'XE_DISPATCHER_JOIN'
       ,N'XE_DISPATCHER_WAIT'              
       ,N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
 )
SELECT
    MAX ([W1].[wait_type])          AS [WaitType],
    CAST (MAX ([W1].[WaitS])        AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS])    AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS])      AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount])          AS [WaitCount],
    CAST (MAX ([W1].[Percentage])                           AS DECIMAL (5,2))  AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount]))     AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount]))   AS DECIMAL (16,4)) AS [AvgSig_S]
 FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
   ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95
;

Langläufer suchen und warnen

Kurz vor meiner Urlaubspause (ja, auch ich darf mal) gibt es noch ein neues Skript.
Das unten angeführte Skript nehme ich gerne zur Laufzeitüberwachung von diversen Queries. Es passiert immer wieder, dass Queries, welche normalerweise nur einige Sekunden oder Minuten laufen, plötzlich von einem Tag auf den anderen schier unendlich laufen. 
Da natürlich viele Abfragen und Programme gleichzeitig Laufen, jedesmal zu einem unterschiedlichen Zeitpunkt, sollte man dies automatisiert überwachen.
In meinem Beispielskript habe ich zusätzlich eine Variable @Laufzeitgrenze eingebaut, welche wir derzeit via Control-M als Schwellwert mitgeben. Sollte die Abfrage ein Ergebnis zurückliefern wird automatisch ein Mail generiert, welches auf einen Langläufer hinweist. 
Danach erfolgt die übliche Analyse. Indizes, Statistiken, PlanHandle wegwerfen, Neustart.

Hat jemand Ideen zur Erweiterung oder Vereinfachung? Oder hat jemand einen komplett anderen Ansatz um dies zu bewerkstelligen? Freue mich über jede Rückmeldung

Syntax:

DECLARE @Laufzeitgrenze AS datetime
SET @Laufzeitgrenze = '00:05:00:000' -- 5 Minuten
-- SET @Laufzeitgrenze = '01:00:00:000' -- 1 Stunde

SELECT 'Schwellwert überschritten!!!' AS Warnung  
      ,[SID]   
      ,[STATUS]  
      ,[DOP]  
      ,[BLOCKED]  
      ,[DBNAME]  
      ,[PROGRAM_NAME]  
      ,CONVERT(CHAR(12),((CURRENT_TIMESTAMP)-start_time),14) AS [LAUFZEIT]
      ,[LOGINNAME]  
      ,[COMMAND]  
      ,SQLStatement  
      ,start_time  
      ,[PLAN_HANDLE]
FROM
  ( SELECT req.session_id AS [SID]  
          ,sqltext.TEXT AS [SQLStatement]  
          ,DB_NAME(req.database_id) AS [DBNAME]  
          ,req.status AS [STATUS]  
          ,mg.dop AS [DOP]
          ,p.blocked AS [BLOCKED]  
          ,req.command AS [COMMAND]  
          ,req.start_time AS start_time  
          ,p.loginame AS [LOGINNAME]  
          ,p.program_name AS [PROGRAM_NAME]  
          ,req.plan_handle AS [PLAN_HANDLE]
    FROM sys.dm_exec_requests req WITH (NOLOCK) 
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) sqltext 
   CROSS APPLY sys.dm_exec_query_plan (plan_handle) eqp
   INNER JOIN sys.sysprocesses p WITH (NOLOCK) ON p.spid = req.session_id
    LEFT JOIN sys.dm_exec_query_memory_grants mg WITH (NOLOCK) 
      ON req.session_id = mg.session_id
   WHERE req.session_id > 50
     AND req.session_id NOT IN (@@SPID)
     AND p.loginame <> ''
     AND (CURRENT_TIMESTAMP)-start_time > @Laufzeitgrenze) sub
ORDER BY start_time ASC

String in Files mittels PowerShell suchen

Ja, eigentlich heißt der Blog "sqlwithpleasure", aber manchmal muss man auch über den Tellerrand blicken. Deshalb heute wieder ein kleiner Post bzgl PowerShell.
Täglich werden tausende Logfiles produziert, diese (historisch) zu durchforsten ist manchmal notwendig, aber mitunter recht mühsam.
Natürlich gibt es im Explorer die Möglichkeit Dateiinhalte zu durchsuchen. Damit bin ich jedoch nicht immer ganz glücklich geworden, also hab ich mir selbst etwas gebastelt.

Ein kleines Powershellskript übernimmt diese Arbeit. Mitgegeben wird der Suchpfad, der Suchtext, ein Pattern um uU nur einen Bruchteil der Files in einem Ordner zu durchsuchen, ob die Suche rekursiv (auf alle Unterornder) erfolgen soll, oder nur im angegebenen Verzeichnis und auch noch, wie die Ausgabe erfolgen soll. HTML kann hier eine praktikable Lösung sein. Man kann einfach und schnell mittels HTML-Links durch die Files navigieren.

Gibt es Erweiterungsideen? Vorschläge zur Verbesserung? 
Freue mich wie immer auf jede einzelne Rückmeldung.

Syntax:

<#
.VERSION
   Produced By   : Stefan Perner
   URL           : www.sqlwithpleasure.blogspot.co.at
   Author        : Stefan Perner
   Date          : 2015-07-30
   Purpose       : Files nach String durchsuchen
   Modifications : no changes, as perfect           

.DESCRIPTION
Das Skript sucht nach einem STRING in allen Dateien eines Verzeichnisses.
 
.EXAMPLE
.\SearchForStringInFilesRecursive.ps1 

#>

param(
 # PATH -> Gibt an in welchem Verzeichnis gesucht wird
 [Parameter(Mandatory=$True)]
    [string]$Path = "\\myserver\Logfiles\",
 # TEXT -> Gibt an nach welchem TEXT gesucht wird
    [Parameter(Mandatory=$True)]
    [string]$Text = "Timeout",
    # PATTERN -> Hier kann man die zu durchsuchenden Files mit Wildcards einschränken - zB CDW*.txt
    [Parameter(Mandatory=$True)]
    [string]$Pattern = "A*.txt",
 # RECURS -> gibt an ob die suche rekursiv auch alle Subordner einschließen soll, oder nicht -> Y oder N
    [Parameter(Mandatory=$False)]
    [string]$Recurs = "Y",
 # OUTPUT -> gibt an, wie der Output erfolgt. Console = nur Joblog, HTML = HTML File, TEXT = TXT File, ALL = Alle varianten
 #   Outputordner: \\myserver\Suchergebnis\
    [Parameter(Mandatory=$False)]
    [ValidateSet('ALL','CONSOLE','HTML', 'TEXT')]
    [string]$Output = "ALL"
)


$PathArray = @()
$Results = "\\myserver\Suchergebnis\Suchergebnis_" + "$Text"+ "_" +(Get-Date -format yyyy_MM_dd_HH_mm_ss) 
$ResultsHTML = $Results + ".html"
$ResultsText = $Results + ".txt"

Write-Host ("Suchstart: " + (Get-Date))


#Entscheidung ob rekursiv durchsucht wird oder nicht
if ( $Recurs = 'Y' )
{
    Get-ChildItem $Path -Filter $Pattern -Recurse | 
       Where-Object { $_.Attributes -ne "Directory"} | 
          ForEach-Object { 
             If (Get-Content $_.FullName | Select-String -Pattern $Text) {
                $PathArray += $_.FullName
             }
          } 
}
else
{
    Get-ChildItem $Path -Filter $Pattern | 
       Where-Object { $_.Attributes -ne "Directory"} | 
          ForEach-Object { 
             If (Get-Content $_.FullName | Select-String -Pattern $Text) {
                $PathArray += $_.FullName
             }
          }
}

Write-Host ("Suchende : " + (Get-Date))
Write-Host "------------------------------------------------------------------------------------------"
Write-Host ("Suchordner         :" + $Path) 
Write-Host ("Suchtext           :" + $Text)
Write-Host ("Durchsuchte Files  :" + $Pattern) 
Write-Host ("Rekursive Suche    :" + $Recurs)
Write-Host ("Outputordner       :" + $Output)
Write-Host ("Outputordner       :" + $Results)
Write-Host "------------------------------------------------------------------------------------------"

Switch($Output)
 {
 'CONSOLE'{ 
            Write-Host "String in folgenden Files gefunden:"
            $PathArray | ForEach-Object {$_}
   }
 'HTML'{ 
            $PathArray | % {'' + $_ + '
'} | Out-File $ResultsHTML
  }  
    'TEXT'{ 
            $PathArray | % {$_} | Out-File $ResultsText
  }  
    'ALL'{ 
            Write-Host "String in folgenden Files gefunden:"
            $PathArray | ForEach-Object {$_}
            $PathArray | % {'' + $_ + '
'} | Out-File $ResultsHTML
            $PathArray | % {$_} | Out-File $ResultsText
  } 
 }

Tabellen mit den meisten Datensätzen

Nachdem mein letzter Post schon ein Weilchen her ist, hier ein kleiner Post für zwischendurch.
Miniskript um die Tabellen mit den meisten Datensätzen einer DB ausfindig zu machen. Listet neben dem Tabellennamen auch den Schemanamen mit auf.
Hinweis:
is_ms_shipped schließt die vom SQL Server mitgelieferten Tabellen aus dieser Übersicht aus.

Syntax:

USE MyDatabase
GO

SELECT sysSchem.name     AS Schemaname
      ,sysTab.name       AS Tabellenname
      ,SUM(sysPart.rows) AS Anzahl
 FROM sys.tables sysTab
INNER JOIN sys.partitions sysPart
   ON sysPart.OBJECT_ID = sysTab.OBJECT_ID
INNER JOIN sys.schemas sysSchem
   ON sysTab.schema_id = sysSchem.schema_id
WHERE sysTab.is_ms_shipped = 0 
  AND sysPart.index_id IN (1,0)
GROUP BY sysSchem.name
        ,sysTab.name
ORDER BY SUM(sysPart.rows) DESC

UpdateStatistic Skript

Eine weitere regelmäßige Tätigkeit ist es, die Statistiken aktuell zu halten.
Anbei ein kleines Skript, welches in der Erstversion bereits einige Steuerungsmöglichkeiten enthält. 

USE [MyDatabase]
GO

---------------------------------------------------
-- Produced By   : Stefan Perner
-- URL           : www.sqlwithpleasure.blogspot.co.at
-- Author        : Stefan Perner 
-- Date          : 2015-07-17
-- Purpose       : Database Maintenance - Statisticupdate
-- Modifications : no changes, as perfect
------------------------------------------------------------

/*#####################################################################*/
/*############ DIESE WERTE VOR DER AUSFÜHRUNG ÄNDERN ! ################*/
/*#####################################################################*/
DECLARE @Resample             int    = 0;    

     -- 0|1 Schalter - wenn 1, erfolgt der StatUpd. mit Resample,
     -- und somit wie bei der letzten Durchführung
     -- wenn 0, wird nach Satzanz. gesteuert, wieviele gesampelt werden.
DECLARE @Exec                 int    = 0;    

     -- 0|1 Schalter - wenn 1, wird das SQL auch ausgeführt, 

     --0 ist nur für Analyse
DECLARE @Print                int    = 1;    

     -- 0|1 Schalter - wenn 1, wird das SQL zusätzlich ausgegeben
/*######################################################################*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @SatzanzahlVeraendert int = 0;
DECLARE @Satzanzahl           int = 0;
DECLARE @SchemaName           nvarchar(130);
DECLARE @TableName            nvarchar(130);
DECLARE @IndexName            nvarchar(130);
DECLARE @Command              nvarchar(4000);

/*
   Für den Statistikupdate relevante Daten ermitteln
   Einschränkung auf Indizes, welche überhaupt veränderte Daten haben, 
   Microsoft interne Indizes werden ausgeschlossen
   Optional kann hier eingeschränkt werden, dass nur Indizes mit einer mind./max. Anzahl 
     von Sätzen berücksichtigt werden
*/

SELECT sSchem.name          AS SchemaName
  , sTab.name        AS TableName
  , sInd.name        AS IndexName
  , ssInd.rowcnt     AS Satzanzahl
  , ssInd.rowmodctr  AS SatzanzahlVeraendert
  INTO #IndizesFuerUpdateStatistic
  FROM sys.indexes AS sInd
 INNER JOIN sys.sysindexes AS ssInd 
    ON sInd.object_id     = ssInd.id
   AND sInd.name          = ssInd.name
 INNER JOIN sys.tables AS sTab 
    ON sTab.[object_id]   = sInd.[object_id]
 INNER JOIN sys.schemas AS sSchem 
    ON sSchem.[schema_id] = sTab.[schema_id]
 WHERE sTab.is_ms_shipped = 0 -- Only application indexes
   AND ssInd.rowcnt       > 100 -- Only indexes with at least 100 rows
   AND ssInd.rowmodctr    > 0 -- Only indexes with changed data
 
-- Cursor deklarieren und öffnen
DECLARE C_Indizes CURSOR FOR SELECT * FROM #IndizesFuerUpdateStatistic;
OPEN C_Indizes;

-- Schleife zum abarbeiten der Statistikupdates - hier wird auch gesteuert, 
-- wie die Statisticupdates durchgeführt werden.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM C_Indizes
INTO @SchemaName, @TableName, @IndexName, @Satzanzahl, @SatzanzahlVeraendert;
 BEGIN TRY
   IF @@FETCH_STATUS < 0 BREAK; -- wenn kein Satz mehr im Cursor, dann Schleife verlassen

   SET @Command = N'UPDATE STATISTICS '
                + QUOTENAME(@SchemaName) 
                   + N'.' 
                   + QUOTENAME(@TableName)
                   + N' ' 
                   + QUOTENAME(@IndexName) 
   IF @Resample = 1
   BEGIN
  SET @Command = @Command + N' WITH RESAMPLE';
      END
      ELSE IF @Resample = 0
      BEGIN
        SET @Command = @Command 
                   + N' WITH SAMPLE '
                   + CASE
                       WHEN @Satzanzahl < 100000     THEN N'100 PERCENT'
                       WHEN @Satzanzahl < 500000     THEN N'50 PERCENT'
                       WHEN @Satzanzahl < 10000000   THEN N'25 PERCENT'
                       WHEN @Satzanzahl < 30000000   THEN N'10 PERCENT'
                       WHEN @Satzanzahl < 50000000   THEN N'5 PERCENT'
                       WHEN @Satzanzahl < 100000000  THEN N'1 PERCENT'
                       ELSE N'500000 ROWS '
                     END
      END
--
      SET @Command = @Command + N' -- ' + CAST(@Satzanzahl AS VARCHAR(22)) + N' rows'
--
      IF @Print = 1
        PRINT N'Abzusetzendes Statement: ' +  @Command
--
      IF @Exec = 1
        EXEC (@Command)
--
 BEGIN TRAN
 COMMIT TRAN
 END TRY
 BEGIN CATCH
 ROLLBACK TRAN
 PRINT 'ERROR ENCOUNTERED'
 END CATCH
END; -- Ende der Schleife!

-- Cursor schließen und auch deallokieren
CLOSE C_Indizes;
DEALLOCATE C_Indizes;


DROP TABLE #IndizesFuerUpdateStatistic

Maintenance Skript - Index Reorg/Rebuild, Update Statistics und Procedure Cache leeren

Bereits vor einigen Tagen habe ich einen Post über Index Rebuild und Reorg, sowie auch über Statistikupdates geschrieben und ein kleines Skript zur Verfügung gestellt.
Dieses Skript habe ich mittlerweile etwas erweitert. Es gibt mehr Einstellungs- und Steuermöglichkeiten.
Weiters wird kann nun automatisch nach einem Index-Reorg ein automatisches Statistikupdate durchgeführt werden, falls gewünscht.
Darüber hinaus kann auch der Procedure Cache der gesamten Datenbank geleert werden. Dies führt dazu, dass bei Neudurchführungen ein neuer Zugriffsplan vom Optimizer errechnet wird.

Zusätzlich gibt es auch die Möglichkeiten Statistiken auszugeben. Die Anzahl der durchzuführenden Arbeitsschritte wird errechnet, die Zeit je Schritt mitprotokolliert und daraus resultierend eine ungefähre Hochrechnung und somit Endzeit der Arbeitsschritte errechnet.
Achtung! Diese Zeit ist am Anfang der Skriptdurchführung sehr ungenau und wird erst im Laufe der Durchführung immer genauer, da kleine Indizes die Zielzeit natürlich nach unten drücken und wenn ein sehr großer Index dabei ist, entwickelt sich die Zieleit natürlich auch in die andere Richtung.

Am Skriptende wird auch eine kurze Zusammenfassung ausgegeben, welche ein Anhaltspunkt für weitere Durchführungen und der Einplanung von Wartungsfenstern sein kann.

USE [MyDatabase]
GO

/* 
 Mit diesem Skript erfolgt eine Indexpflege auf der oben angeführten (USE [abc]) Datenbank.
 Sofern nur ein bestimmtes Zeitfenster zur Verfügung steht, kann variablengesteuert (@reorgrebuiltlimit) ein Zeitlimit in Minuten mitgegeben werden
 Ist die gesamthafte Verarbeitung zu diesem Zeitpunkt noch nicht beendet, wird kein neuer Index-Reorg/Rebuild mehr gestartet.
 Jedoch wird der aktuelle Reorg/Rebuild noch abgeschlossen. Dies kann dazu führen, dass das Endlimit überschritten wird. Bitte berücksichtigen!

 Jeder Index-Reorg/Rebuild wird in einer eigenen Transaktion abgewickelt, dies hat den Grund, dass ansonsten das Transaction Log bei einem
 Reorg von sehr vielen Indizes stark anschwillt und darüber hinaus ein Rollback aller bereits reorganisierten Indizes nicht wünschenswert ist.
 Sprich: Im Fehlerfall wird nur ein Rollback auf den aktuell bearbeiteten Index durchgeführt

 Mittels Variablen können die zu wartenden Indizes eingegrenzt werden in Hinsicht auf Fragmentierung, Größe etc.
 Weiters kann eingestellt werden, wieviele Prozessoren verwendet werden sollen (MAXDOP)
 Darüberhinaus besteht die Möglichkeit, den ProcedureCache am Ende des Skripts für die gesamte DB zu leeren und zukünftige Zugriffe somit 
 zu einer Neuberechnung des Zugriffsplans zu zwingen

 Optional gibt es auch eine Forecast-Option (@avgruntime), welche nach einer mitgegebenen Anzahl von Arbeitsschritten
 jeweils die vorraussichtliche Endzeit der ganzen Verarbeitung hochrechnet. Achtung, hat natürlich einen kleinen Performancenachteil
 und wird erst im Laufe der Verarbeitung immer genauer.
*/

---------------------------------------------------
-- Produced By   : Stefan Perner 
-- URL           : www.sqlwithpleasure.blogspot.co.at
-- Author        : Stefan Perner 
-- Date          : 2015-07-06
-- Purpose       : Database Maintenance 
-- Modifications : no changes, as it is perfect
------------------------------------------------------------

/*##################################################################################################################*/
/*###################### DIESE WERTE VOR DER AUSFÜHRUNG ÄNDERN ! ###################################################*/
/*##################################################################################################################*/
DECLARE @page_count_minimum smallint  = 500;  -- nur Indizes größer diesem Pagewert werden berücksichtigt
DECLARE @fragmentation_minimum float  = 10.0; -- nur Indizes mit Fragmentierung größer dem Prozentwert werden berücksichtigt
DECLARE @reorgrebuiltlimit int        = 30;   -- unter diesem Schwellwert wird ein Reorg durchgeführt, darüber ein Rebuilt 
DECLARE @onlyanalyze int              = 1;    -- 0|1 Schalter - wenn 1, dann wird der Reorg/Rebuilt nicht durchgeführt, 
                                              --                jedoch die generierten Statements aufgelistet
DECLARE @maxruntimeminutes int        = 180;  -- maximales Wartungsfenster in Minuten
DECLARE @maxdopactive int             = 1;    -- 0|1 Schalter - wenn 1, dann wird MAXDOP bei Index Rebuild/Reorg verwendet
DECLARE @maxdopvalue int              = 4;    -- MaxDOP - Degree of Parallelism - Anzahl Prozessoren, welche verwendet werden  
DECLARE @avgruntime int               = 1;    -- 0|1 Schalter - wenn 1, dann wird eine hochgerechnete Endzeit ausgegeben.
DECLARE @avgruntimeevery int          = 10;    -- bestimmt, wie oft die Endzeit hochgerechnet wird 
                                              --                nur wirksam, wenn @avgruntime gesetzt ist
DECLARE @clearprocfordb int           = 1;    -- 0|1 Schalter - wenn 1, wird Proc Cache der DB am Ende des Skripts geleert!
/*##################################################################################################################*/

-- Ensure a USE  statement has been executed first.
SET NOCOUNT ON;
-- Cursor nicht durch Commit schließen lassen!
SET CURSOR_CLOSE_ON_COMMIT OFF;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @C_Indizes bigint;
DECLARE @frag float;
DECLARE @pagecount int;
DECLARE @command nvarchar(4000);
DECLARE @starttime DATETIME;
DECLARE @starttimeindex DATETIME;
DECLARE @workuntiltime DATETIME;
DECLARE @estimatedendtime DATETIME;
DECLARE @amountofindices int;
DECLARE @processingcounter int = 0;
DECLARE @reorgcounter int = 0;
DECLARE @rebuildcounter int = 0;
DECLARE @statcounter int = 0;
--
-- Startzeitpunkt und Abbruchzeitpunkt (wenn mit Arbeit nicht fertig) werden berechnet
SET @starttime = GETDATE();
PRINT N'Index Defragmentierungsjob gestartet um  ' + Convert(varchar(11),@starttime);
SET @workuntiltime = DATEADD(mi,@maxruntimeminutes,GETDATE());

-- Indizes anhand der obangeführten Einschränkungskritierien selektieren
SELECT object_id AS objectid,
       index_id AS indexid,
       partition_number AS partitionnum,
       avg_fragmentation_in_percent AS frag,
       page_count AS page_count
 INTO #work_to_do
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
  AND index_id > 0
  AND page_count > @page_count_minimum;

SELECT @amountofindices = COUNT(*) FROM #work_to_do;
PRINT N'In diesem Lauf werden vorraussichtlich ' + Convert(varchar(11),@amountofindices) +  N' Indizes verarbeitet';

PRINT N'Falls die Verarbeitung um ' +   CAST(CONVERT(datetime2, @workuntiltime, 126) AS nvarchar(30))  + N' noch nicht abgeschlossen ist, beendet sich das Skript von selbst!';
PRINT N' ';
PRINT N' ';

SET @starttimeindex = GETDATE();

-- 
DECLARE C_Indizes CURSOR FOR SELECT * FROM #work_to_do;
OPEN C_Indizes;

-- Alle oben als relevant eingestuften Indizes werden nun in einer Schleife abgearbeitet
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM C_Indizes
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;
 BEGIN TRY
 BEGIN TRAN
 SET @processingcounter = @processingcounter + 1;
 -- Exit, wenn alle gecachten Indizes abgearbeitet wurden
 IF @@FETCH_STATUS < 0 BREAK;
 -- wenn Ende des Wartungsfensters erreicht wurde, wird beendet 
 IF GETDATE() > @workuntiltime BREAK;

 -- Objekt- und Schemanamen ermitteln
 SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
 FROM sys.objects AS o
 JOIN sys.schemas as s ON s.schema_id = o.schema_id
 WHERE o.object_id = @objectid;

 -- Indexname ermitteln
 SELECT @indexname = QUOTENAME(name)
 FROM sys.indexes
 WHERE  object_id = @objectid AND index_id = @indexid;

 -- Partitionsanzahl ermitteln
 SELECT @partitioncount = count (*)
 FROM sys.partitions
 WHERE object_id = @objectid AND index_id = @indexid;

 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;

 -- Hier wird entschieden, ob ein REORG oder ein REBUILD eines Index vorgenommen wird 
 IF @frag > @reorgrebuiltlimit 
 BEGIN
  SET @command = @command + N' REBUILD';
  SET @rebuildcounter = @rebuildcounter + 1;
    END
 ELSE IF @frag <= @reorgrebuiltlimit 
 BEGIN
  SET @command = @command + N' REORGANIZE';
  SET @reorgcounter = @reorgcounter + 1;
    END

 IF @partitioncount > 1
 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

-- Parallelisierung - Falls gewünscht wird hier die Anzahl der Prozessoren, welche bei REBUILD verwendet werden, mitgegeben.
 IF @maxdopactive = 1 AND  @frag >= @reorgrebuiltlimit
    SET @command = @command + N' WITH (MAXDOP=' + CAST(@maxdopvalue AS nvarchar(10))  + N')'

 -- Protokollierung in Joblog ausgegeben.
 PRINT N'----------------------------------------------------------------------------';
 PRINT N'Arbeitsschritt ' + Convert(varchar(11),@processingcounter) + N' von ' + Convert(varchar(11),@amountofindices) + N' wird durchgeführt.'
 PRINT @command
 IF @frag > @reorgrebuiltlimit
  PRINT N'Rebuilding index ' + @indexname + ' on table ' + @objectname;
 ELSE IF @frag <= @reorgrebuiltlimit
  PRINT N'Reorganizing index ' + @indexname + ' on table ' + @objectname;
 PRINT N'  Fragmentation: ' + CAST(@frag AS varchar(15));
 PRINT N'  Page Count:    ' + CAST(@pagecount AS varchar(15));
 PRINT N'Start Index-Reorg/Rebuilt um : ' + convert(char(50), GETDATE(), 114) 

 -- Statement wird nun ausgeführt, wenn gewünscht
 IF @onlyanalyze = 0 EXEC (@command);
 PRINT N'Ende Index-Reorg/Rebuilt um  : ' + convert(char(50), GETDATE(), 114) 

 -- Wenn "nur" REORG durchgeführt wurde, dann werden nun auch noch die Statistiken upgedatet
 IF  @frag <= @reorgrebuiltlimit
 BEGIN
  SET @command = N'UPDATE STATISTICS ' + @schemaname + N'.' + @objectname
  PRINT N'Start Statistikupdate        : ' + convert(char(50), GETDATE(), 114) 
  IF @onlyanalyze = 0 EXEC (@command);
  SET @statcounter = @statcounter + 1;
  PRINT N'Ende Statistikupdate         : ' + convert(char(50), GETDATE(), 114) 
 END
 
 PRINT N' Arbeitsschritt ' + Convert(varchar(11),@processingcounter) + N' von ' + Convert(varchar(11),@amountofindices) + N' wird beendet.'

 -- wenn Hochrechnung der Endzeit erwünscht ist, wird sie hier ausgegben.
 IF @avgruntime = 1 AND @processingcounter % @avgruntimeevery = 0
 BEGIN
  PRINT N'Vorraussichtliches Ende des Skripts um: ' + 
        CAST(CONVERT(datetime2, DATEADD(millisecond,((DATEDIFF ( ms , @starttimeindex , GETDATE() ) / @processingcounter) * @amountofindices),@starttimeindex), 126) AS nvarchar(30))
 END

 PRINT N' ';

 COMMIT TRAN
 END TRY
 BEGIN CATCH
 ROLLBACK TRAN
 PRINT 'ERROR ENCOUNTERED'
 END CATCH
END; -- Ende der Schleife!

-- Cursor schließen und auch deallokieren
CLOSE C_Indizes;
DEALLOCATE C_Indizes;

-- temporäre Tabelle wieder entfernen
DROP TABLE #work_to_do;

--
-- Wenn gewünscht, wird noch der Procedure Cache geleert, damit bei Neudurchführungen ein neuer Zugriffsplan erstellt werden muss
IF @clearprocfordb = 1 AND @onlyanalyze = 0
BEGIN
 DECLARE @intDBID INT;
 SET @intDBID = (SELECT DB_ID() AS [Database ID]);
 DBCC FLUSHPROCINDB (@intDBID);
END
;

PRINT N' ';
PRINT N'----------------------------------------------------------------------------';
PRINT N'                 Zusammenfassung der Verarbeitung';
PRINT N'Index Defragmentierungsjob gestartet um  ' + convert(char(50), @starttime, 114);
PRINT N'Index Defragmentierungsjob beendet um    ' + convert(char(50), GETDATE(), 114);
PRINT N'Index REORGs                             ' + Convert(varchar(11),@reorgcounter);
PRINT N'Statistikupdates                         ' + Convert(varchar(11),@statcounter);
PRINT N'Index REBUILDs                           ' + Convert(varchar(11),@rebuildcounter);
PRINT N'Durchschn. Zeit je Index:                ' + Convert(varchar(11),(CAST(DATEDIFF ( millisecond , @starttimeindex , GETDATE() ) AS FLOAT) / @processingcounter) / 1000) + N' Sekunden';

Update Statistics und Statistikeinstellungen generell

Mittels dem Befehl UPDATE STATISTICS werden die Statistikwerte einer Tabelle aktualisiert. Dies wirkt sich auf den berechneten Zugriffsplan bei Abfragen aus. Sind die Statistiken nicht aktuell, kann es sein, dass mitunter ein (sehr) schlechter Zugriffsplan vom Optimizer errechnet wird, welcher zu massiv langsameren Abfragen führen kann.
Jedoch benötigt auch das Aktualisieren ebendieser Statistiken Zeit. Hier muss man selbst abwägen, ob der Zeitaufwand für die Statistikaktualisierung sich gegenüber der verbesserten Abfragezeit überhaupt rentiert. 

Syntax:

UPDATE STATISTICS table_or_indexed_view_name 
    [ 
        { 
            { index_or_statistics__name }
          | ( { index_or_statistics_name } [ ,...n ] ) 
                }
    ] 
    [    WITH 
        [ 
            FULLSCAN 
            | SAMPLE number { PERCENT | ROWS } 
            | RESAMPLE 
              [ ON PARTITIONS ( {  |  } [, …n] ) ]
            |  [ ,...n ]
        ] 
        [ [ , ] [ ALL | COLUMNS | INDEX ] 
        [ [ , ] NORECOMPUTE ] 
        [ [ , ] INCREMENTAL = { ON | OFF } ]
    ] ;

 ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]

Genaue Informationen gibt es im MSDN - Microsoft Developer Network
Update Statistics Information in MSDN

Standardmäßig werden Statistikupdates automatisch durchgeführt.  Dies wird über über die Tabelle sys.databases gesteuert.
Um die aktuellen Einstellungen zu prüfen, einfach folgendes Statement ausführen:

SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on 
FROM sys.databases

Die Option auto_update_stats_async_on beeinflusst, wie die Statistiken aktualisiert werden. Ist diese Option eingeschalten, wartet der Query Optimizer nicht auf aktuelle Statistikwerte, sondern führt die Query zuerst durch und aktualisiert die Statistiken erst anschließend. Die Query läuft somit mit den aktuell vorhandenen Werten, was natürlich zu suboptimalen Abfrageplänen führen kann. Mittels unten angeführten SQLs können die Einstellungen verändert werden.

ALTER DATABASE YourDBName 
SET AUTO_UPDATE_STATISTICS_ASYNC ON
--
ALTER DATABASE YourDBName 
SET AUTO_UPDATE_STATISTICS ON

Bei Online-Transaktions-Umgebungen wird empfohlen, das asynchrone updaten einzuschalten, im Bereich DataWarehouse nicht. Veränderungen an diesem Parameter können auch zu unvorhersehbaren Nachteilen führen - speziell wenn beide oben angeführte Bewirtschaftungstypen gemischt genutzt werden.

Datenbankzugriff mittels Powershell

Manchmal ist es auch praktisch, wenn man mittels PowerShell Skript auch direkt in eine Tabelle am MS SQL Server schreibt.
Wenn zum Beispiel tourlich PowerShell Skripts durchgeführt werden und deren Arbeit protokolliert werden soll. Durch diese Kombination kann man die volle - und das ist wirklich viel - Macht von Powershell für sich nutzen. 

## Variablen für Zugriff auf Datenbank
$Server = "mylovelyServer"
$sql ="INSERT INTO [dbo].[Testtable] ([wert1]) VALUES (123)" 
$Database = "mylovelyDatabase"
 
## Verbindung herstellen
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;MultipleActiveResultSets=True;"
$Connection.Open()
 
## SQL absetzen
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $sql
$Command.ExecuteReader()

## Verbindung schließen
$Connection.Close()

Will man mittels Powershell aus einer SQL Tabelle auslesen, so ist einfach das Ergebnis zu laden. Ein Beispiel hierfür: 

## Variablen für Zugriff auf Datenbank
$Server = "mylovelyServer"
$sql ="SELECT * [dbo].[Testtable]" 
$Database = "mylovelyDatabase"
 
## Verbindung herstellen
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;MultipleActiveResultSets=True;"
$Connection.Open()
 
## SQL absetzen
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $sql
$Ergebnis = $Command.ExecuteReader()

## Ergebnis laden und anzeigen

$Tabelle = new-object “System.Data.DataTable”
$Tabelle.Load($Ergebnis)

$Format = @{Expression={$_.wert1};Label=”wert1”;width=10}
$Tabelle | format-table $Format

## Verbindung schließen
$Connection.Close()

Procedure Statistiken

Eine sehr hilfreiche vom SQL Server zur Verfügung gestellte System-StoredProcedure heißt sys.dm_exec_procedure_stats
Mit ihr kann man sich in Windeseile einen Überblick über die Statistiken von Procedures verschaffen und einfach zeitintensive Prozeduren eruieren.
Die unten angeführte Query ermittelt die Top-10 der zeitaufwändigsten Durchführungen inklusive einiger wichtigen Attribute, wie die durchschnittliche Ausführungszeit und Anzahl der Durchführungen.
Eine Procedure, welche nicht nur zeitintensiv ist, sondern auch noch oft ausgeführt ist, sollte man sich auf jeden Fall genauer ansehen. Unter Umständen gibt es hier mit wenig Aufwand ein großes Optimierungspotential zu heben. 

-- Die Top 10 Abfragen mit dem längsten Zeitaufwand
SELECT TOP 10 
  d.object_id
 ,d.database_id
 ,OBJECT_NAME(object_id, database_id) 'procedure name'
 ,d.cached_time
 ,d.last_execution_time
 ,d.total_elapsed_time / 1000000 as GesamtzeitSek
 ,(d.total_elapsed_time/d.execution_count) / 1000000 AS DurchschzeitSek
 ,d.last_elapsed_time / 1000000 as LetzteDurchfZeitSek
 ,d.execution_count as AnzahlDurchfuehrungen
 ,d.sql_handle
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;

Expensive Queries - die Performancebremser

Von Zeit zu Zeit will man wissen, welche Statements gegen die Datenbanken abgesetzt wurden und sehr viel Ressourcen verbraucht haben. Anhand dieser Information kann man unter Umständen einen Performanceverbesserung ableiten, oder auch fehlerhafte Queries, welche nicht restriktiv genug einschränken ausfindig machen.
Natürlich ist es auch wichtig, wie oft ein Statement ausgeführt wurde. Denn ein Statement, dass zB 10000 mal ausgeführt wurde, jedoch jeweils nur sehr wenig CPU Zeit und IO benötigt hat, stört kaum. Falls ein Statement oft ausgeführt wird und eine Optimierung möglich ist, kann auf einfachem Wege eine Entlastung von CPU, IO etc erreicht werden.

/*
 Skript listet die "teuersten" Queries, des letzten Monats auf
 - über die Variable @MinDurchfuehr kann gsqlstatementeuert werden, 
   ab wievielen Durchführungen eine Query berücksichtigt wird.
 - über die Variable @Tage kann der Zeitraum gesteuert werden
   es werden die letzten @Tage ausgewertet 
*/

DECLARE @MinDurchfuehr int = 5; 
DECLARE @Tage int = 100;
 
SELECT querystats.total_worker_time AS SummeArbeitszeit 
      ,querystats.total_logical_reads + querystats.total_logical_writes AS SummeLogicalIO 
   ,querystats.total_worker_time / querystats.execution_count as DurchschnCPUZeit 
      ,(querystats.total_logical_reads + querystats.total_logical_writes) / querystats.execution_count AS DurchschnLogicalIO 
      ,querystats.execution_count As AnzahlAusfuehrung 
      ,querystats.last_execution_time AS LetzteDurchfuehrung
      ,DB.name AS Datenbank 
      ,SUBSTRING(sqlstatement.text,1 + querystats.statement_start_offset / 2 
                   ,(CASE WHEN querystats.statement_end_offset = -1  
                          THEN LEN(convert(nvarchar(max), sqlstatement.text)) * 2  
                          ELSE querystats.statement_end_offset 
        END  
                       - querystats.statement_start_offset) / 2 
                ) AS SqlStatement 
      -- Optionale Anzeige des Query Plans - wenn einkommentiert, dann läuft die Query viel länger!
      --,queryplan.[query_plan] AS [QueryPlan] 
FROM sys.dm_exec_query_stats AS querystats 
CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS sqlstatement 
--Wenn Query Plan benötigt, dann auch diese Zeile einkommentieren
--CROSS APPLY sys.dm_exec_query_plan(querystats.plan_handle) AS queryplan 
LEFT JOIN sys.databases AS DB 
  ON sqlstatement.dbid = DB.database_id      
WHERE querystats.execution_count > @MinDurchfuehr 
      AND querystats.last_execution_time > DATEADD(DAY, (@Tage * -1), GETDATE()) 
ORDER BY SummeArbeitszeit DESC 
        ,SummeLogicalIO DESC

Tabellengröße und Komprimierungsstatus

Skript zum ermitteln von Tabellengrößen und deren Komprimierungsstatus.
Hilfreich um die Komprimierungsmöglichkeit von (großen) Tabellen zu untersuchen.

/*
-- Ermittelt Tabellennamen, Satzanzahl und Komprimierungsstatus
*/
USE Y33_ZDW
GO

SELECT OBJECT_NAME(object_id) AS [Objektname], 
SUM(Rows) AS [Satzanzahl], 
data_compression_desc AS [Komprimierungstyp]
FROM sys.partitions WITH (NOLOCK)
WHERE index_id < 2 --Partitionen von non-clustered Indizes ausschl.
AND OBJECT_NAME(object_id) NOT LIKE N'sys%' -- Systemtab. ausschl.
AND OBJECT_NAME(object_id) NOT LIKE N'queue_%' -- Warteschlangentab. ausschl.
AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%' -- Tombstonetab. ausschl.
AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%' -- Fulltextkatalogtab. ausschl.
AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%'  -- Integrierte Full Text Suchtab. ausschl.
AND OBJECT_NAME(object_id) NOT LIKE N'xml_index_nodes%' -- XML-Indextab. ausschl.
GROUP BY object_id, data_compression_desc
ORDER BY SUM(Rows) DESC;

Loop through databases

Manchmal ist es notwendig ein bestimmtes Skript gegen alle bzw. einige Datenbanken eines Servers abzusetzen. Dies ist mit dem unten angeführten Skript leicht realisierbar. 
Die zu selektierenden Datenbanken können mit einer WHERE-Einschränkung auf die master.sys.sysdatabases noch gefiltert werden. 

/*
Dieses Script loopt durch alle vorhandenen Datenbanken auf einem Server.
*/

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR SELECT name FROM master.sys.sysdatabases 

--hier mit WHERE-Clause wenn gewünscht einschränken

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = 'SELECT ' + '''' + @DB_Name + ''''  

--Query, welche gegen jede Datenbank abgesetzt wird
     EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor

Index Rebuild

Im Laufe der Zeit steigt automatisch auch die Fragmentierung von vielen Indizes stetig an.
Je mehr sich in der betroffenen Tabelle ändert, desto höher wird die Fragmentierung. Dies wirkt sich natürlich auch auf die Durchführungszeit von unzähligen Queries aus.
Daher ist es ratsam regelmäßig die Indizes zu reorganisieren oder überhaupt neu aufzubauen.
Dies ist mit dem unten angeführten Skript einfach realisierbar. 

Mit der derzeitigen Einstellungen, werden alle Indizes, welche eine Fragmentierung über 10% aufweisen neu aufgebaut (rebuild oder reorg). Der Index Neuaufbau verbraucht mehr CPU, als ein Index-Reorg. In meinem konkreten Fall ist dies vernachlässigbar, da eine Beladung nur einmal täglich durchgeführt wird und danach auf diesem Beladeserver nicht viel los ist. 

Empfehlung:
Es gibt viele Einstellungsmöglichkeiten, jeder sollte ausprobieren, welche für seinen konkreten Fall, die ideale Lösung darstellt.
Grundsätzlich sollten Indizes, welche eine Fragmentierung über 30% aufweisen, mit einem REBUILD und Indizes mit einer Framentierung zwischen 10% und 30% mit einem REORG bereinigt werden.
Zusätzlich gibt es in der SQL Server Development Version und Enterprise Version die ONLINE-Option. Durch diese bleibt der Fragmentierte Index während der REBUILD-Phase weiterhin verfügbar.

/*
Dieses Skript überprüft in der angegebenen Datenbank alle Indizes bezüglich Fragmentierung.
Ist der Index zu fragmentiert - Schwellwert in % überschritten - wird ein Rebuild auf ebendiesen abgesetzt.
*/


USE MyDatabase -- $$change - Hier den Datenbanknamen einfügen
GO

BEGIN TRY
BEGIN TRAN

SET NOCOUNT ON;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @pagecount int;
DECLARE @command nvarchar(4000);

DECLARE @page_count_minimum smallint
SET @page_count_minimum   = 500 -- $$change - Hier Mindestwert für Pageanzahl der zu prüfenden Indizes angeben

DECLARE @fragmentation_minimum float
SET @fragmentation_minimum   = 10.0 -- $$change - Hier den Schwellwert der Fragmentierung in Prozent angeben

SELECT object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag,
page_count AS page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND index_id > 0
AND page_count > @page_count_minimum;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE  object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;

IF @frag > 30
 SET @command = @command + + N' REBUILD';
ELSE IF @frag <= 30
 SET @command = @command + + N' REORG';

IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
PRINT @command
EXEC (@command);

IF @frag > 30
 PRINT N'Rebuilding index ' + @indexname + ' on table ' + @objectname;
ELSE IF @frag <= 30
 PRINT N'Reorganizing index ' + @indexname + ' on table ' + @objectname;
PRINT N'  Fragmentation: ' + CAST(@frag AS varchar(15));
PRINT N'  Page Count:    ' + CAST(@pagecount AS varchar(15));
PRINT N' ';
END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
COMMIT TRAN

END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'ERROR ENCOUNTERED'
END CATCH