Analyzing Performance Monitor Data With SQL Server Profiler

A really cool feature in SQL Server Profiler is the ability to import performance monitor (perfmon.exe) logs.  Prior to this, DBAs used to have to jump through hoops working w/ tables, excel, and whatever else we had on hand to determine exactly what database activities are causing the resource spikes.

1) Create a Counter Log.   Add only relevant counters, and remember that there is a resource overhead to any type of monitoring activity.  Also remember to set the interval to a number that will generate a manageable, but useful amount of data.

2)  Start a Profiler trace with a time span that overlaps that of the Counter Log.  Again, trace only those events that are relevant, to keep overhead as light as possible (Management Studio –> Tools Menu –> SQL Server Profiler, then File –> New Trace).

3) After your  Counter Log and Profiler trace have accumulated enough data for your needs (a single query’s worth, or a month’s worth, depending on your goal), stop both and make sure they are saved.  Then go to Profiler, File–> Import Performance Data…

You can filter down both your Perfmon counters and your SQL Server columns, to pare down what you are analyzing.  Also, we can highlight a certain range on the graph to localize the trace to that time span.

Index Rebuild versus Reorganize: What’s the difference?

In a nutshell, you should rebuild when an index is over 30% fragmented and reorganize when an index is between 10% and 30% fragmented. If fragmentation is below 10%, you can probably just leave that index alone. Rebuilding takes more server resources (and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions), so reorg when possible. Syntax below to rebuild or reorg all indexes on a table named tTable follows…

REBUILD

ALTER INDEX ALL ON dbo.tTable REBUILD
GO

REORGANIZE

ALTER INDEX ALL ON dbo.tTable REORGANIZE
GO