Is there a way to find out what queries are run the most against a particular table? We have been experiencing issues with slowness regarding our Master Data Services application. So we have looked to see which tables have the highest value of range scans, singleton lookups, page lock waits, etc. Can you tell me if there is a way to see the most queries run against a particular table in our MDS database.
Is there a way to find out what queries are run the most against a particular table?


  T-SQL – Get a Historical Record of SQL Statements Hitting a Table
  
  This query checks the sys.dm_exec_query_stats system view and the
  sys.dm_exec_sql_text table valued function. 
  
  Also because these are system tables, you will need to be logged into
  SQL Server Management Studio (SSMS) with an account that has
  sufficient access permissions.
  
  Here is the query:

 SELECT
 qryStats.last_execution_time AS [Time]
 ,qryText.TEXT AS [Query]
 ,DB_NAME(qryText.[dbid]) AS [Database]
 ,OBJECT_NAME(qryText.[objectid]) AS [TableName]
 FROM sys.dm_exec_query_stats AS qryStats
 CROSS APPLY sys.dm_exec_sql_text(qryStats.sql_handle) AS qryText
 WHERE qryText.TEXT LIKE '%TableName%'
 ORDER BY qryStats.last_execution_time DESC

  
  Running this query will show you in reverse chronological order
  what SQL was run against a particular table on your database server.
  
  If you are using stored procedures then you’ll see that rather than
  showing the EXEC statement, SQL Server will have logged the CREATE
  statement for the stored procedure itself. For our purposes this is
  useful since it allows us to also find stored procedures that are
  having an effect on the table that we are interested in.
  
  The downside of this query is that it will not show you what user
  account is running each query.
  
  source