SQL Server Profiler – Disable auto scroll by default

profiler

Is there a way for new traces to start with auto scrolling disabled?

This is the very first thing I do every time I start a trace and I'd like to optimize my workflow, especially since I run multiple traces on numerous servers, and each one starts with this option enabled (even within the same instance of Profiler).

I would be open to an AutoHotKey workaround. For example, I use a script that detects when a window with ID 32770 is opened to auto-type "y" (to change a file extension in Windows Explorer _{it falsely matches the Outlook Rule name popup window though}). Also note that it can take exorbitantly long for Profiler to open, because my machine is underpowered.

P.S. To add insult to injury, if you're too fast going to the menu to disable auto scroll on your new trace, it doesn't let you…

Best Answer

Here you go my good man,

Create the following MyProfiler.vbs file in insert the following code:

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "PROFILER.EXE /S <your SQL Server Name> /T Standard"
WshShell.AppActivate "PROFILER.EXE"
WScript.Sleep(5000) 
WshShell.sendkeys "%(WO)", True

Now, run MyProfiler.vbs.

Note:

It takes a few seconds to load Profiler.exe so sleep is set to 5 seconds. Otherwise, the auto-scroll commands will be input into the command window where MyProfiler is running. Cheers...

p.s.

You can run whichever template you prefer. I'm running the SQL Server Standard Template with /T.

Related Solutions

Sql-server – Sql profiler replay table

Are you using one of the default traces in Profiler or are you creating it manually?

If I were in the same situation, I would just save the trace to a regular text file and then import that file into a SQL Server table.

USE mydb 
GO 

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default)
GO

At least that way you are certain to get the same columns everytime. As to the BULK INSERT statements, you could try this filter.

Column Filter for TextData with the Not Like set to %BULK INSERT%.

Sql-server – SQL Server Profiler behavior

SQL Trace (what Profiler is built on) will capture all instances of any events that you decide to collect. So if you collect RPC:Completed events, you'll get an event for every RPC completed, trace will be capturing that event. If you don't select that event, trace won't be grabbing it. So it will only capture what it is told to.

You can (and should) then further filter the data returned by adding filters on various columns (say a database name or a duration for a completed event to capture only events with a duration of greater than a certain value, for example). In that case, all of the instances of that event are still captured, but the filter is then applied before the information is dumped to the mechanism you are using to trace.

See the difference? If you don't select the event, that event isn't captured or sent (Emitted as you are saying, if I comprehend you correctly), but if you do select the event - all instances are captured but only those that pass the filter criteria are sent (emitted).

I would also argue against using profiler but consider doing a Server Side trace to a file. Look at this blog post from Jonathan Kehayias to show the difference in performance impact between the methods.

Best Answer

Related Solutions

Sql-server – Sql profiler replay table

Sql-server – SQL Server Profiler behavior

Related Question