Troubleshooting Bottlenecks Using SQL Server Profiler : PART IV

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

SQL Server Profiler – – PART IV

Once connected select location for TRACE file and name your trace.

Clcik on TAB “EVENT selection” to enable TRACE for a specific Event. There are tonnes of them.

Let’s also select some locks to get stats on

Click on any column like Reads/Writes/Duration to enter criteria which would filter results

Let’s collect all SQL showplans in an XML file.

Hit RUN and the trace window would look like one below :

Let’s start a management studio and fire an SQL to see if profiler traps details for our SQL

Now I can see at trace my statements

Let’s stop trace and look at SQL execution plans. Open file “C:\PerfLogs\XML-Showplan.SQLPlan” and you can see the statemnts and all execution plans in this fille. One can use a 3rd party tools to dig details.