Post reply

SELECT query is timing out due to SELECT STATMAN process

  • 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.

  • 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]

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply