1. Windows System Monitor – PART I
2. DBCC commands – PART II
3. Dynamic Memory Views - PART III
4. SQL Server Profiler – - PART IV

Windows System Monitor

Above graph would give you an overall picture of a server performance. Let’s drill down into a SQL counters to see how we can locate a performance issue.

Let’s add some counters to take readings by selecting “Add Counters”

I can now see a counter added into my Monitor.

I want to create a nice Excel bar,Pie graph for visual presentation so let’s create comma delimited file which I can use at Excel.

Right click and select START at -> FILE_IO, after defined interval use STOP to stop the Metric. Look at Directory where log is created and process it thru EXCEL or any other plotting tool to see the trends.