Why would Query cost and Execution plan change

Transact-SQL
,
1

I am using SQL 2008 R2
Why would the query execution plan change if I put a variable in the place of a number. My table tblwebreport has over 600 million records and has a nonclustered index called
CREATE NONCLUSTERED INDEX [IX_tblWebReport_ActivityDate_ActivityTypeID_CompanyID] ON [dbo]. tblWebReport]
( [ActivityDate] ASC,
[CompanyID] ASC,
[ActivityTypeID] ASC,
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

This is the TSQL statement I am running

Declare @CompanyID int, @ActivityDate smalldatetime
Set @companyid = 3
Set @ActivityDate = Getdate()-1

select sessionid, MIN(webreportid) as minReportID, count() from tblwebreport with (nolock)
where ActivityDate = @ActivityDate and CompanyID = 3 and ActivityTypeID = 1024
group by sessionid
having COUNT() > 1

When I run this the execution plan will use the IX_tblWebReport_ActivityDate_ActivityTypeID_CompanyID for an index seek with a cost of 18% and a keylookup of 18%

If I change Company = @companyid then I get an execution plan that is saying I have a missing index. Index seek 0% and a key lookup cost 99%

      CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
      ON [dbo].[tblWebReport] ([CompanyID],[ActivityTypeID],[ActivityDate])
      INCLUDE ([WebReportID],[SessionID])
2

With the first query, SQL is able to do a better estimate of the number of rows to be read, so it is able to do a seek.

For the second query, the estimate is higher, so rather than seeking on the index, SQL must scan it or the table.

Don't have enough details to tell you any more than that right now.

3

In some cases, SQL Server has no clue about the variable at run time. You can sometimes solve the problem by adding a statement level OPTION(RECOMPILE) or by copying the input parameter to another variable and then using that variable. You an also use "optimize for", although I don't recall the precise syntax.

1 Like
4

Thanks Jeff that was it. The OPTION(RECOMPILE) uses the correct Execution plan. Also the optimize for works. Syntax is OPTION(OPTIMIZE FOR (@companyid = 3)). Copying to another variable did not work.

Thanks for the help
Tim

5

You bet. Thank you for the feedback.