StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimize sql query that's using 15% cpu time
text
Body
copied!<p>I have the following query that's eating up %15 cpu time on the sql server.</p> <pre><code>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) </code></pre> <p>Can someone offer any pointers to help me optimize this query?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload