Issue in Monitoring Table Scans by MDA table

[Former Member]

Hi,

Today our monitoring script send alert for table Scan by one Query  with Query Plan and SQL text . I checked it was  the plan manually it was table Scan as  Column in Join do  not have index ..

Then I try to run MDA  (monOpenObjectActivity) query to get list of table where table scan is happening for same table ..

Table Name : JDEStlmtLnExtractWork. -- (Datarows lock)  it has only one Unique Clustered index  on different set of column.

I run below Query .. with IndexID=0  and without IndexID=0 .

select "Database1" = db_name(DBID), "Table_Name" = convert(varchar(40),object_name(ObjectID, DBID)),

   IndID = IndexID, UsedCount, LastUsedDate,ROWCOUNT1=row_count(DBID,ObjectID) ,getdate()

from master..monOpenObjectActivity

Where UsedCount >0

--and IndexID=0

and convert(varchar(40),object_name(ObjectID, DBID))='JDEStlmtLnExtractWork'

I do not get any result for today's table scan with IndexID=0 

Database1                      Table_Name                               IndID       UsedCount   LastUsedDate              ROWCOUNT1           

------------------------------ ---------------------------------------- ----------- ----------- ------------------------- --------------------

ops                            JDEStlmtLnExtractWork                    0           1213451     2016-09-18 08:36:33.526   206642796           

ops                            JDEStlmtLnExtractWork                    2           597950      2016-10-04 15:20:02.823   206642796

But there was table Scan today on this table by two Query ..  Is This correct way to Get list of table with table scan (IndID=0)  from MDA --> Does not look .

Let me know if i am missing some in MDA query ..

Thanks

Ajay Pandey

[former_member89972]

Usedcount > 0 for IndexId = 0 is correct check for table scans.

Just to be sure :

force a manual table scan with Index 0 as hint.

And then check MDA  table monOpenObjectActivity from a different session if the table is big enough and query runs.

Otherwise follow the sequnce in a batch (you can also use a table that was not scanned today)

- select the same MDA query as above (before)

- seletct count(*) from the table with hint of index 0 i.e. table scan

- select the same MDA query as above (after)

At least you will know that MDA monitor mechanism is working  as expected.

HTH

Avinash

[Former Member]

In my case 'Usedcount > 0 for IndexId = 0 is correct check for table scans."

     is not showing correct result that is why i am raising this issue .  Please see below detail

1) I run the sql this morning which is  doing table scan . 9 AM

  SELECT  *

    FROM ops..JDEStlmtLnExtractWork J

   WHERE J.jDEObjSubsdryId = 15

   AND J.transpnModeId = 1 

  AND J.jDEGLDt  BETWEEN '2016-09-01 05:01:00.0' AND '2016-10-01 04:59:00'     

2) Query Plan :

   |   |

       |   |   |SCAN Operator (VA = 0)

       |   |   |  FROM TABLE

      |   |   |  ops..JDEStlmtLnExtractWork

       |   |   |  J

       |   |   |  Table Scan.

       |   |   |  Forward Scan.

       |   |   |  Positioning at start of table.

3) MDA query .. In  (IndexID with 0 -- Showing last used 18th Sept . But we have table scan today 9 AM

Which is shown as IndexId=2 as LastUsedDate  : 2016-10-05 09:00:25.786

So it looks IndexID=0 is not  correct SQL for table Scan .

select "Table" = convert(varchar(40),object_name(ObjectID, DBID)),

   IndexID, UsedCount, LastUsedDate

from .monOpenObjectActivity

Where UsedCount >0

and object_name(ObjectID, DBID)='JDEStlmtLnExtractWork'

go

Table                                    IndexID     UsedCount   LastUsedDate             

---------------------------------------- ----------- ----------- -------------------------

JDEStlmtLnExtractWork                    0           1256152    2016-09-18 08:36:33.526 

JDEStlmtLnExtractWork                   2           621017      2016-10-05 09:00:25.786

Thanks

Ajay Pandey

[former_member89972]

Is that for one specific table only ?  

Is the table APL by any chance ?

By running a manual table scan for some other table (e.g. created today) you can verify if monOpenObjectActivity is updating UsedCount and LastUsedDate correctly.

Also note that ObjetctName for IndexID > 0, is "index name" in monOpenObjectActivity for an index though the ObjectId is the Table's Id from sysobjects for that table.

Time permitting I will check used count increments for table scan on a DOL (DRL/DPL) tables and APL table with clustered index.

HTH

Avinash

[Former Member]

In original message ..

Table Name : JDEStlmtLnExtractWork. -- (Datarows lock)  it has only one Unique Clustered index  on different set of column.

sp_helpindex JDEStlmtLnExtractWork

index_name               index_keys                      index_description    index_max_rows_per_page index_fillfactor index_reservepagegap index_created       index_local 

------------------------ ------------------------------- -------------------- ----------------------- ---------------- -------------------- ------------------- ------------

XPKJDEStlmtLnExtractWork  jDEBatchId, stlmtId, stlmtLnId nonclustered, unique                       0                0                    0 Feb 16 2008 11:09PM Global Index

select Index_NAME=convert(varchar(40),name),convert(varchar(40),object_name(id)),indid

from sysindexes where object_name(id)= 'JDEStlmtLnExtractWork'

Index_NAME                                                                        indid 

---------------------------------------- ---------------------------------------- ------

JDEStlmtLnExtractWork                    JDEStlmtLnExtractWork                    0     

XPKJDEStlmtLnExtractWork                 JDEStlmtLnExtractWork                    2    

version : Adaptive Server Enterprise/16.0 SP02 PL02/EBF 25318 SMP/P/RS6000/AIX 7.1/ase160sp02plx/2492/64-bit/FBO/Sun Nov 22 18:44:08 2015

Last three day for this table :

Database1                      Table_Name                               IndID       UsedCount   LastUsedDate              ROWCOUNT1            timestamp                

------------------------------ ---------------------------------------- ----------- ----------- ------------------------- -------------------- -------------------------

ops                            JDEStlmtLnExtractWork                    0           872594      2016-09-18 08:36:33.526   206285250            2016-09-30 12:48:55.736  

ops                            JDEStlmtLnExtractWork                    0           1139741     2016-09-18 08:36:33.526   206568610            2016-10-04 08:39:28.683  

ops                            JDEStlmtLnExtractWork                    0           1259692     2016-09-18 08:36:33.526   206696492            2016-10-05 11:00:48.383 

[former_member89972]

Cool . So APL is ruled out.

In the last reply I do see that the UsedCount is going up for indexid = 0 (i.e. table scans for the table) Though the LastUsedDate seems stuck.

As I said create a simple test table and chek if the mechanism of updating the UsedCount and LastUsedDate is working as expected by forcing a table scan.

If the machnism is working for other table(s) then wait for your next server recycle !!

If recycle fixes it, then you have to let it go as  'one off' aberration and move on.

HTH

Avinash

PS : I tested this in my sandbox server. monOpenObjectActivity is behaving as expected. Details in attachment.

Outline of what I did

- created a test table DRL lockscheme

- populated it with some data

- queried MDA table

- created clustered unique index (indid 2)

- queried MDA table

- forced a table scan

- queried the MDA table

- verified that used count is up by 1 for IndexId = 0 entry for the table

monOpenObjectActivity and TableScan.txt.zip