End of March, I had a chance to dig a bit deeper into MS SQL Server analysis. The german chapter of PASS (http://www.sqlpass.org) organized one of their “Essentials”, a condensed training day with a specific topic. This one was named “SQL Server Analysis” and was held by Andreas Wolter (MCSM) in Nuremberg. Here comes my “look-at’s” – the usual public scratchpad of what I want to review as follow-up of an event.
- There are two SQL Server related IO benchmark tools (like ORION that I’m familiar with, by Oracle): SQLIOSIM and SQLIO.
- Event Tracing for Windos (ETW) is supported for MSSQL since Version 2008, use Windows Performance Recorder for that.
- Try “perfmon /report”
- Windows Server 2008R2 and above supports “mountpoints” in addition to drive letters. Nice, finally aligning POSIX?
- Profiler can load PERFMON data and correlate them (old style / deprecated)
- SQL Server reports: Disk Usage Report shows, for example, resizing operations. So check your file sizing policy this way.
- Extended Events (XEvents) take 2µs, Profiler events take 4ms (=> Profiler is factor 2000 slower; source: SQL Server & BI blog)
- SQL Server index growth is different from Oracle: They have no 90/10 split on the growth end of the leaf list, SQL Server adds empty leaf nodes
- Extended Events editor to be found under “Administration” in SQL Server Managament Studio (SSMS)
- Setting up a Performance Data Warehouse brings historical performance data. Do the following:
1. Create a Data Collection Set
2. Create a Performance DWH
3. Create a data Collector - Look at tools:
1. Event Notifications
2. SQL Diag (Log/Trace collector)
3. RML Utilities
So for sure this is not everything – the more you dig into a RDBMS, the more surprises and fields of knowledge-to-learn you will find. Let’s go ahead, next time.
Good luck
Martin Klier
Edit: Version supporting ETW