none
Datenbank arbeitet nach Wartungstask langsam RRS feed

  • Frage

  • Hallo,

    ich habe hier ein seltsames Problem, bei dem ich nicht weiterkomme. Wir haben hier eine Drittanbieter-Datenbankanwendung, die bisher immer reibungslos lief. Seit neuestem dauern diverse Abfragen 3 Mal so lange, wie vorher. Wir hatten erst die AD-Benutzergruppe in Verdacht, weil die Person, die direkt und nicht über eine Benutzergruppe an der Datenbank berechtigt war, performant arbeiten konnte. Eine Nachfrage beim Entwickler der Anwendung ergab, dass wir keine AD-benutzer, sondern einen SQL-Benutzer für die Authentifizierung an der DB nehmen sollen. Also haben wir die Authentifizierung umgestellt und es lief wieder performant - bis zum darauffolgenden Wochenende. Danach war es wieder langsam. Erneut beim Entwicklerangerufen, ein paar SQL-Server einstellungen angepasst und es lief wieder - bis zum Wochenende. Danach war es plötzlich wieder langsam. Ich habe nun auf gut Glück einmal die Einstellungen, die wir am SQL-Server geändert hatten wieder zurückgestellt und sofort lief es wieder performant.Davor haben wir es mit einem Serverneustart versucht, was allerdings nicht half. 

    Ich kann mir auf die ganze Sache keinen Reim machen, weil die anderen DB's auf dem Server normal laufen. kann sich das jemand erklären?

    PS: Am Wochenende läuft immer ein SQL-Server-Wartungstask, der die Indizes neu erstellt.


    • Bearbeitet Jan Merker Dienstag, 25. September 2018 14:20
    Dienstag, 25. September 2018 14:19

