Is there a way to find out what queries are run the most against a particular table?

Question

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.

Answer 1

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