Skip to Content
0
Former Member
Oct 04, 2016 at 08:26 PM

Issue in Monitoring Table Scans by MDA table

83 Views

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