Those who have been following this site regularly would know that a 10-part tutorial series on the SQL Server Profiler, authored by me just was recently published. You can find the master page here. This week, I would try to address some of the questions that have come from you, the kind readers as part of this series or in the “ASK” module of this site.

The question that I would address today is:

How can I filter multiple objects of the same type?

For example, add a condition like ObjectName NOT LIKE ‘%SP1%’ AND ObjectName NOT LIKE ‘%SP2%’

The SQL Server Profiler is a very powerful performance analysis tool, and this is a fairly common requirement. Here’s a step-by-step guide to realize this requirement. For this demo, let me create 3 very stored procedures. Before we execute these procedures, we will use the Profiler to filter out two of them from the trace.

USE AdventureWorks2012
GO
CREATE PROCEDURE HumanResources.proc_GetEmployes1
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP1',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

CREATE PROCEDURE HumanResources.proc_GetEmployes2
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP2',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

CREATE PROCEDURE HumanResources.proc_GetEmployes3
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP3',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

Now that these procedures are created, let us setup the Profiler to capture the stored procedure execution, filtered such that only the procedure is seen in the trace.

1. Follow the steps provided in the Profiler Tutorial Part #2: http://beyondrelational.com/modules/12/tutorials/631/tutorials/15252/getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-custo.aspx to create a customized template
   • I generally prefer using the “TSQL_SPs” default template as the base and then customize it to suite the requirement
2. When customizing the column filters, add the first line:
3. 
4. Now, hit enter to add a new line
5. 
6. Now, add the second line of filter
7. 
8. Click “OK” to complete configuration of the template

Now, start the Profiler trace and execute the following query:

USE AdventureWorks2012
GO
EXEC HumanResources.proc_GetEmployes1 @businessEntityId = 1
GO
EXEC HumanResources.proc_GetEmployes2 @businessEntityId = 2
GO
EXEC HumanResources.proc_GetEmployes3 @businessEntityId = 3
GO

The Results tab of the SQL Server Management Studio (SSMS) will show the results from the execution of all 3 procedures.

The Profiler would capture the following trace, which only captures the activity from the stored procedure HumanResources.proc_GetEmployees2 and not from the other two procedures. Adding new lines to the Profiler template column filters makes the Profiler to apply an “AND” condition on the filter.

I hope the above simple steps help you to filter your Profiler Results efficiently.

Until we meet next time,

Be courteous. Drive responsibly.