sqlshack detecteren en voorkomen

In dit artikel bespreken we de best practices die moeten worden gevolgd om de TempDB database in een gezonde staat te houden en onverwachte groei van de database te voorkomen, naast de procedure die kan worden gevolgd om deze onverwachte groei eenmaal opgetreden te detecteren.

TempDB-overzicht

de TempDB-database is een van de belangrijkste SQL Server-systeemdatabases, die wordt gebruikt om tijdelijke gebruikersobjecten op te slaan, zoals de tijdelijke tabellen die door de gebruiker zijn gedefinieerd of die worden geretourneerd uit het uitvoeren van functies met een tabelwaarde, tijdelijke opgeslagen procedures, tabelvariabelen of indexen.

naast de gebruikersobjecten zal TempDB worden gebruikt om interne objecten op te slaan die door de Database-Engine van SQL Server zijn gemaakt tijdens de verschillende interne bewerkingen, zoals tussentijdse sortering, wachtrij, aggregatie of cursor.

de tempdb-systeemdatabase wordt ook gebruikt om de rijen-versies op te slaan om de functies te ondersteunen die het bijhouden van de wijzigingen vereisen die op de tabelrijen worden uitgevoerd, zoals het snapshot-isolatieniveau van de transacties, online Index rebuilds of de functie meerdere actieve resultaatsets.

TempDB database zal worden verwijderd en opnieuw worden aangemaakt elke keer dat de SQL Server service wordt herstart, te beginnen met een nieuwe schone kopie van de database. Op basis van dat feit, alle gebruiker en interne database bezwaar die zijn opgeslagen in deze database zal automatisch worden verwijderd wanneer de SQL Server service wordt herstart of wanneer de sessie waar deze objecten zijn gemaakt wordt verbroken. Daarom zijn de back-up en herstel operaties niet beschikbaar voor de TempDB.

waarom TempDB

de tempdb systeemdatabase speelt een belangrijke rol in het SQL Server performance tuning proces. Omdat het wordt gebruikt als caching opslag om verschillende soorten gebruikersdatabase objecten op te slaan en om het systeem interne objecten op te slaan om de SQL Server Database Engine gerelateerde processen te versnellen.

vanwege deze vitale rol die de tempdb-systeemdatabase speelt bij het verbeteren van de algehele prestaties van SQL Server, is het erg belangrijk om de initiële grootte, automatische groei en locatie best practices toe te passen op de tempdb-databasegegevens en logbestanden. Het toepassen van deze best practices op een manier die past bij uw SQL Server instance workload zal voorkomen dat de uitbreiding van de TempDB database gegevens en logbestanden zeer vaak, rekening houdend met het feit dat het bestand breidt proces is een duur proces waarin SQL Server Engine extra ruimte zal vragen van het besturingssysteem, die nulstelling zal uitvoeren op die ruimte voordat het toewijzen aan SQL Server Engine. U kunt zich de tijd en middelen voorstellen die nodig zijn voor dit toewijzingsproces.

Als u al Alle best practices toepast en er zich een onverwachte groeibewerking heeft voorgedaan, kunt u het toewijzingsproces versnellen met behulp van de optie directe bestandsinitialisatie van de database, waarbij het besturingssysteem de SQL Server-service vertrouwt en de vereiste ruimte toewijst zonder de tijd en middelen te verspillen aan de toegewezen ruimte.

Best practices

er zijn een aantal TempDB-systeemdatabaseconfiguratie-best practices die worden aanbevolen om de beste prestaties uit die gedeelde bron te halen en uw SQL Server-instantie te beschermen tegen onverwachte groei in de tempdb-databasegegevens of logbestanden die de onderliggende schijfruimte kunnen vullen.

deze configuraties omvatten:

• het aantal TempDB gegevensbestanden moet overeenkomen met het aantal logische processors, tot acht bestanden, op de machine waar de SQL Server instance is geïnstalleerd. Als het aantal logische processors op die machine groter is dan acht, stel het aantal databestanden in op acht, met de mogelijkheid om het uit te breiden met een veelvoud van 4 in het geval u een tempdb contention issue
• zorg ervoor dat de TempDB-bestanden dezelfde grootte hebben binnen dezelfde bestandsgroep om de hoogst mogelijke mate van parallelle operaties efficiëntie te bieden, vanwege het proportionele vul schrijfmechanisme dat wordt gebruikt om de gegevens in de databestanden te schrijven. Voor meer informatie, controleer begrijpen van de SQL Server Proportional fill algoritme
• Configureer de tempdb database data en log bestanden met een redelijke initiële grootte en auto-growth hoeveelheden die past bij de verwachte hoeveelheid gegevens die zullen worden geschreven op deze bestanden, en de verschillende soorten workloads uitgevoerd in uw SQL Server instance, met inbegrip van gebruikersactiviteiten en index onderhoud operaties, om de frequente kleine increment bedragen die de Algemene instantie prestaties beïnvloeden te voorkomen
• Zoek de TempDB bestanden op een snelle I/O subsysteem om elke I / O bottlenecks
• Lokaliseer de TempDB op een aparte schijf, weg van de gebruikersdatabases en andere systeemdatabases. Op deze manier zullen de zware lees-en schrijfbewerkingen die op TempDB worden uitgevoerd, geen invloed hebben op of worden beïnvloed door de werklast van de gebruikersdatabases, waardoor de algehele SQL Server-instance-prestaties

worden verbeterd voor meer informatie over de nieuwe verbeteringen in de tempdb-prestaties, raadpleeg het tempdb-databasedocument.

waar

moet worden toegepast vóór de versie van SQL Server 2016, kan de tempdb-groottetoewijzing worden uitgevoerd na het installeren van de SQL Server-instantie, vanaf de pagina Database-Eigenschappen. Hier kunt u het aantal databasegegevensbestanden beheren dat past bij het huidige SQL Server-exemplaar, de initiële grootte voor elk databasegegevensbestand en logboekbestand, de juiste waarde voor de optie Autogrowth en de locatie van de databasegegevens en logboekbestanden.

om deze wijzigingen uit te voeren, vouwt u het Databases knooppunt uit in SQL Server Management Studio, vouwt u het systeem Databases knooppunt uit en klikt u met de rechtermuisknop op de tempdb database en kiest u de eigenschappen optie zoals hieronder:

Blader door de Bestandenpagina en controleer of wijzig de verschillende database data en log bestanden instellingen hieronder:

vanaf versie SQL Server 2016 kunnen al deze wijzigingen, met de beste praktijken die passen bij de huidige serverbronnen die al door de installatiewizard worden voorgesteld, worden gecontroleerd en uitgevoerd tijdens het installatieproces van SQL Server, met behulp van de installatiewizard SQL Server, onder Database Engine Configurations / TempDB, zoals hieronder getoond:

Track tempdb growth

het bijhouden van de groei van de temdb-databasebestanden en de query ‘ s die de tempdb-bronnen verbruiken, kan worden uitgevoerd met behulp van de Prestatiemeter-tellers die zijn gekoppeld aan de TempDB-bestanden en de SQL Profiler tool die luistert naar een specifieke database workload type.

een meer database administrator ‘ s vriendelijke manier om de groei van de tempdb database bestanden te volgen is het bevragen van de volgende dynamische Beheer weergaven:

• sys.dm_db_file_space_usage dat geeft de ruimte het gebruik van de informatie voor elk bestand in de database, zonder aan te geven welke sessie of taak verbruikt die ruimte
• sys.dm_db_session_space_usage dat geeft het nummer van de toegewezen en opgeheven pagina ‘ s per sessie
• sys.dm_db_task_space_usage dat geeft het nummer van de toegewezen en opgeheven pagina ‘ s per elke taak

Merk op dat het resultaat van de sys.dm_db_session_space_usage DMV samengevat het resultaat van de sys.dm_db_task_space_usage DMV wanneer alle taken met succes zijn voltooid, vanwege het feit dat meerdere taken met betrekking tot dezelfde query zullen worden uitgevoerd onder dezelfde sessie. Een ander voordeel van deze DMV ‘ s is dat het het type object zal tonen dat de tempdb hulpbronnen verbruikt en of dit object een gebruikersobject is, zoals een tijdelijke of variabele tabel of een intern object, zoals index onderhoud of sorteer operatie. Dus, op basis van het aantal pagina ‘ s verbruikt door elk object, kunt u opgeven of TempDB wordt verbruikt door een interne of gebruiker object en optimaliseren van die consument.

