SQL Server – What Does ‘Exclude Rows That Do Not Contain Values’ Checkbox Do in SQL Profiler?
profilersql server
I want to filter a trace and wonder what does 'exclude rows that do not contain values' checkbox exactly do? Is it to filter all the data columns that are not selected in any event class?
Thanks.
Best Answer
It's basically this
WHERE .. AND TheValue NOT NULL
Like/Not like filters only works on values that exist for that event.
No value is always true and passes the filter: this checkbox removes that
For example:
You want to see SQL:BatchStarting events
You filter on Duration
Duration is not valid for SQL:BatchStarting, so the filter is ignored
Duration represents the total time from start to finish the request by SQL Server, which represents time to send over network, cpu time, waits, and execution. As Aaron mentioned once the client has received the data SQL Server has finished it's timing, and the client is then processing. This should not be part of the duration.
I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:
WHILE (@@FETCH_STATUS = 0) AND
( @About80MoreBooleanExpressionsHere)
BEGIN /* 1 */
-- about 700 lines of logic, math and if-parameter-this-then-that
-- stuff omitted INSERT InnerProcedureCallWithinLoop__TraceTable
VALUES (@from_locn, @About53PARAMSOMITTED
EXEC @ConsiderItem =
InnerProcedureCallWithinLoop
@from_locn,
@About53PARAMSOMITTED,
...
If I know that it is only ever called from one location. Otherwise, I do it in the callee instead of the caller.
ALTER PROC InnerProcedureCallWithinLoop
@from_locn int,
@About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc
This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions). If that is your problem, you need to look at CLR or email methods to externalise the captured output.
Best Answer
It's basically this
Like/Not like filters only works on values that exist for that event.
No value is always true and passes the filter: this checkbox removes that
For example:
Duration is not valid for SQL:BatchStarting, so the filter is ignored