Sql-server – How to use an OR in SQL Profiler for Column Filters

profilersql serversql-server-2008-r2

I am setting up a SQL Profiler and I wanted to have it filter on any items that have a duration > 30000 OR CPU > 10000 OR Writes > 1000

It seems like if I put those into the "column filter" area that it is an AND (i.e. I only get items when the duration, cpu and writes are over those limits).

Is there a way to have those be OR so that I get any items that fit any one of those criteria? I know I could start up three different profilers, each with the specific criteria, but it would be nice to have that all in one window/output.

I am currently on SQL 2008 R2, but hoping there is a general answer to this item.

Best Answer

You can't. SQL Server Profiler is a bit restricted in the way it displays and filters data.

Solution

You would be better off storing the traced data into a separate table on the same server and then querying the data at given intervals. The big benefit being you have the data in the table and can perform any complex SELECT statement you wish. Ensure you don't capture too much data by specifying one or two filter criteria.

You could also consider running the SQL Server Profiler from the command prompt which will reduce the overhead of displaying the data constantly in an interface.

The parameters are:

---------------------------
SQL Server Profiler
---------------------------
Usage: PROFILER.EXE
    [/S<SQL Server instance name>]
    [/A]<Analysis Services instance name>
    [/D<database>]
    [/B<trace table to load>]
    [/E] trusted connection
    [/U<login id>]
    [/P<password>]
    [/F<trace file to load>]
    [/T<template file to load or use when starting a new trace>]
    [/L<locale id>]
    [/M<trace stop time>]
    [/O<trace output file name>]
    [/Z<maximum output file size (MB)>]
    [/R<use rollover files>]

Reference: Profiler (Microsoft Docs)

Steps

  1. You would create a template for your standard profiling and use this in the command-line startup
  2. Start SQL Profiler via command-line referencing your template
  3. Query data stored in table

Alternatives

Seeing as you might be looking at a migration in the near future and because SQL Server Profiler is going out of fashion as mentioned by Microsoft, you could consider bringing your skills up-to-date by looking at Extended Events.

IMPORTANT!!
We are announcing the deprecation of SQL Server Profiler for Database Engine Trace Capture and Trace Replay. These features are available in SQL Server 2016 but will be removed in a later version.+ The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects will also be deprecated.

Reference: SQL Server Profiler (Microsoft Docs)

Extended Events

Extended Events is a light weight performance monitoring system that uses very few performance resources. Albeit with the slight drawback that there was no GUI for SQL Server 2008 (ok, there is an add-on for SSMS, which can be found on codeplex.)

An introduction into Extended Events can be found on Microsoft's site.

Related Question