1

We have following SQL command in our application.

SELECT trackerid,taskid,action,sendto, sendtofullname, recfrom, actiontime 
FROM [LO].[dbo].[TRACKER] WITH (NOLOCK) 
where [EVENTRECORDID] = ? and ACTION in (1,4,5,15,4000,4200) 
order by Actiontime

It ran some time and it was very quick, but last days it became extreme slowly. I changed it to 

SELECT trackerid,taskid,action,sendto, sendtofullname, recfrom, actiontime 
FROM [LO].[dbo].[TRACKER] 
where [EVENTRECORDID] = ? and ACTION in (1,4,5,15,4000,4200) 
order by Actiontime

So I remove the section WITH (NOLOCK).

And it became fast again. It makes me any sense and i have no idea, why it has this behavior. I am also worried it became slow again. Could somebody explain it to me?

And DB server is MS SQL 2008

Thanks

Ocaso Protal
  • 19,362
  • 8
  • 76
  • 83
iaragorn
  • 53
  • 1
  • 8
  • You need to compare the execution plans. Do they appear the same? What do they look like? Do they both have a scan? – Martin Smith Sep 09 '11 at 11:50
  • Try comparing [execution plans](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan), that said there is a good chance that the cached plan was the problem and the act of changing the query just caused it to use a new plan – Justin Sep 09 '11 at 11:53
  • @Justin - If stats were updated that would cause an optimality based recompilation of the first query. – Martin Smith Sep 09 '11 at 11:55
  • If this is reproducible (and sounds like it is from the fact that it was like it for days) then the only reasons I can think they would be different is either that the nolock one can use an allocation ordered scan. Not sure if that can be catastrophically slower in some circumstances. Or possibly if the query is parameterised (do you have forced parameterisation turned on?) then could be a parameter sniffing issue. – Martin Smith Sep 09 '11 at 12:25

1 Answers1

0

I compared the execution plans, and they are the same.

One thing came to my mind. Every night we execute index reorganization (and once a week index rebuild). But after index reorganization we forgot update statistics. Could it have some connection?

iaragorn
  • 1
  • 1
  • Are you the OP? If so, can you update the email address to the same as the other account so I can merge both accounts. Please flag this when you've actioned this. Thanks. – Kev Sep 09 '11 at 22:21
  • Dodgy stats should affect both versions of the query. When you look at the plans do these queries have any of the literal values replaced with parameters? – Martin Smith Sep 09 '11 at 22:57
  • @iaragorn - OP = Original Poster i.e. Are you the person that asked the original question. – Martin Smith Sep 10 '11 at 18:49
  • I look on the plans and no literal values are replaced with parameters – iaragorn Sep 12 '11 at 08:20