sys.dm_db_file_space_usage

de SYS.dm_db_file_space_usage DMV kan gebruikt worden om de schijfruimte te controleren die gebruikt wordt in de tempdb databasebestanden. De onderstaande query ‘ s tonen de niet-toegewezen ruimte, de ruimte die wordt gebruikt om rijen-versies op te slaan, de ruimte die wordt verbruikt door de interne objecten en ten slotte de ruimte die wordt verbruikt door de gebruikersobjecten, als volgt:

Van het resultaat, die is teruggekeerd van mijn lokale exemplaar van SQL Server, kunt u zien dat er 60 MB ongebruikt in de TempDB van bestanden, met slechts 0,5 MB gebruikt voor interne objecten en 1,5 MB gebruikt voor de gebruiker objecten, zoals hieronder weergegeven:

sys.dm_db_task_space_usage en sys.dm_db_session_space_usage

sys.dm_db_task_space_usage en sys.dm_db_session_space DMV ’s worden gebruikt om het aantal pagina’ s te controleren dat is toegewezen en toegewezen door elke taak of sessie in de TempDB-database. Op deze manier, zult u in staat zijn om te zien welke gebruiker of een intern object dat de TempDB ruimte verbruikt.

bijvoorbeeld, het onderstaande resultaat wordt geretourneerd na het opvragen van het systeem.dm_db_task_space_usage DMV onder TempDB database, waar het zal het aantal pagina ‘ s toegewezen en deallocated voor elke gebruiker of interne taak als volgt retourneren:

maar de DBA zal niet zoveel details nodig hebben om te beginnen met het volgen van de query die de tempdb bronnen verbruikt. Dus, we moeten het vorige resultaat voor elke sessie samen te vatten, met behulp van de query hieronder:

En de pagina ‘ s samengevat verbruik resultaat per elke sessie zal worden zoals hieronder:

hetzelfde resultaat kan worden geretourneerd door de query ‘ s in de sys.dm_db_session_space_usage DMW, die de pages allocation en deallocation voor alle taken die onder dezelfde sessie, zoals hieronder getoond:

laat ons enkele acties uitvoeren op de TempDB-database met zowel interne als gebruikersobjecten zoals hieronder:

voer vervolgens de onderstaande geconsolideerde query uit, die het aantal toegewezen en gedealloceerde pagina ‘ s retourneert door interne en gebruikersobjecten, en beide voltooide bewerkingen toont (met behulp van sys.dm_db_session_space_usage DMV) en de onvoltooide operaties (met behulp van sys.dm_db_task_space_usage DMV):

En de vorige activiteiten resultaat rekening wordt gehouden met de query-uitvoer, met het aantal pagina ‘ s toegewezen en opgeheven door het creëren van de user tijdelijke tabel en het sorteren van de gegevens tijdens de index rebuild-proces, zoals hieronder weergegeven:

om de tekst te krijgen van de query die de tempdb-bronnen verbruikt, kunt u uw SQL-ontwikkelingsvaardigheden gebruiken door het resultaat van de YS.dm_db_task_space_usage DMV te verbinden met andere DMV’ s zoals sys.dm_exec_requests en sys.dm_exec_sessions met de sys.dm_exec_sql_text dynamische beheerfunctie, waar het de query ‘ s zal weergeven die de tempdb ruimte nu consumeren, zoals in deze query.

het resultaat zal vergelijkbaar zijn met het onderstaande:

• auteur
• recente berichten

Ahmad Yaseen is een Microsoft Big Data engineer met diepgaande kennis en ervaring in SQL BI, SQL Server Database administratie en ontwikkeling velden.
hij is een Microsoft Certified Solution Expert in Data Management en Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate en Microsoft Certified Trainer.
ook draagt hij bij met zijn sql tips in veel blogs.
Bekijk alle berichten van Ahmad Yaseen

Laatste bericht door Ahmad Yaseen (zie alle)

• Azure Data Factory Interview Vragen en Antwoorden – februari 11, 2021
• Hoe monitor Azure Data Factory – 15 januari 2021
• met Behulp van Source Control in Azure Data Factory – januari 12, 2021