xSQL Profiler Take 2

Today I decided to use xSQL Profiler to help me diagnose some memory pressure/paged out problems we are facing, and boy, that was probably an error (partially my fault to trace too much perhaps)

Problems:

• I was tracing a remote server A (trace files are located on A's E drive) starting 12:30 pm
• xSQL Profiler repository is located on my computer J, that go fetch trace files from server A
• I think my computer cannot catch up to the trace files creation, and in ten minutes I have 6GB of trace files on server A and running low on space.
• I killed the xSQL Profiler hoping it would stop (at this time about 80,000 rows collected)

And....

• It did not stop!! and drove my local SQL Server memory to 1GB and slowed my laptop to a crawl
• I could not start the "xSQL Profiler" as it says it cannot connect to the database despite being there
• I could not start the "xSQL Profiler Service" as it creates 10+ traces insantly on server A again
• I tried deleting entries in the local "xSQLProfiler" database tables (such as dbo.Traces, dbo.APPLICATION_SETTINGS, dbo.TRACE_SCHEDULES....) => as soon as I started xSQL Profiler, it created the 10+ traces AGAIN.
  

Fix:

• Created the SQL script below to KILL ALL traces except the default one (I ran about 10 times this afternoon)
• Renamed the trace file directory on server A's E drive [this made it stop, finally, near 3:30 pm]
• I still cannot open up the xSQL Profiler application (cannot connect to database)
• EXTREME CAUTION for NEXT TIME
  

This is the code I ended up with, and added to my toolbox
It kills ALL Profilers trace but the default SQL trace


DECLARE @traceid INT
DECLARE curDBs CURSOR FAST_FORWARD FOR -- fast forward = read only, forward only
   --Run the following Transact-SQL statement to retrieve the list of the running trace:
   SELECT DISTINCT traceid
   --,*
   FROM ::fn_trace_getinfo(NULL)   
   WHERE traceid <> 1 -- do not kill the default SQL trace

OPEN curDBs
FETCH NEXT FROM curDBs INTO @traceid
WHILE @@FETCH_STATUS = 0
BEGIN

   --Run the following Transact-SQL statement to stop the server-side trace
   EXEC sp_trace_setstatus @traceid = @traceid , @status = 0
  
   --Run the following Transact-SQL statement to close the trace and to delete the trace information
   EXEC sp_trace_setstatus @traceid = @traceid , @status = 2

   FETCH NEXT FROM curDBs INTO @traceid
END

CLOSE curDBs
DEALLOCATE curDBs

SELECT * FROM ::fn_trace_getinfo(NULL)

RETURN


REVIEW: xSQL Profiler

I came across the xSQL software website and realized they have a few Free SQL Server products, so being the curious monkey I am, I downloaded them and installed. Here's my first usage of xSQL Profiler and a short review

What I liked:

• Easy to use and setup a quick trace
• Filter (in both setting up or querying) and interfaces are better than SQL Profiler trace
• Easy to define Category/Event Definition
• Lets you pick a repository database
• Custom Query Template is useful to find Top N queries by CPU time/Duration/Execution etc...
• Able to schedule the trace (one-time/daily/weekly/month)
• e.g. I can setup a Daily trace from 10am to 11am to get a snapshot of busy times

What could improve:

• Let me think on it... maybe more licenses for the Free version (or for me?)
• Uses up a lot of memory when Viewing Trace Data (e.g. to view 81000 rows, my local SQL Server memory incrased by ~200MB, and so did the xSQL.Profiler.UI.exe and took about 2~3 minutes to display

So far so good, it shall remain my first choice for Profiling for now, unless I am in Servers where it's not installed. Keep up the good work, xSQL! This is something Red Gate didn't have =P