SELECT query is timing out due to SELECT STATMAN process

• Chinesinho

  Old Hand

  Points: 374

  More actions

  October 11, 2010 at 4:28 pm

  #229478

  So I'm running a simple SELECT statement on a table that is ~44 GB in data size. However, the query just churns & churns until it times out on the application side. When I run it in SSMS, it just runs forever without completion. The query itself is just a SELECT TOP 1 <list of columns> from <TableName> with(nolock) where <c1> = <c1_value> and <c2> = <c2_value> so I know it shouldn't take that long. After checking sysprocesses, I was able to find the following query when the SELECT statement is issued:

  SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT <c1]> AS [SC0] FROM [dbo].<TableName> TABLESAMPLE SYSTEM (1.791753e+001 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

  I know STATMAN has something to do with auto-update stats but I've checked all the indexes on this table, and they have 'auto-recompute statistics' unchecked. Has anyone ever come across such an issue? Any insight is appreciated. Thanks in advance.

• @SQLFRNDZ

  SSCrazy Eights

  Points: 9095

  More actions

  October 11, 2010 at 5:32 pm

  #1235268

  If you are selecting a column column and it have an index then you should use the same column in the where clause. then only it uses the index.

  [font="Tahoma"]
  --SQLFRNDZ[/url]
  [/font]

• Chinesinho

  Old Hand

  Points: 374

  More actions

  October 11, 2010 at 8:46 pm

  #1235288

  @pavan_srirangam The query is using the correct index as I've already verified that by viewing the execution plan.