Cruisin' Baseline Road
If your shop is anything like mine SQL Server is the first to blame when something goes wrong. So what can you do?
BASELINE!
Kevin Kline (blog | twitter) says it best “How do you know what’s abnormal unless you know what’s normal?”
Now there are a lot of third party tools that can do this for you with minimal effort. However, I’m all about free and what’s better than rolling your own solution with a product that Microsoft itself uses internally?
This installation is also for running the SQL Nexus application on my workstation, collecting the data on the production server, and analyzing on a non-production SQL Server database. This will help minimize the load on the production SQL Server.
Let’s get started:
First you’ll need a copy of SQL Nexus (http://codeplex.com/sqlnexus)
You will also need a copy of ReadTrace (RML Utilities) for 32 (or 64) bit (http://sqlnexus.codeplex.com/wikipage?title=ReadTrace&referringTitle=Home)
On your workstation:
Unzip SQL Nexus and place the files in a directory of your choosing.
Install the ReadTrace msi.
You will also need the PerfStatsScript for your flavor of SQL Server found at the bottom of this page: http://sqlnexus.codeplex.com/Wiki/Info.aspx?title=Sql2005PerfStatsScript&action=Info
On the SQL Server you’ll be collecting metrics from:
Make a folder and Copy SQLDiagPerfStats_Trace.XML, StartSQLDiagTrace.cmd, SQL_200x_Perf_Stats.sql, and SQL_200x_Perf_Stats_Snapshot.sql there. (mine were 5’s instead of the x for SQL Server 2005)
Now open the SQLDiagPerfStats_Trace.XML file (this is the config) and edit the following pieces:
<Machine name=”YOURMACHINENAMEHERE (OR A DOT FOR ALL)”>
<Instance name=”YOURINSTANCEHERE (OR A STAR FOR ALL)” windowsauth=”true” ssver=”9″ user=””>
**Update** – If you are collecting data from a SQL 2008 R2 instance you will need to also modify the following:
<Instance name=”<instance name>” windowsauth=”true” ssver=”10.5” user=””>
Or if you want to collect data from more than one instance and release (like 2008 and 2008 R2) then you will need to add the “star” instead like this:
<Instance name=”<instance name>” windowsauth=”true” ssver=”*” user=””>
Please see this writeup if you want more information or if in the SQLDIAG.log you are getting the “SQLDIAG There are no instances of version 10 on this computer“.
You can also set other miscellaneous options as well but we’ll go for the default to start.
Now open up StartSQLDiagTrace.cmd in your favorite text editor. Insure all the paths are correct. (This also gives you instructions at the top in order to make this into a service you can stop and start).
To start collecting data double click the StartSQLDiagTrace.cmd or start the service. This should create a folder called “SQLDiagOutput” in the same folder you’re already in. This is the data SQL Nexus will need.
Once you think you’ve collected enough hit Ctrl+C in the command window (or stop the service) and copy the entire folder down to your workstation.
Open up SQL Nexus. The first thing it will prompt you for is the database server you want the data to be placed on. I chose a non-production server to do this with.
Then click on import. (I also go to options and “Drop Current DB Before Importing” since I already had data on the non-production server). This will auto-magically create a new database and populate with your data for analysis.
And there you go. You should now be able to click around and view bottlenecks, waits, etc.
Lastly I’d like to thank one of the SQL Nexus contributors Amit Banerjee (blog | twitter) for helping me spot a problem with my initial install. Thanks Amit!
Enjoy your metrics!
