July 30, 2007 at 1:38 pm
friday we noticed a poor running query with an index scan instead of an index seek. Friday night i dropped and recreated the index and it ran OK afterwards.
on saturday we ran update statistics with full scan on the entire database, and the normal update statistics on sunday night. Come in this morning and it's an index scan again. I checked the frag data from the weekend and it's less than 1%.
On one of our subscriber servers it runs properly with an index seek. I checked it on one of our QA servers even restoring this morning's full backup and it ran with an index seek.
does anyone have any idea why this is?
August 1, 2007 at 2:46 am
there are lots of reasons - try set options and ansi nulls / quoted ident to start.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 2, 2007 at 8:04 am
This sounds like a parameter sniffing issue. Assuming this is a sproc, use the WITH RECOMPILE option.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 2, 2007 at 8:30 am
i'll try that
not a sp, but a query from a web application for a report
August 2, 2007 at 8:47 am
Since you are using SQL 2005, there is also an OPTION you can use with the query statement. See here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/66fb1520-dcdf-4aab-9ff1-7de8f79e5b2d.htm
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply