Microsoft SQL Server IO en Virtual File Stats

Eén van de kenmerken van SQL Server, anders dan bijvoorbeeld bij Oracle, is dat we meerdere databases kunnen hosten in één SQL Server Instance. Bij performance tuning en/of troubleshooting is het daarom handig om te weten welke database(s) het meeste bijdrage aan de totale server workload. In deze blogpost beperk ik mij tot de resource die vaak het zwaarste belast wordt: storage. Hoeveel fysieke IO krijgen de verschillende databases, en dus de onderliggende files, te verwerken? En hoe kunnen we de datafiles, logfiles en tempdb dan het beste verspreiden over de beschikbare storage?

Dynamic Management Function

Sinds SQL Server 2005 is er een geweldige DMF (Dynamic Management Function) die informatie geeft over fysieke io naar de verschillende files: sys.dm_io_virtual_files_stats. Deze DMF geeft per file de hoeveelheid reads en writes, zowel in absoluut aantal als in bytes, sinds de laatste restart van de server. Ook het aantal milliseconde sinds die restart is beschikbaar. Verder zijn er kolommen voor de io stalls. Met andere worden: hoelang hebben we moeten wachten voordat de io daadwerkelijk werd uitgevoerd. De teruggegeven waarde is een totaaltelling, maar delen door het aantal milliseconde geeft ons de wachttijd per io! In het screenshot staat het script inclusief het resultaat op mijn laptop. In het voorbeeld zien we alleen reads. Voor writes zijn overeenkomstige kolommen beschikbaar.

Door aflopend te sorteren op wachttijd per read zien we meteen welke database het meeste tijd kwijt is aan simpelweg wachten op het io subsysteem. Vaak is performance problemen opzoeken en verhelpen gelijk aan het zoeken naar SQL Server waits en deze waits wegnemen. Als we hoge io stalls zien, kunnen we met performance counters en anders DMV’s verder uitzoeken waar de oorzaak precies zit en bijbehorende maatregelen treffen.

Best Practices File Placement SQL Server

SQL Server kent een aantal best practices met betrekking tot file placement. Zo wordt er aangeraden de transactielog op een dedicated schijf te zetten. Maar met 10 databases heb ik daarmee al 10 schijven nodig. Daarnaast wordt RAID 10 aangeraden vanwege de schrijf performance. Dus nu heb ik 10 RAID 10 arrays nodig. Verder is het een best practice om tempdb op een aparte schijf te zetten en het liefst voor elke processor in de server een aparte datafile in tempdb te maken (die met een grote workload wellicht allemaal op een eigen schijf moeten). Ook voor tempdb is RAID 10 de aangeraden configuratie. Tenslotte moeten nog de datafiles ergens opgeslagen worden.

Er zijn veel omgevingen waar we de luxe niet hebben om zoveel aparte RAID 10 arrays op te bouwen. En dus moeten we consessies doen en verschillende files toch op dezelfde array plaatsen. Gelukkig geeft de sys.dm_virtual_file_stats een goede indicatie van de throughput per file. De output is daarmee een goede leidraad voor de fileplacement. Als er bijvoorbeeld één RAID 10 en één RAID 5 array beschikbaar is, moeten we kiezen wat we op de RAID 10 zetten. Als alle databases een zelfde schrijf workload hebben en tempdb heel veel wordt gebruikt (het is niet vreemd als tempdb als één van de drukste databases naar voren komt wat betreft io), is dat een indicatie dat het systeem baat zal hebben bij tempdb op de RAID 10. Als één database een erg grote schrijf workload heeft en veel io stalls laat zien bij schrijven op de transaction log, kunnen we overwegen juist die transaction log op de RAID 10 te zetten.

Performance tuning is een complex geheel met veel nuances. De DMF sys.dm_virtual_file_stats vertelt slechts een klein stukje van het verhaal. Tegelijkertijd geeft het snel en eenvoudig een indicatie over de IO van de SQL Server instance. En dat is altijd waardevolle informatie.

Meer over Microsoft en SQL Server leer je natuurlijk bij Vijfhart.