Parameter query NOT using Index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Parameter query NOT using Index

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2014-02-04 : 07:28:50

Hi All,

I am working on a sql 2005 Enterprise Edition DB using sp4.

I am seeing strange performance when using a parameter in part of my WHERE clause.
Looking at the Execution plan. The ‘parameter’ query appears NOT use a NON CLUSTERED INDEX on the RowUpdateTime. While the hardcoded value ‘02/02/2014’ does use the NCI when running the TSQL

I have laid out the example sample code to see if anyone can point to anything obvious.
Is it the way im setting my ‘date’ value?

Any help would be great.
As you can imagine the performance difference is huge!
Thanks

--============================================
--Sample TSQL - not
--============================================

----tsql e.g.1 with NO parameter (hardcoded)
--uses INDEX
SELECT *
FROM [LiveDB].[dbo].[LabSpecimenTests]
WHERE (SourceID = 'CCH')
AND ([RowUpdateDateTime] >= '02/02/2014')
AND (NOT [ResultDateTime] IS NULL)

--tsql e.g.2 with parameter
--Does NOT use index?

--declare variable
DECLARE @RefreshDate datetime
--set value
SET @RefreshDate = (SELECT CONVERT(DATETIME, '02/02/2014', 103))

SELECT *
FROM [LiveDB].[dbo].[LabSpecimenTests]
WHERE (SourceID = 'CCH')
AND ([RowUpdateDateTime] >= @RefreshDate)
AND (NOT [ResultDateTime] IS NULL)

--tsql e.g.2 with basic parameter
--Does NOT use index?

--decalre variables
DECLARE @RefreshDate datetime
--set variables
SET @RefreshDate = '02/02/2014'
SELECT *
FROM [LiveDB].[dbo].[LabSpecimenTests]
WHERE (SourceID = 'CCH')
AND ([RowUpdateDateTime] >= @RefreshDate)
AND (NOT [ResultDateTime] IS NULL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-04 : 13:09:37

This is known as parameter sniffing. I would suggest googling it to see what people have done as workarounds. With SQL Server 2012, you can create a plan guide, which is what we do when we encounter parameter sniffing and are unable to change the stored procedure code.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources