====== SQLServer ======

===== Infos über Indexfragmentierung etc. =====
<code sql>
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  
</code>

Oder die Luxusvariante pro DB

<code sql>
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
</code>

Und um für eine DB alle Rebuild Queries zu bauen:
<code sql>
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
</code>
 
===== Database Shrink - wie sehe ich den Fortschritt =====

<code sql>
SELECT 
    percent_complete, 
    dateadd(second,estimated_completion_time/ 1000, getdate()) as est_completion_time
FROM 
    sys.dm_exec_requests
WHERE
    command = 'DbccFilesCompact'
</code>

===== Select Where NOT in - der Fehler =====

Wenn man in einer Tabelle Daten löschen möchte, die in einer anderen Tabelle keinen Eintrag haben,
nimmt man gern:
<code sql>
DELETE FROM X WHERE X.Id not IN (SELECT Y.Id From Y ...) 
</code> 

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 
<code sql>
DELETE X WHERE NOT EXISTS ( SELECT 1 FROM Y WHERE X.Id = Y.Id ) 
</code>

Für Fortgeschrittene als MERGE (ab SQL2008, für mich nicht intuitiv :-) )
<code sql>
MERGE X USING Y ON (X.Id = Y.Id) WHEN NOT MATCHED BY Y THEN DELETE
</code>

===== blockierte SQL Prozesse =====
<code sql>
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
</code>

===== Delete All Rows in all Tables =====

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

<code sql 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
</code>

===== Transaction Rows verkleinern =====

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...

<code sql 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
</code>

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...

===== Analyse von Tabellengröße in einer Datenbank =====

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 [[vmware:vsphere#vcenter_datenbank_zu_gross|vCenter Datenbank zu Groß?]].

===== Transaction Log verkleinern =====

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:
<code sql>
SELECT name FROM sys.database_files 
WHERE type_desc = 'LOG'
go
</code>

Danach schrinken...
<code sql>
DBCC SHRINKDATABASE ('vcenter01', TRUNCATEONLY)
</code>

Allgemeines Wachstum von Trans-Logs anschauen:
<code sql>
DBCC SQLPERF(logspace)
</code>

===== Ola Hallengren =====

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


===== Sonstiges =====

Wie war das mit den JOINs ???:

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