This query involves 3 tables:

CREATE TABLE [VPOFF] (
[VACYEAR] [smallint] NOT NULL CONSTRAINT [DF_VPOFF_VACYEAR] DEFAULT
(0),
[OFF_COD] [char] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
CONSTRAINT [DF_VPOFF_OFF_COD] DEFAULT (''),
[VACSTART] [smalldatetime] NOT NULL CONSTRAINT [DF_VPOFF_VACSTART]
DEFAULT (0),
[VACSTOP] [smalldatetime] NOT NULL CONSTRAINT [DF_VPOFF_VACSTOP]
DEFAULT (0),
[DSAVAIL] [decimal](8, 5) NOT NULL CONSTRAINT [DF_VPOFF_DSAVAIL]
DEFAULT (0),
[DSFULL] [decimal](8, 5) NOT NULL CONSTRAINT [DF_VPOFF_DSFULL]
DEFAULT (0),
[SLOTLIMIT] [smallint] NOT NULL CONSTRAINT [DF_VPOFF_SLOTLIMIT]
DEFAULT (0)
) ON [PRIMARY]
GO

CREATE TABLE [VPOFFSLOTS] (
[VACDATE] [smalldatetime] NOT NULL CONSTRAINT
[DF_VPOFFSLOTS_VACDATE] DEFAULT (0)
) ON [PRIMARY]
GO

