0

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?

2
  • 2
    You should check out Brent Ozar's First Responder Kit - specifically sp_BlitzCache - it's a free set of scripted tools that you can use to diagnose which of your queries are the ones driving the high read counts. brentozar.com/blitzcache May 17, 2022 at 19:07
  • Thanks for this note. I'm not finding anything in sp_BlitzCache that would allow me to specify a particular table, rather it shows the number of logical reads on ALL tables, for a particular query. Am I missing something? I'm specifically looking to figure out which of hundreds of queries are the ones causing the most logical reads on particular table, so I can rework the indexes.
    – Sylvia
    May 18, 2022 at 22:26

1 Answer 1

0

To extract all the objects dependent on that table. This would be one approach, using dbatools powershell commandlet 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.