如何找出在SQL Server中最常访问或最常用的表 [英] How to find out which tables are most accessed or frequently used in SQL server

问题描述

我目前处于一种情况下，我需要找出哪些表经常使用或给定时间段内应用程序中的次数.可以说从开始日期到结束日期.

I am currently in a situation where I need to find out which tables are used frequently or the number of times in our application given a certain period of time. Lets say from some startDate to endDate.

我想在这里获得的详细信息是tableName及其被访问了多少次. 例如:-

The details I would like to get here are, the tableName and how many times it was accessed. Eg:-

tableName    No.Of Access
Table5        100
Table2         80
...
Tablen         n

如果上面的查询或者是否有其他方法可以检查这些表中的哪些列是经常使用的，那也很好.该练习背后的想法是正确索引大多数使用的表中的列.但是我不确定这是否也是进行优化的正确方法.因此，如果你们有更好的替代方法来确定如何对上述方法进行优化，那也将很棒.

Also if the above query or if there is any other way to check which columns in these tables are frequently used would be wonderful. The idea behind this exercise is to properly index the columns in most used tables. But I am not sure if this is the correct way to go about the optimization too. So if you guys have any better alternatives to identify how to optimize on the above, that would also be great.

我们正在使用SQL Server 2005，并且该应用程序在IIS托管的.net Framework 3.5上运行. 如果需要其他详细信息，请告诉我.

We are using SQL server 2005 and the application is running on .net framework 3.5 hosted on IIS. If any further details are required, let me know.

推荐答案

我唯一了解的这类信息是sys.dm_db_index_usage_stats

The only thing I'm aware of with anything like this type of information is sys.dm_db_index_usage_stats

事实证明，可以从中获得更可靠的信息

It turns out that more reliable information can be gained from

select * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL)

很好地涵盖了两个报告之间的区别

The difference between what the two report is covered well in this blog post.

这篇关于如何找出在SQL Server中最常访问或最常用的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！