Finding most queried items in a table in SQL Server

Question

We have a SQL Server database which has table consisting of tickers. Something like

Ticker | description
-------+-------------
USDHY  | High yield 
USDIG  | Investment grade  ...

Now we have a lot of other tables which has data corresponding to these tickers (time series). We want to able to create a report which can show us which of these tickers are more queried for and which not not queried for at all. This can allow us to selectively run some procedures for the tickers which are more frequently used and ignore the others on a regular basis.

Is there some way to achieve this in SQL, any report which could generate this stat over a period of time say n-months.

Any help is much appreciated

Answer 1

Seems like no answers so far. As I mentioned, one possibility is to use Extended Events like below:

CREATE EVENT SESSION [TestTableSelectLog]
ON SERVER
ADD EVENT sqlserver.sp_statement_completed (
WHERE [statement] LIKE '%SELECT%TestTable%' --Capure all selects from TestTable
  AND [statement] NOT LIKE '%XEStore%' --filter extended event queries
  AND [statement] NOT LIKE '%fn_xe_file_target_read_file%'),
ADD EVENT sqlserver.sql_statement_completed (
WHERE [statement] LIKE '%SELECT%TestTable%'
  AND [statement] NOT LIKE '%XEStore%'
  AND [statement] NOT LIKE '%fn_xe_file_target_read_file%')
ADD TARGET package0.event_file (SET FILENAME=N'C:\Temp\TestTableSelectLog.xel');--log to file

ALTER EVENT SESSION [TestTableSelectLog] ON SERVER STATE=START;--start capture

You can then select from file using sys.fn_xe_file_target_read_file:

CREATE TABLE TestTable
(
    Ticker varchar(10),
    [Description] nvarchar(100)
)

SELECT * FROM TestTable

SELECT *, CAST(event_data AS XML) AS 'event_data_XML'  
FROM sys.fn_xe_file_target_read_file('C:\Temp\TestTableSelectLog*.xel', NULL, NULL, NULL)

The SELECT statement should be captured.

Extended Events can be also configured from GUI (Management/Extended Events/Sessions in Management Studio).