Optimize sql query that's using 15% cpu time

Question

I have the following query that's eating up %15 cpu time on the sql server.

declare
  @JobQueueId int = NULL,   @JobTypeId int = NULL,   @JobTypeIdHasValue BIT = 0,   @TargetId int = NULL,   @TargetIdHasValue BIT = 0,   
  @JobMessage nvarchar(max) = NULL,   @ComputerName nvarchar(60) = NULL,   @ComputerNameHasValue BIT = 0,   @StartedOn datetime = NULL,   
  @StartedOnHasValue BIT = 0,   @CompletedOn datetime = NULL,   @CompletedOnHasValue BIT = 0,   @SkipOrderedBy datetime = NULL,   
  @SkipOrderedByHasValue BIT = 0,   @SkipOrderedOn datetime = NULL,   @SkipOrderedOnHasValue BIT = 0,   @Attempts int = NULL,   
  @AttemptsHasValue BIT = 0,   @FailResult nvarchar(max) = NULL,   @FailResultHasValue BIT = 0,   @CreatedBy uniqueidentifier = NULL,   
  @CreatedOn datetime = NULL  



  SET TRANSACTION ISOLATION LEVEL READ COMMITTED    

  SELECT   [JobQueueId],   [JobTypeId],   [TargetId],   [JobMessage],   [ComputerName],   [StartedOn],   [CompletedOn],   [SkipOrderedBy],     [SkipOrderedOn],   [Attempts],   FailResult],   [CreatedBy],   [CreatedOn]  
  FROM      [dbo].[JobQueue]  

  WHERE   
  ([JobQueueId] = @JobQueueId OR @JobQueueId IS NULL)    
  AND ([JobTypeId] = @JobTypeId OR (@JobTypeId IS NULL AND @JobTypeIdHasValue = 0))   
  AND ([TargetId] = @TargetId OR (@TargetId IS NULL AND @TargetIdHasValue = 0))   
  AND ([JobMessage] = @JobMessage OR @JobMessage IS NULL)   
  AND ([ComputerName] = @ComputerName OR (@ComputerName IS NULL AND @ComputerNameHasValue = 0))   
  AND ([StartedOn] = @StartedOn OR (@StartedOn IS NULL AND @StartedOnHasValue = 0))   
  AND ([CompletedOn] = @CompletedOn OR (@CompletedOn IS NULL AND @CompletedOnHasValue = 0))   
  AND ([SkipOrderedBy] = @SkipOrderedBy OR (@SkipOrderedBy IS NULL AND @SkipOrderedByHasValue = 0))   
  AND ([SkipOrderedOn] = @SkipOrderedOn OR (@SkipOrderedOn IS NULL AND @SkipOrderedOnHasValue = 0))   
  AND ([Attempts] = @Attempts OR (@Attempts IS NULL AND @AttemptsHasValue = 0))   
  AND ([FailResult] = @FailResult OR (@FailResult IS NULL AND @FailResultHasValue = 0))   
  AND ([CreatedBy] = @CreatedBy OR @CreatedBy IS NULL)   
  AND ([CreatedOn] = @CreatedOn OR @CreatedOn IS NULL)

Can someone offer any pointers to help me optimize this query?

Try looking at the execution plan. Evaluate your indexes, do they make sense ? Do you have any indexes on the table ? Do you need more ? — driis
– driis, Commented Feb 12, 2013 at 19:27
Take a look at blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/… — SQLMenace
– SQLMenace, Commented Feb 12, 2013 at 19:29
I've run the query through the DTA. There's about 13 indexes and some 42 statistics on it. I simply need to redo the query at this point. It's just eating up the server and it runs many times a minute. — Prescient
– Prescient, Commented Feb 12, 2013 at 19:49
I'll second, third, and fourth what @driis says. I spent several days recently trying to figure out a performance problem that turned out to be due to several missing indexes. — Hot Licks
– Hot Licks, Commented Feb 12, 2013 at 21:54
The Tuning Advisor gives me no recommendations. It has all the needed indexes. — Prescient
– Prescient, Commented Feb 12, 2013 at 22:31

Aaron Bertrand · Accepted Answer · 2013-02-12 19:58:50Z

1

Since the query is likely to cache a plan that favors a particular parameter, it is probable that different parameters will not benefit much from that plan (do a search on "parameter sniffing" and you'll see that this is a very common issue). A couple of options:

Add OPTION (RECOMPILE) to the statement. This will cost a little more due to compilation each time, but it will at least be predictable and you shouldn't have these massive swings where for some parameters it runs in half a second and for others it runs for a long time.
Use OPTIMIZE FOR UNKNOWN to avoid using the previously cached parameters for optimization.

Turn on the server-level option (via sp_configure) Optimize for ad hoc workloads (see here and here), and change your query to use dynamic SQL. In other words build the query like this:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT ... FROM [dbo].[JobQueue] WHERE 1=1';

IF @JobQueueId IS NOT NULL
  SET @sql = @sql + N' AND JobQueueId = @JobQueueId';

-- ... repeat for other params

EXEC sp_executesql 
  @sql,
  N'@JobQueueId INT, ..., @CreatedOn DATETIME',
  @JobQueueId, ..., @CreatedOn;

Also see: http://www.sommarskog.se/dynamic_sql.html#Dyn_search

edited Feb 12, 2013 at 19:58

answered Feb 12, 2013 at 19:51

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prescient Over a year ago

I think you're on to something. Currently the sproc's were developed using csla. But I think it needs some refinement I think that your 3rd option might be the way to go.

fdreger · Accepted Answer · 2013-02-13 14:59:01Z

1

This is a terrible, terrible query. The sheer number of ANDs and ORs (and their pattern) makes any static reasoning about indexes futile. And the main reason why it's so terrible is because it's "universal": a lot of work that should be static (like checking if @JobQueueId is null) is left to SQL server.

Since you don't profit from any kind of query preparation, you should construct your query in a dynamic fashion and let db server optimize your select for each specific case.

edited Feb 13, 2013 at 14:59

answered Feb 12, 2013 at 21:38

fdreger

12.6k1 gold badge38 silver badges46 bronze badges

1 Comment

Prescient Over a year ago

Thanks for the input. I will keep this in mind when we make changes.

Collectives™ on Stack Overflow

Optimize sql query that's using 15% cpu time

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related