Here are some tools that can be useful for troubleshooting.
SQL Server Profiler
Start the profiler using the menu option “Tools > SQL Server Profiler”.
On the “General” tab.
- Trace name : A name for the trace.
- Template : Pick any of the predefined templates. “Blank” allows you to select exactly what events you care about. There are a lot. “Standard” is a good staring point.
- Check “Save to file” and specify a file on the machine running the profiler (your PC).
- Check “Enable file rollover”.
- If you want the trace to stop automatically, check and set the “Enable trace stop time”.
On the “Events Selection” tab. Select the event you are interested in. The “Show all events” and “Show all columns” allow you to see all possible choices if unchecked. If checked, they only show the events and columns where you have made a selection.
When you are happy with your selection, click the “Run” button. To manually stop the trace, click the stop button (red square) on the toobar.
If you chose to save to file, you can open a previous trace file in the profiler and work through it. You can also amend the trace properties and it will amend the trace output on screen to filter out events you exclude.
For more information see:
Server-Side Trace (SST)
The SQL Profiler is a GUI wrapper over Server-Side Trace (SST), a collection of system stored procedures that expose the SQL Server trace information.
The easiest way to get to grips with this is to use the SQL Profiler as follows.
- Start a profiler run.
- Immediately stop it.
- Export export it as a “Script Trace Definition”. (File > Export > Script Trace Definition > For SQL Server 2005 – 20014).
The contents of the resulting file will be the SST commands to reproduce the SQL Profiler run you started. There are instructions in the script comments telling you how to modify this script to allow you to rerun specific traces.
You can see any traces that are present on the system using the following query.
SELECT * FROM sys.traces
For more information see:
Dynamic Management Views and Functions (DMVs and DMFs)
These are like Oracle’s dynamic performance (V$) views. They provide information about everything happening in the system. For example, there are DMVs and DMFs for session resource usage, index usage, OS performance and I/O performance, amongst other things.
For information see: