SQLServer

SELECT 
    DB_Name(database_id),* 
FROM 
    sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) 
WHERE 
    avg_fragmentation_in_percent > 0 
ORDER BY 
    avg_fragmentation_in_percent DESC ;  
GO  

Oder die Luxusvariante pro DB

SELECT 
    dbschemas.[name] AS 'Schema', 
    dbtables.[name] AS 'Table', 
    dbindexes.[name] AS 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
    INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
    INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
ORDER BY 
    indexstats.avg_fragmentation_in_percent DESC

Und um für eine DB alle Rebuild Queries zu bauen:

SELECT DISTINCT
	'ALTER INDEX All ON  ' + dbschemas.[name] + '.' + dbtables.[name] + ' REBUILD WITH (ONLINE = OFF, Statistics_norecompute = OFF)'
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.TABLES dbtables ON dbtables.[object_id] = indexstats.[object_id]
    INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
    INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
AND
	indexstats.avg_fragmentation_in_percent > 30

SELECT 
    percent_complete, 
    dateadd(SECOND,estimated_completion_time/ 1000, getdate()) AS est_completion_time
FROM 
    sys.dm_exec_requests
WHERE
    command = 'DbccFilesCompact'

Wenn man in einer Tabelle Daten löschen möchte, die in einer anderen Tabelle keinen Eintrag haben, nimmt man gern:

DELETE FROM X WHERE X.Id NOT IN (SELECT Y.Id FROM Y ...) 

Und wundert sich dann, warum das Ergebnis nicht ganz den Erwartungen entspricht

Das Problem dabei - NOT IN ist boolean gesprochen X.Id <> Y.Id1 and X.Id <> Y.Id2. Jetzt können aber dadurch auch Vergleiche entstehen X.Id <> NULL. Im Gegensatz zu Programmiersprachen ist ein Vergleich mit NULL nicht definiert, auf alle Fälle aber nicht TRUE und somit ist die gesamte WHERE Klausel FALSE und es wird gar nichts gelöscht…

Besser ist da mit

DELETE X WHERE NOT EXISTS ( SELECT 1 FROM Y WHERE X.Id = Y.Id ) 

Für Fortgeschrittene als MERGE (ab SQL2008, für mich nicht intuitiv )

MERGE X USING Y ON (X.Id = Y.Id) WHEN NOT MATCHED BY Y THEN DELETE

SELECT
    r.session_id AS spid
        ,r.cpu_time,r.reads,r.writes,r.logical_reads 
        ,r.blocking_session_id AS BlockingSPID
        ,LEFT(OBJECT_NAME(st.objectid, st.dbid),50) AS ShortObjectName
        ,LEFT(DB_NAME(r.database_id),50) AS DatabaseName
        ,s.program_name
        ,s.login_name
        ,OBJECT_NAME(st.objectid, st.dbid) AS ObjectName
        ,SUBSTRING(st.text, (r.statement_start_offset/2)+1,( (CASE r.statement_end_offset
                                                                  WHEN -1 THEN DATALENGTH(st.text)
                                                                  ELSE r.statement_end_offset
                                                              END - r.statement_start_offset
                                                             )/2
                                                           ) + 1
                  ) AS SQLText
    FROM sys.dm_exec_requests                          r
        JOIN sys.dm_exec_sessions                      s ON r.session_id = s.session_id
        CROSS APPLY sys.dm_exec_sql_text (sql_handle) st
    WHERE r.session_id!=@@SPID --uncomment to not see this query

Manchmal kann man nicht einfach Tabellen dropen bzw. die ganze Datenbank löschen. Dann könnten folgende Befehle hilfreich sein:

deleteAllRows.sql

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Muss man sehr viele Daten in einem Query verarbeiten, kann es sein, dass die Größe des Transaktion Logs nicht reicht.

In meinem Fall musste ich in einer Tabelle mit ca 85 Millionen Zeilen bearbeiten und das Delete umfasste ca 73M Einträge. Da waren selbst 100GB Transaktionlog noch zu klein.

Also muss man das in “Häppchen” machen…

splitTrans.sql

DECLARE @ROWS INT, @err INT
SELECT @ROWS = 1
SET rowcount 50000 -- in 50K chunks
BEGIN tran
while @ROWS > 0
BEGIN
DELETE FROM dbo.SensorValueHistory WHERE <Condition>
SELECT @ROWS = @@rowcount, @err = @@error
commit tran
-- check @err here for non zero, error handling is for wimps ;-) --
IF @ROWS >0
BEGIN tran
END

Vor der Ausführung sollte man die DB auf “SimpleRecovery” stellen. Dann bleibt beim Abarbeiten das Transaktion Log klein und außerdem geht die Abarbeitung wesentlich schneller…

Manchmal wachsen Datenbanken und man weiss nicht so recht warum. Mit einigen Queries kann man dies herausfinden.

Wie, ist im Artikel zur VMware vCenter Datenbank erläutert. Siehe dazu vCenter Datenbank zu Groß?.

Wenn mal die LOG Datei zu groß geworden ist und ein normales Backup mit abschneiden des Transaktionlogs nicht funktioniert (weil z.B. kein Platz mehr ist) kann man auch das Transaktionlog im Notfall ohne Backup abschneiden.

Erst einmal den Namen des Logfiles ermitteln:

SELECT name FROM sys.database_files 
WHERE type_desc = 'LOG'
GO

Danach schrinken…

DBCC SHRINKDATABASE ('vcenter01', TRUNCATEONLY)

Allgemeines Wachstum von Trans-Logs anschauen:

DBCC SQLPERF(logspace)

https://github.com/olahallengren/sql-server-maintenance-solution

Wie war das mit den JOINs ???:

https://www.codeproject.com/articles/33052/visual-representation-of-sql-joins