Traceflag activatie / deactivatie in SQL Server

Wat is een traceflag?

Een optie om het gedrag van MSSQL Server aan te passen is het gebruik van traceflags. Je kunt een traceflag vergelijken met de oude DIP switches die je soms nog weleens tegen komt in apparatuur. Op dit moment zijn er al meer dan 100 traceflags en dit aantal neemt alleen maar toe.

Er zijn traceflags welke zijn beschreven (“documented”) en dus ondersteund door Microsoft, maar er zijn ook “undocumented” traceflags bekend. Het gebruik van undocumented traceflags neemt een risico met zich mee, want dit kan leiden tot een situatie waardoor er geen support meer wordt geleverd. Andersom mogen undocumented traceflags alleen worden geactiveerd op advies van Microsoft. Bestudeer altijd eerst het doel en de effecten van een traceflag alvorens deze in een testomgeving uit te proberen. DBA.nl kan ondersteuning bieden bij vragen over de toepassing traceflags.

Hoe te activeren?

Als hint in een query

Met de T-SQL hint QUERYTRACEON kan een traceflag alleen voor een specifieke query geactiveerd worden (in dit voorbeeld traceflag 4199):

SELECT … FROM … OPTION (QUERYTRACEON 4199)

Tijdelijk – voor een sessie

Je kunt een traceflag voor een specifieke query gebruiken, door het DBC TRACEON() commando. Een voorbeeld om traceflag 4199 te testen:

DBCC TRACEON(4199)
 SELECT …

Als de sessie wordt beëindigd, is de traceflag weer uitgezet. Je kunt meerdere traceflags opgeven, gescheiden door komma’s.

Semi-permanent

Het zelfde commando kan gebruikt worden om een traceflag globaal aan te zetten. De flag geldt dan voor alle sessies. Er moet dan alleen -1 worden toegevoegd aan het laatste tracenummer:

DBCC TRACEON(4199, -1)

De opgegeven traceflag blijft hiermee actief totdat de instance wordt herstart. Na de herstart is de traceflag verdwenen.

Permanent

Hiermee wordt bedoeld dat de traceflag als startparameter wordt toegevoegd aan de MSSQL service. De werkwijze verschilt per gebruikte MSSQL versie, vanaf MSSQL 2012 is dit een stuk eenvoudiger geworden.

Opstartparameters wijzigen voor MSSQL 2005 / 2008 (R2)

• Open SQL Server Configuration Manager
• Klok op “SQL Server Services” En klik met de rechtermuisknop “SQL Server (InstanceName)” om naar “properties” te gaan
• Ga naar de “Advanced” tab bij properties
• Klik op de waarde achter “Startup parameters” om een dropdown menu te krijgen. Voeg de trageflag toe met de -T (hoofdletter!) parameter. Zet voor de traceflag een puntcomma. Vervang xxxx door het gewenste traceflag nummer:

Opstartparameters wijzigen vanaf MSSQL 2012

De werkwijze is in grote lijnen gelijk aan eerdere versies, echter er is een apart tabblad gemaakt voor startup parameters. Voeg hier de parameter toe, zonder een puntkomma.

 

Hoe weet ik welke traceflags actief zijn?

Je kunt met het commando DBCC TRACESTATUS() opvragen welke traceflags er actief zijn en of deze voor een sessie actief zijn of globaal. Geef je -1 op, dan zie je de globale traceflags, anders zie je alleen je sessie traceflags. Je kunt ook een tracenummer opgeven om te controleren of deze actief is:

DBCC TRACESTATUS(-1)

Werkwijze aanzetten traceflag

Omdat traceflags globaal aangezet kunnen worden, is dit een handige methode (in onderstaand voorbeeld activeren we traceflag 4199)

• Activeer de traceflag globaal met het T-SQL commando DBCC TRACEON(4199, -1)
• De traceflag is nu actief
• Voeg de traceflag ook nog toe als opstart parameter

Hoe zet ik een traceflag uit?

Met het commando DBCC TRACEOFF() kan een traceflag voor een sessie of globaal uitgezet worden. Voeg -1 toe om de traceflag globaal uit te zetten:

DBCC TRACEOFF(4199, -1)

Om een traceflag te verwijderen als opstart parameter, zie de beschrijving hoe een traceflag toe te voegen. Bij MSSQL 2012 en hoger kun je op de –T optie gaan staan en dan op de knop [remove] klikken. Let erop dat andere startup parameters niet per ongeluk ook verwijderd worden.