SP:CacheRemove

• AC Lupu

  Say Hey Kid

  Points: 709

  More actions

  December 5, 2007 at 6:30 am

  #119855

  I have a SP with a query on a 2 mil table

  The SP takes input parameters used by the query

  If I run the SP code in Query Analyzer window , setting values to the parameters, I get results in 1 sec.

  If I try to run the SP with the same parameters ,it doesn't complete - keeps running.

  SQL Profiler shows SP:CacheRemove event - sql server seems to be busy to free-up memory

  Any suggestions to get this SP running ?

  Thanks

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  December 10, 2007 at 10:37 am

  #758669

  check your query plans - view estimated plans, not actual. I'd give best guess your proc is table scanning not using an index seek - index to suit.

  ( could also be parameter sniffing )

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/