CREATE TABLE [VPVAC] (
[VACYEAR] [smallint] NOT NULL CONSTRAINT [DF_VPVAC_VACYEAR] DEFAULT
(0),
[OFF_COD] [char] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
CONSTRAINT [DF_VPVAC_OFF_COD] DEFAULT (''),
[SUITSID] [char] (6) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
CONSTRAINT [DF_VPVAC_SUITSID] DEFAULT (''),
[VACTYPE] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL CONSTRAINT [DF_VPVAC_VACTYPE] DEFAULT (''),
[VACDURATION] [decimal](4, 2) NOT NULL CONSTRAINT [DF_VPVAC_VACDUR]
DEFAULT (0),
[VACSTART] [smalldatetime] NOT NULL CONSTRAINT [DF_VPVAC_VACSTART]
DEFAULT (0),
[VACSTOP] [smalldatetime] NOT NULL CONSTRAINT [DF_VPVAC_VACSTOP]
DEFAULT (0),
[RESSTART] [smalldatetime] NOT NULL CONSTRAINT [DF_VPVAC_RESSTART]
DEFAULT (0),
[RESSTOP] [smalldatetime] NOT NULL CONSTRAINT [DF_VPVAC_RESSTOP]
DEFAULT (0),
[FUSEDATE] [smalldatetime] NOT NULL CONSTRAINT [DF_VPVAC_FUSEREC]
DEFAULT (0),
[FUSETYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL CONSTRAINT [DF_VPVAC_FUSETYPE] DEFAULT (''),
[LINKDATE] [smalldatetime] NOT NULL CONSTRAINT [DF_VPVAC_LINKREC]
DEFAULT (0),
[LINKTYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL CONSTRAINT [DF_VPVAC_LINKTYPE] DEFAULT (''),
[WLIST] [bit] NOT NULL CONSTRAINT [DF_VPVAC_WLIST] DEFAULT (0),
[CARRYOVER] [bit] NOT NULL CONSTRAINT [DF_VPVAC_COVER] DEFAULT (0),
[HOL] [bit] NOT NULL CONSTRAINT [DF_VPVAC_HOL] DEFAULT (0),
[ADMINONLY] [bit] NOT NULL CONSTRAINT [DF_VPVAC_ADMINONLY] DEFAULT
(0),
[UNTAKEN] [bit] NOT NULL CONSTRAINT [DF_VPVAC_UNTAKEN] DEFAULT (0),
[DATEINS] [datetime] NOT NULL CONSTRAINT [DF_VPVAC_DATEINS] DEFAULT
(0),
[LASTUPDATE] [datetime] NOT NULL CONSTRAINT [DF_VPVAC_LASTUPDATE]
DEFAULT (0),
[CIRC] [tinyint] NOT NULL CONSTRAINT [DF_VPVAC_CIRC] DEFAULT (3)
) ON [PRIMARY]
GO


VPOFF has one row for each day in a year
VPOFFSLOTS has one row for each 15 minute time period across the whole
year
VPVAC contains one row for each vacation scheduled

The clustered indexes for each table are as follows:
VPOFF: VACYEAR, OFF_COD, VACSTART, VACSTOP, DSAVAIL, DSFULL, SLOTLIMIT
VPOFFSLOTS: VACDATE
VPVAC: VACYEAR, OFF_COD, SUITSID, VACSTART

The stored procedure looks in the VPOFF table to find a particular
day. It joins with the VPOFFSLOTS table to generate a each 15 minute
time slice from the start to the end of the office hours defined by
VPOFF. It then joins to the VPVAC table to see how many vacation
codes are taken in that time period. It sums up the number of people
gone at any given 15 minute increment during the day. Here is the
code for the procedure:

create procedure getStaffingDetail
@vacYear smallint,
@officeCode char(4),
@detailDate smalldatetime
as

select s.VACDATE,
sum(coalesce(v.WLIST + 1, 0)) SFULL
from VPOFF o
inner join VPOFFSLOTS s
on s.VACDATE >= o.VACSTART
and s.VACDATE < o.VACSTOP
left join VPVAC v
on v.VACYEAR = @vacYear
and v.OFF_COD = @officeCode
and v.VACSTART <= s.VACDATE
and v.VACSTOP > s.VACDATE
and v.WLIST = 0
where o.OFF_COD = @officeCode
and o.VACYEAR = @vacYear
and o.VACSTART >= @detailDate
and o.VACSTART < dateadd(dd, 1, @detailDate)
group by s.VACDATE
order by s.VACDATE

When ran with the following command:

exec getStaffingDetail 2008, 'FRC1', '2008-02-01'

it takes roughly 4-5 seconds to run to completion.


When I take the same query and paste it into QA and fill out the
variable names with the supplied values:

select s.VACDATE,
sum(coalesce(v.WLIST + 1, 0)) SFULL
from VPOFF o
inner join VPOFFSLOTS s
on s.VACDATE >= o.VACSTART
and s.VACDATE < o.VACSTOP
left join VPVAC v
on v.VACYEAR = 2008
and v.OFF_COD = 'FRC1'
and v.VACSTART <= s.VACDATE
and v.VACSTOP > s.VACDATE
and v.WLIST = 0
where o.OFF_COD = 'FRC1'
and o.VACYEAR = 2008
and o.VACSTART >= '2008-02-01'
and o.VACSTART < dateadd(dd, 1, '2008-02-01')
group by s.VACDATE
order by s.VACDATE

It runs in .17 seconds. The execution plans for the two queries are
as follows:

StmtText
------------------------------
SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exec getStaffingDetail 2008, 'FRC1', '2008-02-01'
CREATE procedure getStaffingDetail
@vacYear smallint,
@officeCode char(4),
@detailDate smalldatetime
as

select s.VACDATE,
sum(coalesce(v.WLIST + 1, 0)) SFULL
from VPOFF o
inner join VPOFFSLOTS s
on s.VACDATE >= o.VACSTART


(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Parallelism(Gather Streams, ORDER BY:([s].[VACDATE] ASC))
|--Compute Scalar(DEFINE:([Expr1003]=If ([Expr1009]=0)
then NULL else [Expr1010]))
|--Stream Aggregate(GROUP BY:([s].[VACDATE]) DEFINE:
([Expr1009]=COUNT_BIG(If (Convert([v].[WLIST])+1<>NULL) then
(Convert([v].[WLIST])+1) else 0), [Expr1010]=SUM(If (Convert([v].
[WLIST])+1<>NULL) then (Convert([v].[WLIST])+1) else 0)))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([s].[VACDATE]), ORDER BY:([s].[VACDATE] ASC))
|--Nested Loops(Left Outer Join, WHERE:([v].
[VACSTART]<=[s].[VACDATE] AND [v].[VACSTOP]>[s].[VACDATE]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([s].[VACDATE]))
| |--Clustered Index Scan(OBJECT:
([VAC3].[dbo].[VPOFFSLOTS].[ci] AS [s]), ORDERED FORWARD)
| |--Filter(WHERE:([s].
[VACDATE]<[o].[VACSTOP]))
| |--Index Spool(SEEK:([o].
[VACSTART] <= [s].[VACDATE]))
| |--Clustered Index
Seek(OBJECT:([VAC3].[dbo].[VPOFF].[C_YearOffDate] AS [o]), SEEK:([o].
[VACYEAR]=[@vacYear] AND [o].[OFF_COD]=[@officeCode] AND [o].
[VACSTART] >= [@detailDate] AND [o].[VACSTART] < dateadd(day
|--Table Spool
|--Clustered Index Seek(OBJECT:
([VAC3].[dbo].[VPVAC].[C_YearOffSuits] AS [v]), SEEK:([v].
[VACYEAR]=[@vacYear] AND [v].[OFF_COD]=[@officeCode]), WHERE:
(Convert([v].[WLIST])=0) ORDERED FORWARD)

(12 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select s.VACDATE,
sum(coalesce(v.WLIST + 1, 0)) SFULL
from VPOFF o
inner join VPOFFSLOTS s
on s.VACDATE >= o.VACSTART
and s.VACDATE < o.VACSTOP
left join VPVAC v
on v.VACYEAR = 2008
and v.OFF_COD = 'FRC1'
and v.VA

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY:([s].[VACDATE] ASC))
|--Compute Scalar(DEFINE:([Expr1003]=If ([Expr1011]=0) then
NULL else [Expr1012]))
|--Hash Match(Aggregate, HASH:([s].[VACDATE]) DEFINE:
([Expr1011]=COUNT_BIG(If (Convert([v].[WLIST])+1<>NULL) then
(Convert([v].[WLIST])+1) else 0), [Expr1012]=SUM(If (Convert([v].
[WLIST])+1<>NULL) then (Convert([v].[WLIST])+1) else 0)))
|--Nested Loops(Left Outer Join, WHERE:([v].
[VACSTART]<=[s].[VACDATE] AND [v].[VACSTOP]>[s].[VACDATE]))
|--Nested Loops(Inner Join, OUTER REFERENCES:
([o].[VACSTOP], [o].[VACSTART]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([Expr1008], [Expr1009], [Expr1010]))
| | |--Compute Scalar(DEFINE:
([Expr1008]='Feb 1 2008 12:00AM', [Expr1009]=Convert('Feb 2 2008
12:00AM')+'Jan 1 1900 12:01AM', [Expr1010]=22|If (Convert('Feb 2
2008 12:00AM')+'Jan 1 1900 12:01AM'=NULL) then 0 else 10))
| | | |--Constant Scan
| | |--Clustered Index Seek(OBJECT:([VAC3].
[dbo].[VPOFF].[C_YearOffDate] AS [o]), SEEK:([o].[VACYEAR]=2008 AND
[o].[OFF_COD]='FRC1' AND [o].[VACSTART] > [Expr1008] AND [o].
[VACSTART] < [Expr1009]), WHERE:(Convert([o].[VACSTART]
| |--Clustered Index Seek(OBJECT:([VAC3].
[dbo].[VPOFFSLOTS].[ci] AS [s]), SEEK:([s].[VACDATE] >= [o].[VACSTART]
AND [s].[VACDATE] < [o].[VACSTOP]) ORDERED FORWARD)
|--Table Spool
|--Clustered Index Seek(OBJECT:([VAC3].
[dbo].[VPVAC].[C_YearOffSuits] AS [v]), SEEK:([v].[VACYEAR]=2008 AND
[v].[OFF_COD]='FRC1'), WHERE:(Convert([v].[WLIST])=0) ORDERED
FORWARD)

(12 row(s) affected)

StmtText
-----------------------------
SET STATISTICS PROFILE OFF

(1 row(s) affected)


I can see that they are drastically different, but I don't know why.

I've read up on parameter sniffing here:
http://www.lockergnome.com/sqlsquirr...eter-sniffing/

And applied these changes to my procedure:

ALTER procedure getStaffingDetail
@vacYear smallint,
@officeCode char(4),
@detailDate smalldatetime
as

declare @localVacYear smallint
declare @localOfficeCode char(4)
declare @localDetailDate smalldatetime

set @localVacYear = @vacYear
set @localOfficeCode = @officeCode
set @localDetailDate = @detailDate

select s.VACDATE,
sum(coalesce(v.WLIST + 1, 0)) SFULL
from VPOFF o
inner join VPOFFSLOTS s
on s.VACDATE >= o.VACSTART
and s.VACDATE < o.VACSTOP
left join VPVAC v
on v.VACYEAR = @localVacYear
and v.OFF_COD = @localOfficeCode
and v.VACSTART <= s.VACDATE
and v.VACSTOP > s.VACDATE
and v.WLIST = 0
where o.OFF_COD = @localOfficeCode
and o.VACYEAR = @localVacYear
and o.VACSTART >= @localDetailDate
and o.VACSTART < dateadd(dd, 1, @localDetailDate)
group by s.VACDATE
order by s.VACDATE

This change made the procedure run 1:22 minutes till completion. Here
was the execution plan:

StmtText
------------------------------
SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exec getStaffingDetail 2008, 'FRC1', '2008-02-01'
CREATE procedure getStaffingDetail
@vacYear smallint,
@officeCode char(4),
@detailDate smalldatetime
as

declare @localVacYear smallint
declare @localOfficeCode char(4)
declare @localDetailDate smalldatetime

set @localVacYear = @vacYe

set @localOfficeCode = @officeCode

set @localDetailDate = @detailDate

select s.VACDATE,
sum(coalesce(v.WLIST + 1, 0)) SFULL
from VPOFF o
inner join VPOFFSLOTS s
on s.VACDATE >= o.VACSTART
and s.VACDATE < o.VACSTOP
left join VPVAC v
on v.VACYEAR = @localVacYear
and v.OFF_COD = @localOffic

(5 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1003]=If ([globalagg1006]=0)
then NULL else [globalagg1008]))
|--Parallelism(Gather Streams, ORDER BY:([s].[VACDATE]
ASC))
|--Stream Aggregate(GROUP BY:([s].[VACDATE]) DEFINE:
([globalagg1006]=SUM([partialagg1005]),
[globalagg1008]=SUM([partialagg1007])))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([s].[VACDATE]), ORDER BY:([s].[VACDATE] ASC))
|--Stream Aggregate(GROUP BY:([s].
[VACDATE]) DEFINE:([partialagg1005]=COUNT_BIG(If (Convert([v].[WLIST])
+1<>NULL) then (Convert([v].[WLIST])+1) else 0),
[partialagg1007]=SUM(If (Convert([v].[WLIST])+1<>NULL) then
(Convert([v].[WL
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([s].[VACDATE]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([s].[VACDATE]) WITH PREFETCH)
| |--Clustered Index
Scan(OBJECT:([VAC3].[dbo].[VPOFFSLOTS].[ci] AS [s]), ORDERED FORWARD)
| |--Clustered Index
Seek(OBJECT:([VAC3].[dbo].[VPOFF].[C_YearOffDate] AS [o]), SEEK:([o].
[VACYEAR]=[@localVacYear] AND [o].[OFF_COD]=[@localOfficeCode] AND
([o].[VACSTART], [o].[VACSTOP]) > ([@localDetailDate], [s].
|--Filter(WHERE:(Convert([v].
[WLIST])=0))
|--Bookmark Lookup(BOOKMARK:
([Bmk1002]), OBJECT:([VAC3].[dbo].[VPVAC] AS [v]) WITH PREFETCH)
|--Index Seek(OBJECT:
([VAC3].[dbo].[VPVAC].[Calendar] AS [v]), SEEK:([v].
[VACYEAR]=[@localVacYear] AND [v].[OFF_COD]=[@localOfficeCode] AND [v].
[VACSTART] <= [s].[VACDATE]), WHERE:([v].[VACSTOP]>[s].[VACDATE]

(12 row(s) affected)

StmtText
-----------------------------
SET STATISTICS PROFILE OFF

(1 row(s) affected)


So, obviously preventing the parameter sniffing did not improve
performance.

I've no clue what's causing this and don't know what else to try. Any
ideas?

kaht77,

Yes, I believe that you are suffering from parameter sniffing, as you read
about, but the solution to move the data from parameters into variables can
be worse than the parameter sniffing.

Since variables by definition have no value when the procedure is compile,
there is no effective way for the optimizer to use statistics. Therefore it
estimates something like a 30% selectivity, which compiles for the slow,
scan the table type of plan. Definitely you should remove the variables and
return to using the parameters.

You could do a couple of other things, such as have a procedure for near
term vacations and another for the broader sweep of vacation history. If
you scope these correctly, you may have two procedures that are very
similar, but run by different plan.

Another approach is to create the procedure WITH RECOMPILE, so that it
always recompiles. This means that there is always a compile hit for every
call (a goodly part of a second), but the rest of the execution is more
leveled. You might also want to read about OPTION (KEEPFIXEDPLAN) and see
if that is useful.

RLF


<kaht77> wrote in message
news:a565
[..]

>>You could do a couple of other things, such as have a procedure for near
term vacations and another for the broader sweep of vacation history.
If
you scope these correctly, you may have two procedures that are very
similar, but run by different plan.


All vacations in this procedure would be near term vacations. The
procedure only pulls the information for one day for one office.
There is another procedure that pulls all vacations across an entire
month, and was largely what the 1-day procedure was based on. Just
for the record, the procedure that pulls all vacation codes across the
whole month runs in about 1.3 seconds - which makes no sense why a
similar query that narrows down to 1/30th of data would run in 4-5
seconds.

>>Another approach is to create the procedure WITH RECOMPILE, so that it
always recompiles. This means that there is always a compile hit for
every
call (a goodly part of a second), but the rest of the execution is
more
leveled.

I tried this approach already, it used the same execution plan as the
pre-compiled version and basically ran in about the same amount of
time.

>> You might also want to read about OPTION (KEEPFIXEDPLAN) and see
if that is useful.

This looks like it would work if the proc ever compiled with the
correct execution plan in the first place. After the proc is freshly
compiled and ran, it runs with the execution plan posted above that
takes about 4-5 seconds to complete.

When viewing the graphical execution plan it shows this section:

| |--Filter(WHERE:([s].
[VACDATE]<[o].[VACSTOP]))
| |--Index Spool(SEEK:([o].
[VACSTART] <= [s].[VACDATE]))

Taking up roughly about 50% of the total time. This step does not
occur at all when I run the query as a standalone in QA. Also, when
running the query by itself in QA, the query uses a clustered index
seek on the VPOFFSLOTS table, but does a clustered index scan with the
proc.

Any other ideas?

Given the huge amount of code etc you posted perhaps you could consider
having someone help you professionally? Chewing through that amount of
stuff is quite a bit to ask someone to do in their spare time. :-)

Didn't go through the entire post carefully but here are some things to
maybe try:

1) make problem statements into dynamic sql. Pretty much guaranteed to get
correct plan when hard-coded values are present.

2) recompile sproc and/or statements (if 2005).

3) forcing the plan, specifying the plan, or using INDEX hints. Note that
these can lead to really BAD plans for certain parameter inputs (just like
you can get from parameter sniffing).

(kaht77) writes:
> So, obviously preventing the parameter sniffing did not improve
> performance.

No, as Russell said this is not likely to increase performance. In general,
it's usually not a good idea to prevent parameter sniffing entirely, but
you need to watch out for scenarios where a procedure gets stuck with
the plan for a non-typical value in the cache.

I should guess that your problem is more that the optimizer does not
sniff deep enough. Hard-coded parameter values gives more information
than sniffed parameters. After all, the hard-coded values never change.

> I've no clue what's causing this and don't know what else to try. Any
> ideas?

I'm running out of time for a closer analysis, but I notice that the
procedure plan includs parallelism. Have you tried adding OPTION (MAXDOP 1)
to prevent parallelism? (MAXDOP = Max degree of parallelism).

> Given the huge amount of code etc you posted perhaps you could consider
> having someone help you professionally? Chewing through that amount of
> stuff is quite a bit to ask someone to do in their spare time. :-)

The problematic "stuff" is only one query in one small proc. I posted
everything else as reference. Had I not provided it in my initial
post then surely someone would have eventually asked for it.

I had not considered dynamic SQL, so thank you for that suggestion.
Sounds like it makes sense.

I will also look into the MAXDOP option.

Thank you for the replies.

kaht77,

Just curious if you solved your problem. Especially if MAXDOP made a
difference.

RLF

<kaht77> wrote in message
news:b60d
[..]

> Just curious if you solved your problem. Especially if MAXDOP made a
> difference.

Sorry for the late reply. Yes, MAXDOP did make a big difference.
Here was the new execution plan:


StmtText
------------------------------
SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exec getStaffingDetail 2008, '2002', '2008-01-08'
CREATE procedure getStaffingDetail
@vacYear smallint,
@officeCode char(4),
@detailDate smalldatetime
as

select s.VACDATE,
o.DSAVAIL,
o.DSFULL,
o.SLOTLIMIT,
sum(coalesce(v.WLIST + 1, 0)) SFULL
from VPOFF o
inner join V

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1003]=If ([Expr1009]=0) then
NULL else [Expr1010]))
|--Stream Aggregate(GROUP BY:([s].[VACDATE], [o].
[DSAVAIL], [o].[DSFULL], [o].[SLOTLIMIT]) DEFINE:
([Expr1009]=COUNT_BIG(If (Convert([v].[WLIST])+1<>NULL) then
(Convert([v].[WLIST])+1) else 0), [Expr1010]=SUM(If (Convert([v].
[WLIST])+1<>NULL) th
|--Nested Loops(Left Outer Join, WHERE:([v].
[VACSTART]<=[s].[VACDATE] AND [v].[VACSTOP]>[s].[VACDATE]))
|--Sort(ORDER BY:([s].[VACDATE] ASC, [o].
[DSAVAIL] ASC, [o].[DSFULL] ASC, [o].[SLOTLIMIT] ASC))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([o].[VACSTOP], [o].[VACSTART]) WITH PREFETCH)
| |--Clustered Index Seek(OBJECT:([VAC3].
[dbo].[VPOFF].[C_YearOffDate] AS [o]), SEEK:([o].[VACYEAR]=[@vacYear]
AND [o].[OFF_COD]=[@officeCode] AND [o].[VACSTART] >= [@detailDate]
AND [o].[VACSTART] < dateadd(day, 1, [@detailDa
| |--Clustered Index Seek(OBJECT:([VAC3].
[dbo].[VPOFFSLOTS].[ci] AS [s]), SEEK:([s].[VACDATE] >= [o].[VACSTART]
AND [s].[VACDATE] < [o].[VACSTOP]) ORDERED FORWARD)
|--Table Spool
|--Clustered Index Seek(OBJECT:([VAC3].
[dbo].[VPVAC].[C_YearOffSuits] AS [v]), SEEK:([v].[VACYEAR]=[@vacYear]
AND [v].[OFF_COD]=[@officeCode]), WHERE:(Convert([v].[WLIST])=0)
ORDERED FORWARD)

(9 row(s) affected)

StmtText
-----------------------------
SET STATISTICS PROFILE OFF

(1 row(s) affected)



This made the query run in .36 seconds, instead of the 4-5 seconds it
was taking before. Thanks a lot for the suggestion!