Alle Antworten

  • "PS: Am Wochenende läuft immer ein SQL-Server-Wartungstask, der die Indizes neu erstellt."

    Dies kann bedeuten, dass eben nicht alle Indizes erstellt werden sondern nur die, der Anwendung bekannten.
    Ggf. werden aber bei komplexeren Abfragen weitere Indizes benötigt, die durch irgendwelche Überwachungsprozeduren durchaus erst später erstellt werden.

    Warum werden die Indizes überhaupt neu erstellt. Das Aufräumen von Logs (Transaktionen, TempDB) sollte i.d.R. reichen.

    Dienstag, 25. September 2018 15:35
  • ...

    Warum werden die Indizes überhaupt neu erstellt. Das Aufräumen von Logs (Transaktionen, TempDB) sollte i.d.R. reichen.

    Frage: Was genau ist mit dem "Aufräumen" von Tranaktionen und Tempdb gemeint?
    Ich kann mir darauf keinen Reim machen.

    Selbstredend macht eine regelmäßige Index-Wartung/Defragmentierung Sinn. Und einmal pro Woche ist da ein guter Zeitraum, der sicher nicht zu oft ist.

    ------

    Zum Problem selber kann man aus dem Erläuterten nur ableiten, dass es sich sehr wahscheinlich wirklich um Plan-Änderungen handelt. Parameter-Sniffing wäre das zugrundeliegende Problem. Da ist es etwas vom Zufall abhängig, welcher Plan als erstes nach einem Neustart gebildet und dann gecached wird.

    Ich empfehle, sich an der Stelle einmal die Zeit zu nehmen, einige Beispiel-Abfragen samt Plänen über die Zeit zu sammeln, um für die Pläne Vorher-/Nachher-Vergleiche machen zu können. Dabei kann man die Query Store von SQL Server (ab 2016) oder eigene Scripte gegen den Plan Cache oder Drittherstellertools um Zeit zu sparen verwenden.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Dienstag, 25. September 2018 19:21
  • Guten Abend,

    meiner Meinung nach sind noch ganz viele Fragen offen.

    Wurden Updates installiert? Auf dem Server oder bei der Software vom Drittanbieter?

    Wo ist das "langsamer" denn gemessen worden? Direkt im Management-Studio auf dem Datenbank-Server, oder mit der Software vom Drittanbieter, die die Abfragen ausführt? Soweit Software - sind es ggfs. Terminalserververbindungen?

    Wie ist das Verhalten, wenn der im Verdacht stehende Wartungsdienst, der die Indexe neu erstellt, temporär ausgesetzt wird?

    Wie ist das Verhalten, wenn man die Datenbank in eine Testumgebung auf einem anderen Datenbankserver wiederherstellt, und die Abfragen hier ausführt?

    Übrigens gibt es hier für die Wartung von Indexen eine sehr gute Lösung:

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Schönen Abend.

    Dienstag, 25. September 2018 20:10
  • Wir wollen ja nicht hoffen, dass hier ein Shrink von Transactionlog und TempDB gemeint war, denn das ist absolut kontraproduktiv.

    Interessant wäre es auch zu erfahren, welche Servereinstellungen geändert und rückgängig gemacht wurden. Falls diese auch auf eine Bereinigung des Cache hinauslaufen, würde das die Annahme von Andreas noch weiter unterstützen.

    Du könntest auch einmal versuchen mit dieser "undokumentierten" Aktion den Cache nur für die Datenbank zu bereinigen, wenn es mal wieder klemmt. Das ist natürlich auch nur zur Fehlersuche gedacht und keine Lösung:

    Use <MeineDatenbank>
    go
    Declare @db_ID int = DB_ID();
    DBCC FLUSHPROCINDB(@db_ID);


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Mittwoch, 26. September 2018 06:10
    Beantworter
  • Nachdem die Produktiv-DB langsam war, haben wir die selbe Aktion auf der Test-DB durchgeführt, die jeden Tag über eine Kopie der Echt-DB neu erstellt wird und bei der die DB-Files auf dem selben SQL-Server und den selben Laufwerken liegen, wie die der Echt-DB. Auf der Test-DB liefen die gleichen Aktionen schnell.

    Wir haben die Geschwindigkeit per Stopuhr mit der Anwendung getestet, da der getestete Vorgang in der Anwendung unter normalen Umständen eh schon 2-3 Sekunden dauert.

    Der Wartungstask, der am Wochenende läuft, besteht aus folgenden Schritten:

    1. Sicherung der DB

    2. Recoverymodell auf Einfach setzen

    3. Index neu organisieren

    4. Statistiken aktualisieren

    5. Log-Shrink der DB (wobei unsere Logs auf einem Compellent SAN liegen, welches wiederum dieses Volume auf SSD-Platten schreibt)

    6. Recoverymodell wieder auf Full setzen

    7. Erneute Datenbanksicherung, damit am nächsten Tag wieder Logsicherungen gemacht werden können.

    Bis auf die Recoverymodelländerungen sind handelt es sich ausschließlich um die mitgelieferten Wartungsplantasks des SQL-Servers.

    Am SQL-Server haben wir lediglich die Werte von Kostenschwellwert für Parallelität von 20 auf 5 (und später wieder zurück) und Maximale Anzahl von gleichzeitigen Verbindungen von 0 auf 2000 (und später wieder zurück) geändert. Ganz egal was wir davon geändert haben, sobald es geändert wurde, war die Performance wieder wie vorher und das verstehe ich nicht. 

     

    Mittwoch, 26. September 2018 07:10
  • Hallo,

    hier mal ein paar Vorschläge was ich anders machen würde.

    LOG Shrink ersatzlos streichen

    Als Index Wartung das Skript von Ola Hallengren nutzen (geht schneller und erzeugt weniger Last)

    Recovery Model nicht auf Simple stellen sondern in Full oder maximal in Bulklog belassen. (Bulklog belastet das Transaktionslog nicht so stark)

    Am Ende keine Vollsicherung sondern eine Logsicherung um das Log zu leeren (Bei Bulklog nicht notwendig).

    die Parameter für Parallelität deutlich höher stellen als 5. Die 20 sind an Anfang ok.

    Kannst du mal die beiden TSQL Skripte ausführen um mal zu sehen wie der Status des Servers ist?

    USE master;
    GO
    
    -- Page Life Expectancy (PLE) value for each NUMA node in current instance!
    SELECT	@@SERVERNAME											AS	[Server Name],
    		[object_name],
    		[instance_name],
    		[cntr_value]											AS	[Page Life Expectancy],
    		GETDATE()												AS	[Measure_Date_Time],
    		(SELECT sqlserver_start_time FROM sys.dm_os_sys_info)	AS	Server_Start_Date_Time
    FROM	sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE	[object_name] LIKE N'%Buffer Node%' AND
    		[counter_name] = N'Page life expectancy' OPTION (RECOMPILE);
    Wartestatistik
    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],
    		'http://documentation.red-gate.com/display/SM4/' + W1.wait_type AS [DocumentLink]
    FROM	[Waits] AS [W1] INNER JOIN [Waits] AS [W2]
    		ON ([W2].[RowNum] <= [W1].[RowNum])
    GROUP BY
    		[W1].[RowNum],
    		w1.wait_type
    HAVING	SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 100;
    GO


    Benjamin Hoch
    MCSE: Data Platform & Data Management and Analytics
    MCSA: SQL Server 2012/2014 & 2016 DB Administration
    MCSA: Windows Server 2012


    Mittwoch, 26. September 2018 07:34
  • Ich habe die Abfragen ausgeführt, da ich aber kein Spezialist bin, sagen mir die Werte nichts. Daher würde ich sie gerne hier rein posten, weiß aber nicht, wie ich das halbwegs übersichtlich hinbekomme.

    Mittwoch, 26. September 2018 09:02
  • ...

    Am SQL-Server haben wir lediglich die Werte von Kostenschwellwert für Parallelität von 20 auf 5 (und später wieder zurück) und Maximale Anzahl von gleichzeitigen Verbindungen von 0 auf 2000 (und später wieder zurück) geändert. Ganz egal was wir davon geändert haben, sobald es geändert wurde, war die Performance wieder wie vorher und das verstehe ich nicht. 

     ...

    wie angedeutet, das sind Dinge, die den Plan Cache des SQL Servers leeren (wie ein Neustart natürlich auch)
    Daher würde ich mir mal die Pläne ansehen.

    Die Wait-Stats oder PLE auf Serverebene werden nicht helfen, für solche konkreten einzelnen Queries Aussagen treffen zu können.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Mittwoch, 26. September 2018 09:08
  • "LOG Shrink ersatzlos streichen" kann ich aus Erfahrung so nicht stehen lassen.
    Da hatte ich leider mal den Fall, dass eine vom Kunden nicht gewartete 300MB-DB ein 20GB-Log hatte. Dies führte bei einem Update von 100.000 Sätzen zu einer Dauer von knapp 15 Minuten!

    Nach dem entfernen des Logs dauerte die Transaktion dann nur noch wenige Sekunden.

    Und kann mir einer erklären, was ich mit einem 20GB-Log noch machen soll?

    Aber vielleicht macht dei DB das ja inzwischen auch automatisch.

    Donnerstag, 27. September 2018 08:45
  • Hallo bfuerchau,

    doch das kannst du. Das Transaktionslog wächst nur dann wenn es mehr Platz benötigt als aktuell im Log frei ist.

    Dies passiert in der Regel bei folgenden Situationen:

    • Sehr viele parallele Vorgänge
    • Log Sicherung fehlt
    • Replikation ist aktiviert aber gerade nicht möglich (AlwaysOn, Transationlog Versand, Spiegelung)
    • Lang laufende Transaktionen

    Wenn das Log auf Grund normaler Nutzung wachsen muss, dann sollte ich einen Teufel tun und es wieder verkleinern, dann bei nächster Gelegenheit wird es sowieso wieder wachsen. Wenn das Log wegen eines Problems gewachsen ist, dann muss ich das Problem lösen (Log Sicherung machen, Replikation wieder in Gang bringen, Langlaufende Transaktionen beenden und Ursache beseitigen) danach kann ich das Log wieder bewusst auf das normale Level verkleinern.

    Auch bei bestimmten Wartungsarbeiten (SAP Support Packages) wird bei uns das Log sehr groß. Nach der Wartung wird das Log wieder verkleinert. Aber als bewusste Aktion im Rahmen der Wartung.

    Bei deiner 300 MB Datenbank würde ich erstmal den Grund suchen warum das Log so wachsen musste. Danach kannst du es ja verkleinern. Wichtig ist vor allem eine gescheite Wachstumseinstellung für das LOG. Bei einem MB oder einem Prozent bekommt man (gerade in älteren SQL Server Versionen) massiv viele VLF in LOG welche sich extrem auf die Leistung ausüben.

    https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/

    Nicht das Verkleinern an sich ist das Problem aber in einem regelmäßigen Wartungstask macht es mehr kaputt als es den Aufwand wert ist.

    Und einen kleinen Hinweis noch: Wenn dein 200 GB Log File gerade zu 99% leer ist und nur als VLF am Ende gerade in Benutzung ist, dann passiert mal einfach Garnichts mit der Log Größe.


    Benjamin Hoch
    MCSE: Data Platform & Data Management and Analytics
    MCSA: SQL Server 2012/2014 & 2016 DB Administration
    MCSA: Windows Server 2012

    Donnerstag, 27. September 2018 10:30
  • Die 300MB-Datenbank ist eine Single-User, Single-App-Anwendung mit einem Access-Frontend gewesen und unterlag dem Kunden selber.
    Was immer die Ursache des 20GB-Logs war ließ sich halt nicht feststellen. Tatsache war aber auch, dass das Erweitern dieses Logs bei der Transaktion genau das verursachende Problem war.

    Wenn der SQL-Server das heute intelligenter macht, ist das ja auch ok so.

    Donnerstag, 27. September 2018 11:52