Update Statistics

• ps.

  One Orange Chip

  Points: 29423

  More actions

  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

• ps.

  One Orange Chip

  Points: 29423

  More actions

  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