Chapter 15 Monitoring and troubleshooting SQL Server Performance

Chapter 15 Monitoring and troubleshooting SQL Server Performance
SQL Server Profiler
1. You should specify SP: Stmtcompleted or SP:Stmtstarting event only after you
have narrowed the focus of your trace. These events capture every statement executed within a stored procedure. On high volume systems, capturing every statement can quickly generate extremely large trace logs.

2. Saving Trace Definition
The process of setting up, launching and closing traces is automated by using SQL Server Agent jobs. However, writing a trace is not easy. shortcut is to use profiler for this. After you create a new trace inside profiler that contains the events, data columns, and filters that you want, click on run and then immediately stop the trace .Under the file menu, go to the option export, script trace definition .you can use this option to generate a transact SQL batch to create a trace for either SQL 2005 or 2000. You then use this batch as basis for stored procedure that SQl server agent calls to manage the trace.

3. Saving Trace Data
If during the trace definition you specified to save to a file or table, the trace data is already saved for you .however you can explicitly write the contents of the grid inside profiler to either a file or table by accessing the file, save as, trace table options or Trace file.

4. Profiler and Performance Monitor Co-relation in SQL Server 2005
Start a trace in profiler and simultaneously start performance monitor log counter with a counter like % processor time.
Remember trace must contain start time column to be able to co-relate to perfmon
You cannot open up the co-relation with perfmon till you save a profiler trace and then load it back up.
Save trace file and stop perfmon log also
Once the profiler trace is saved, in Profiler, from the File menu, select Import Performance Data. Select the location where you stored your Performance Monitor log. Then, select from the File menu Open and then Trace. Select the location where you stored your Profiler trace. Now, you can use Performance Monitor correlation (from File/Import Performance Data)