CREATE procedure [dbo].[Problem_Stored_Procedure] (

@blank uniqueidentifier,

@blank int,

@blank bit = null,

@blank bit = null,

@blank datetime = null

)

as

if @blank is null

begin

set @Now = getdate()

end

SELECT COUNT(distinct imr.column1) AS CountMessage

FROM Table1 imr

inner join Table2 im on

(

im.column1 = imr.column1

and im.column2 = @blank

and im.column3 = @blank

)

WHERE imr.column2 > CONVERT(DATETIME, @blank , 103)

and case when @blank is null then im.column4 else @blank end = im.column4

and case when @blank is null then case when imr.column3 is null then 0 else 1 end else @blank end = case when imr.column3 is null then 0 else 1 end

Basically the point is to get the number of messages that a user has sent out, that have been read by the recipient. You have the variables left in as hints as well.

I am also getting this missing index hint

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON imr (column2)

INCLUDE (column1,column3)

I tried adding this on production but it actually made the problem worse (this SP started queuing up like crazy in exec_requests) even though it gives a much nicer execution plan and stops the REOURCE_SEMEPHORE wait types happening.