SQL table hit per second

Question

Hi Team,

Currently, I have installed sql server 2016 enterprise on my server. I want to know the query to check "What table name that hit or access so much per second in sql database?"

If you guy know about this, please kindly help.

Best regards,

Vuthy

Answer 1

I'm not sure that you want to know.

There is nothing built-in for this particular metric, but obviously you could run a trace or extended event session, and analyse the queries being executed. But it would create a bit of overhead. And it may not give the right number anyway, see below.

Moreover, I don't think this is a particularly meaningful metric. Let's say that you have two tables both the size of 1GB. One is being queried 100 times a second, but they are all point lookups on the clustered index key. The other one is only being accessed once a second, but that's a query that requires a full scan of the table. The latter table is taking much more resources from your server.

And what is an access. Say that you have a query that goes:

SELECT ...
FROM   A
JOIN    B ON A.col = B.col

and the query plan is a CI scan of A and a nested loops + CI seek against B. Say that there are 100 rows in A. Technically, that are 100 accesses to table B. Change the plan to be a hash join instead and it is only one access.

Answer 2

Hi @Vuthy M ,

We can using SQL server profiler trace or XEvent to trace this. But it seems that this value is not meaningful as Erland mentioned.

Could you share us the reason that why did you want to get this information? Then we may give you some suggestion.

Answer 3

Because I just want to know which table that hit the most.

The place where you want to look is sys.db_index_usage_stats. This DMV will not give you hits per second, but it will give the number of access since SQL Server started, and you get a distinction between seeks, scans lookups and writes. The data are per index and not per table, but you can aggregate per table if you wish.