Sql server random slow query

• mdsantos

  SSC-Addicted

  Points: 404

  More actions

  May 20, 2015 at 8:50 am

  #1799345

  The Hint FORCESEEK, seems to do the trick.

  Analysing the "bad" execution plan, I notice that the searches where done with index scan instead of index seek, incresing drastically the reads on the tables.

  I really don't know why the Query Optimizer sometimes decides to use the scan index... but when it does the query takes a long time, plan is cached and the problems start.

  Thats why freeproccache was a temporary solution (couple of hours), things were okay till the query optimizer decides to use scan index again.

  Let's see if this is a definitive solution.... hope so.

  Thank you everybody.

• Eric M Russell

  SSC Guru

  Points: 125595

  More actions

  May 20, 2015 at 9:02 am

  #1799354

  mdsantos (5/20/2015)

  ---

  The Hint FORCESEEK, seems to do the trick.

  Analysing the "bad" execution plan, I notice that the searches where done with index scan instead of index seek, incresing drastically the reads on the tables.

  I really don't know why the Query Optimizer sometimes decides to use the scan index... but when it does the query takes a long time, plan is cached and the problems start.

  Thats why freeproccache was a temporary solution (couple of hours), things were okay till the query optimizer decides to use scan index again.

  Let's see if this is a definitive solution.... hope so.

  Thank you everybody.

  The query optimizer's decision of using scan versus seek for alternate plans is driven by cost estimations based on table statistics. For example, if it estimates only a relatively few number of rows will be returned, then it will use seek, but for an estimated large number of rows, a partial or full scan is more efficient. Investigate into whether statistics need to be updated more frequently on these tables.

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

• Grant Fritchey

  SSC Guru

  Points: 399047

  More actions

  May 20, 2015 at 9:02 am

  #1799355

  mdsantos (5/20/2015)

  ---

  The Hint FORCESEEK, seems to do the trick.

  Analysing the "bad" execution plan, I notice that the searches where done with index scan instead of index seek, incresing drastically the reads on the tables.

  I really don't know why the Query Optimizer sometimes decides to use the scan index... but when it does the query takes a long time, plan is cached and the problems start.

  Thats why freeproccache was a temporary solution (couple of hours), things were okay till the query optimizer decides to use scan index again.

  Let's see if this is a definitive solution.... hope so.

  Thank you everybody.

  If you read my post, I told you one of the reasons why you'll get scans. Those functions on columns used in filtering are going to lead straight to that. Glad to hear the hint is helping, but it's not a good solution and will probably have to get readdressed.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning