Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Robin Schumacher

Viewing Trace Information

Once these commands complete, the trace can be viewed through SQL Profiler by using the Open Trace File option in the tool and selecting the trace file created for this purpose.

Another option is to use the fn_trace_gettable function to view trace results. Once the trace file has been created, a SQL like the following can be issued to view the output, substituting the trace file name for the example one shown:

select
*
from
::fn_trace_gettable
('c:\dev\trace\tracetest.trc',default)


The nice thing about trace data is it is granular in that each execution of a SQL statement is seen as its own atomic unit. The bad thing about trace data is it is granular in that each execution of a SQL statement is seen as its own atomic unit.

Contradictory? Not really. There are some analysis situations in which the DBA needs to see each individual run of a SQL statement even if it is executed 1,000 times. There are other times, however, when the DBA wants an aggregation of a trace file so it can be seen, for example, that a particular SQL statement was run 1,000 times. This helps troubleshoot situations in which a SQL statement runs very fast, but runs much more often than it should and is therefore eating away at resources.

Unfortunately, the DBA cannot use the fn_trace_gettable function and perform aggregations on the TextData column, which contains the actual SQL text because the column type is a text datatype. However, a table from the function where the TextData column is altered to be a long varchar column such as 3000 or so can be created. The DBA can then find out execution counts with a simple query like the following:

select
TextData,
count(*)
from
tracetab
group by
TextData
order by
2 desc


The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter