Performance SQL

Hallo,

Performance wird sicherlich jeden Monat mal neu durchgekaut. (780 Beiträge)
Hier mal meine Frage dazu, da ich konkret noch nicht das richtige gefunden habe:

Welches ist ein guter Mix zwischen ndf und mdf Datei?

Wir haben eine ndf-Datei mit 38 GB und eine mdf Datei mit 487 MB
jetzt wollen wir unsere Datenbankdatei vergrössern um 10-15 GB.

Sollte man dann die mdf-Datei ebenfalls vergrössern?
Wenn ja, um welchen Wert?
Ist diese Datei überhaupt auf einem guten Wert eingestellt?

Die Frage ist noch wie voll ist die Datei denn? Grundsätzlich sollte die Datenbank bis kurz vor knapp gefüllt sein. Die Datenbank sollte unter 80 % der Dateigröße bleiben.

Was mdf und ndf angeht streiten sich viele und die Meinungen gehen weit auseinander.

Grundsätzlich sollte die Datenbank bis kurz vor knapp gefüllt sein. Die Datenbank sollte unter 80 % der Dateigröße bleiben.

Also ich habe in der SQL-Schulung gelernt, das man die Datenbankdatei mit den eigentlichen Daten schon am Anfang möglichst groß machen soll, damit sie im Dateisystem nicht fragmentiert. Das bedeutet auch, dass wenn man sie vergrößert, das nicht in 100MB- Schritten tun soll, sondern in ausreichend großen Blöcken, nicht aber prozentual, wie vorgeschlagen. Es gibt allerdings noch eine weitere Regel die besagt, dass man die DB-Datei nicht größer als 80% des Dateisystems machen soll, da NTFS sonst langsam wird.

Gruß, Fiddi

Kurz vor knapp gefüllt?

es ist eine Füllung von 99% vorhanden. (unter Datenbank->Informationen)
Das sollte aber doch nicht so sein, oder?
Ich kenne das das die Datenbank nur "bis zu 80%" gefüllt sein sollte.....alles andere wirkt sich auf die Performance aus.
Die Dateivergrösserung ist auf 512000 KB eingestellt. und nicht auf %

auf der Datenplatte haben wir noch 20 GB frei, die ich nutzen könnte.

es ist eine Füllung von 99% vorhanden. (unter Datenbank->Informationen)
Das sollte aber doch nicht so sein, oder?
Ich kenne das das die Datenbank nur "bis zu 80%" gefüllt sein sollte.....alles andere wirkt sich auf die Performance aus.

Das ist erst einmal nicht schlimm, wenn die DB auf automatische Vergrößerung steht. Das mit den 80% Füllgrad gilt für die Native- DB.

Die Dateivergrösserung ist auf 512000 KB eingestellt. und nicht auf %

Wenn eure SQL-DB 5GB groß ist , ist das O.K., ist sie aber 50GB groß, ist das keine gute Idee, da ihr sonst die DB-Datei(en) auf der Platte zu sehr fragmentiert. Die Datenbank sollte nicht jeden Tag vergrößert werden. Das kostet Geschwindigkeit, und fragmentiert zu sehr.
Ist der Jahresplatzbedarf der DB abschätzbar, solltest du SQL-DB auch um den Wert vergrößern (evtl. von Hand )

auf der Datenplatte haben wir noch 20 GB frei, die ich nutzen könnte.

Wenn das eine 73GB Platte ist ja, bei einer 146GB- Platte würde das die 80%- Dateisystemfüllgrad überschreiten, dort also nein.


Gruß, Fiddi

Ok, es ist eine 69 GB PLatte und bei 20 GB frei würde mir dann 7 GB zur Verfügung stehen um die Datenbank zu vergrössern..damit ich unter der 80%
grenze von NTFS bleibe. (in den letzten 6 Monaten ist die Datenbank um etwa 4 GB gewachsen) also sollte 7 GB für den "Jahresbedarf" ausreichen.

Wie sollte ich denn in Zukunft den Vergrösserungsfaktor in MB einstellen?
Ist dann die mdf-Datei ebenfalls per Hand zu vergrössern?

Das ist erst einmal nicht schlimm, wenn die DB auf automatische Vergrößerung steht. Das mit den 80% Füllgrad gilt für die Native- DB.

Also im Buch von Jörg Stryk steht aber man sollte 20-25% freien Platz haben bzw. größte Tabelle x 1,5

Mir stellt sich aber eine andere Frage: Was ist noch auf der Platte? 69 -39 = 30 GB, wenn noch 20 GB frei, dann fehlen 10 GB. So wie ich das sehe, bringt es mehr neue HDD's mit je einer Paltte DB und Log zu installieren.

Volker

natürlich ist da noch etwas anderes drauf..aber aus anderen gründen kann ich eben auch keine andere Festplatte einbauen.....

mir geht es um die Werte die ich parametrisieren kann IN der Datenbank um der Datenbank etwas mehr performance mit den jetzigen Gegebenheiten
zu geben.

Kurz vor knapp gefüllt?

Grundsätzlich sollte die Datenbank bis kurz vor knapp gefüllt sein.

Sorry, da fehlt ein "nicht". Soll heißen, ja nicht die Datenbank vollpumpen.

Hier meine Gedanken zu dem Thema:

Grundsätzlich ist die Standard NAV Architektur 1 x mdf ("PRIMARY", Meta- u. Objektdaten), 1 x ndf ("Data Filegroup 1", Daten und Indexe etc.) und 1 x ldf (Log) so OK.
Ob man diese Architektur ändern soll, also z.B. mehrere mdf haben soll muss im Enzelfall diskutiert werden; bringt aber i.d.R. heutzutage nicht mehr viel, da die meisten I/O Probleme schlichtweg mit der HArdware "erschlagen" werden können. Kurz: eine Aufteilung in mehrere mdf macht i.d.R. nur dann Sinn, wenn jede Datei auf ein eigenes physikalisches Laufwerk gelegt werden kann. Wenn man das so macht, dann sollte jede Datei gelich groß sein. SQL Server führt kein echtes "Loadbalancing" durch, sondern wird versuchen die Dateien in einem "Round Robin" Verfahren gleichmäßig zu füllen. Wie gesagt, sowas ist eigentlich nicht mehr nötig; besser ist z.B. die RAID Volumes "aufzubohren", d.h. mehr Platten in das "Striping" einzubinden. Und wenn man ein leistungsfähiges SAN verwendet, dann sieht die Sache eh ganz anders aus ...
Eine Aufsplittung des Logs (ldf) ist Schwachsinn, da die Datei rein sequenziell beschrieben wird - Parallelität ist hier nicht möglich.

Wenn es um die Dateigrößen geht, dann sollte das oberste Ziel sein den "Auto Growth" zu vermeiden. Immer dann, wenn eine Datei erweitert wird fragmentiert sie im Dateisystem. Ob und wie das der Fall ist hängt letztlich auch von der Hardware ab. Je fragmentierter eine Datei ist, desto "träger" wird sie sich verhalten.
Inbesondere beim Log sollte kein AG auftreten, da dies zusätzlich noch logisch fragmentiert; d.h. es werden sog. "V-Logs" erzeugt - Verwaltungseinheiten zur Transaktionssteuerung - je mehr man davon hat, desto aufwendiger wird die Verwaltung von Transaktionen was zu Perfortmance-Verlusten führen kann.
Man kann sich die VLogs anzeigen lassen:

Code:

USE DynamucsNAV
GO
EXEC DBCC LOGINFO

Anzahl der VLogs sollte im zweistelligen Bereich iegen; wenn nicht, dann muss man das Log schrumpfen (DBCC SKRINKFILE) und re-initialisieren.
Kurz: "Auto Growth" sollte besser nie stattfinden, besser ist, ein Mensch kümmert sich um das Wachstum; trotzdem empfehle ich den AG zu aktivieren, quasi für den Notfall: MDF: 100 MB, NDF: 1000 MB, LDF: 500 MB
(P.S.: bitte nicht auf "binäre" Werte setzen - 1024 etc. - das macht der SQL Server schon selber)

Insbesondere die MDF Datei benötigt freien Speicherplatz; dieser wird benötigt beim defragmentieren von Indexen. D.h. beim ALTER INDEX REBUILD werden die fragmentierten Seiten in den freien Bereich ausgelagert und dann sortiert zurück geschrieben - das funzt in etwa so wie das defragmentieren einer Festplatte.
Um einen VOLLSTÄNDIG fragmentiereten Index zu defragmentieren benötigt man ca. das 1,5 fache seiner Größe an freiem Platz. D.h. also, dass man idealerweise das 1,5 fache des größten Indexes an freien Speicher in der MDF Datei hat. Das kann man zwar ausrechnen (Script weiter unten), ist aber ein wenig mühsam; als "Best-Practice" kann man auch grob 20-25% freien Speicher ansetzen.
Der REBUILD könnte auch mit der Option "Sort in tempdb" laufen, dann wird kein freier Platz in der MDF benötigt. Aber nach meinen Erfahrungen erzeugt diese Option eher ein Problem; sie setzt voraus, dass die "tempdb" optimal konfiguriert ist und auf einem leistungsfähigen dediziertem RAID Volume liegt!

IMHO ist es also sinnvoll, die MDF-Datei(en) so groß anzulegen, wie es möglich, nötig und sinnvoll ist. Also groß genug, damit der SQL Server sie stetig füllen kann - ohne AG. Für das Log gilt letztlich das selebe, aber hier muss die Größe noch mit der Frequenz der Log-Backups abgestimmt werden. Auch da findet man eine "Faustregel" die besagt: Log Brutto-Größe = 20% der Data-Netto-Größe".
(Zu beachten ist, dass beim Restore eines Backups der SQL Server stets diese Dateigrößem allokieren wird; ggf. ist es daher auch notwendig die Dateigrößen im Hinblick auf "DB Kopien" abzustimmen, also z.B. auch "Log Shipping", "Mirroring" & Co.)

Wenn also die Dateien so konfiguriert sind, der SQL Server also nun in Ruhe schreiben kann, dann wäre es schick den Füllgrad der DB zu überwachen; ich mache das so (hier eine Light-Version aus der "NAV/SQL Performance Toolbox"):

Code:

-- Suche & Ersetze den Ausdruck <Database> mit dem Namen der zu überwachenden Datenbank

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'STRYK System Improvement' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'STRYK System Improvement'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SSI: File Usage (<Database>)',
      @enabled=1,
      @notify_level_eventlog=2,
      @notify_level_email=2,
      @notify_level_netsend=0,
      @notify_level_page=0,
      @delete_level=0,
      @description=N'Monitors DB file usage and raises error if free space is less than 20 percent',
      @category_name=N'STRYK System Improvement',
      @owner_login_name=N'sa',
      @notify_email_operator_name=N'SSI: PTB Admin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'db',
      @step_id=1,
      @cmdexec_success_code=0,
      @on_success_action=1,
      @on_success_step_id=0,
      @on_fail_action=2,
      @on_fail_step_id=0,
      @retry_attempts=0,
      @retry_interval=0,
      @os_run_priority=0, @subsystem=N'TSQL',
      @command=N'
SET STATISTICS IO OFF
SET NOCOUNT ON

DECLARE @size_kb int, @used_kb int, @free_perc decimal(18,2), @cmd nvarchar(250)

CREATE TABLE #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
INSERT #tmpspc EXEC (''dbcc showfilestats with no_infomsgs'')

SELECT
   @size_kb = SUM(s.size * CONVERT(float,8)),
   @used_kb = SUM(CAST(tspc.UsedExtents * convert(float,64) AS float))
   FROM sys.filegroups AS g
   INNER JOIN sys.master_files AS s ON (s.type = 0 AND s.database_id = db_id() AND (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
   INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
   WHERE (CAST(cast(g.name as varbinary(256)) AS sysname)=N''Data Filegroup 1'')

DROP TABLE #tmpspc

declare @max_idx bigint
-- select @max_idx = max(page_count) * 8 / 1024
-- from sys.dm_db_index_physical_stats (db_id(), 0, null, null, ''limited'')
-- select @max_idx as [max_IdxSize (MB)], @max_idx * 1.5 as [min_FreeDbSpace (MB)]

SET @free_perc = ((convert(decimal(18,2), @size_kb) - convert(decimal(18,2), @used_kb)) / convert(decimal(18,2), @size_kb)  * 100)

IF @free_perc < 20 BEGIN
  SET @cmd = ''Warning: Low free space in database "'' + db_name() + ''", File Size (MB): '' + convert(varchar(15), @size_kb / 1024) +
             '', Used Space (MB): '' + convert(varchar(15), @used_kb / 1024) +
             '', Free Space (MB): '' + convert(varchar(15), (@size_kb - @used_kb) / 1024) +
             '', Free Space (Percent): '' + convert(varchar(15), @free_perc) + char(13) + char(10) +
             ''Expand the file size to have at least 20 Percent free space.''
  RAISERROR(@cmd, 19, 1) WITH LOG
END ELSE
  SELECT ''Database "'' + db_name() + ''", File Size (MB): '' + convert(varchar(15), @size_kb / 1024) +
         '', Used Space (MB): '' + convert(varchar(15), @used_kb / 1024) +
         '', Free Space (MB): '' + convert(varchar(15), (@size_kb - @used_kb) / 1024) +
         '', Free Space (Percent): '' + convert(varchar(15), @free_perc)
',
      @database_name=N'<Database>',
      @flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily',
      @enabled=1,
      @freq_type=4,
      @freq_interval=1,
      @freq_subday_type=1,
      @freq_subday_interval=6,
      @freq_relative_interval=0,
      @freq_recurrence_factor=0,
      @active_start_date=20111014,
      @active_end_date=99991231,
      @active_start_time=20000,
      @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


Das Script legt einen Job an, der täglich den Füllgrad der "Data Filegroup 1" mißt und einen Fehler erzeugt, wenn der freie Speicher unter 20% fällt. Der "Fehler" soll lediglich eine Benachrichtigung an einen Operator auslösen, so dass nun dieser Mensch die Dateigrößen manuell konfigurieren kann.
(Der Job enthält auch 2 auskommentierte Zeilen, die den größten Index ermitteln)

Das Log kann via "Alert" überwachen:

Code:

-- Suche & Ersetze den Ausdruck <Database> mit dem Namen der zu überwachenden Datenbank

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'SSI: Log Usage (<Database>)',
      @message_id=0,
      @severity=0,
      @enabled=1,
      @delay_between_responses=900,
      @include_event_description_in=0,
      @category_name=N'STRYK System Improvement',
      @performance_condition=N'MSSQL$SSI_SQL2008_1:Databases|Percent Log Used|<Database>|>|80'
GO

Hoffe das hilft ein wenig weiter!