April 14, 2015 at 11:13 am
I have set up a specialized trace to capture only stored procedure calls, using a filter like
exec sp_trace_setfilter @traceidout, 1, 0, 6, N'%USP[_]%' -- Filter Select USP_ prefix
But when I look at my trace file, I have 90 % rows in which TextData is NULL
Not sure where the NULLs come from in the first place, but can I omit them, and what would be the syntax ?
EDIT: Looks like this works.
exec sp_trace_setfilter @traceidout, 1, 0, 1, NULL -- Filter Omit NULL
April 14, 2015 at 12:20 pm
Fair warning, filtering in trace occurs after the trace event is captured. Filtering only really helps with the data you're storing from the capture. Extended events work much better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2015 at 3:02 pm
Could you elaborate on "Extended events work much better." ??
April 14, 2015 at 4:36 pm
Extended events filter at the point of capture. That means they radically reduce their footprint as they will only capture the events you're interested in. Trace captures every event first, then filters out the events it doesn't need. HUGE difference in performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2015 at 10:02 am
Thanks. Currently I'm working with a mix of 2005 & 2008 servers, so extended events won't work on 2005 (I think).
I'll read up on it for the 2008 servers, as I have not done much with them.
April 16, 2015 at 11:49 am
homebrew01 (4/16/2015)
Thanks. Currently I'm working with a mix of 2005 & 2008 servers, so extended events won't work on 2005 (I think).I'll read up on it for the 2008 servers, as I have not done much with them.
They're only 2008 and up.
The big issue for a lot of people using them with 2008 is that there's no GUI. That was introduced in 2012.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply