How to figure out which procedure or function is causing the most logical reads on a particular table?



  • One of my tables only has about 5k rows, but is the table with the most logical reads, of all the tables in the database (based on dm_db_index_usage_stats). I'd like to work on the indexes to this table, but hundreds of objects use the table. Is there a shortcut to figure out WHICH of the many objects that use this table are the ones that are causing the most logical reads, specifically on this table? Perhaps some way of using QueryStore data for this?



  • To extract all the objects dependent on that table. This would be one approach, using dbatools powershell commandlet https://docs.dbatools.io/Get-DbaDependency .

    Example: Finding dependency of Person.Address in AdventureWorks2019 database.

    >> Get-DbaDbTable -SqlInstance localhost -Table Person.Address -Database AdventureWorks2019  |
    >> Get-DbaDependency | 
    >> select Dependent,Type,Parent,Object
    

    Dependent Type Parent Object


    vEmployee View Address [HumanResources].[vEmployee]
    vVendorWithAddresses View Address [Purchasing].[vVendorWithAddresses]
    BusinessEntityAddress Table Address [Person].[BusinessEntityAddress]
    vStoreWithAddresses View Address [Sales].[vStoreWithAddresses]
    vSalesPerson View Address [Sales].[vSalesPerson]
    vIndividualCustomer View Address [Sales].[vIndividualCustomer]
    SalesOrderHeader Table Address [Sales].[SalesOrderHeader]
    SalesOrderHeaderSalesReason Table SalesOrderHeader [Sales].[SalesOrderHeaderSalesReason]
    vVendorWithAddresses View BusinessEntityAddress [Purchasing].[vVendorWithAddresses]
    vIndividualCustomer View BusinessEntityAddress [Sales].[vIndividualCustomer]
    SalesOrderDetail Table SalesOrderHeader [Sales].[SalesOrderDetail]
    uSalesOrderHeader Trigger SalesOrderHeader [uSalesOrderHeader]
    vEmployee View BusinessEntityAddress [HumanResources].[vEmployee]
    vSalesPersonSalesByFiscalYears View SalesOrderHeader [Sales].[vSalesPersonSalesByFiscalYears]
    vStoreWithAddresses View BusinessEntityAddress [Sales].[vStoreWithAddresses]
    vSalesPerson View BusinessEntityAddress [Sales].[vSalesPerson]
    iduSalesOrderDetail Trigger SalesOrderDetail [iduSalesOrderDetail]

    Once you have the list of dependent objects,

    • For objects like views, procedures or functions you could configure XEvents session filtered on those objects to see the required details.
    • For any other tables referencing your concerned table, look if there are indexes defined on those referenced fields.



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2