Post reply

Update Statistics

  • December 1, 2010 at 3:03 am

    #141571

    On one of my servers update stat job is currently running. I am getting different result if i try to see the text of the batch running. for example, dbcc inputbuffer shows this(which is expected):

    dbcc inputbuffer(72)

    UPDATE STATISTICS [dbo].[T_nammishome01_home_FILES] WITH FULLSCAN

    I fetch the plan handle from dm_exec_requests and this returns no row for that plan

    select * from sys.dm_exec_sql_text(0x06002100700FC52A40031B81010000000000000000000000)

    -- returns no rows. this is plan handle

    I try to find text for sql_handle and i get this

    select * from sys.dm_exec_sql_text(0x02000000926EB7171D16224B5513F9DAC0340CACCE4C8B5C)

    SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [FI_ID] AS [SC0] FROM [dbo].[T_nammishome01_home_FileVersion] WITH (READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL

    Can someone explain these to me pls? What is surprising is the plan_handle doesnt return any text though it is present in the exec_request dmv.


    Pradeep Singh

  • December 1, 2010 at 5:16 am

    #1257250

    Or is it that since this is a maintainence statement (not a DML query), it didn't have a plan in the plan cache and hence no plan_handle?

    I've read that relation between sql_handle:plan_handle is 1:N. This also shows N can be 0(N>=0). 😎

    I am still looking for answer to the other question which is the difference between the output of DBCC inputbuffer and sql text of sql_handle. sql_handle is the hash of the SQL submitted so ideally it should give me the query which user/session submitted, however it seems it is giving something else.


    Pradeep Singh

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

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