SQL Server Analysis Look At’s

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




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “SQL Server Analysis Look At’s”

  1. Andreas Wolter Says:

    Hi Martin,

    just to make sure: ETW is indeed included in Windows since Win 2000, but SQL Server Support only came in starting with SQL Server 2008 :)

    cheers

    Andreas

  2. usn Says:

    Thanks Andreas, already corrected.

Leave a Reply