Most hits on tables

Question

Most hits on tables

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
August 20, 2008 at 11:24 am

#127115

Is there a way to know in SQL Server the most hits on table with using SQL Profiler. Is this statistics already store in the system tables.
I need to know which tables have most hits in sql server.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You could use sys.dm_db_index_usage_stats to tell. You would have to do some aggregation of columns and rows, but it looks like it would work.

This is probably better than trying to get this from Profiler.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

Thanks for your reply but this does not work on SQL 2000. Sorry, I did not mentioned ver.

Is there a similar command on sql 2000 server.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Not that I am aware of.

BTW-if your question pertains to SQL Server 2000 you should have posted it in a SQL Server 2000 forum.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lowell SSC Guru Points: 323518 More actions · Answer 4

aww too bad your on 2000; I found Jack's suggestion really interesting and slapped this together real quick and it gave me promising results:

[font="Courier New"]

SELECT

DB_NAME(database_id) AS dbname,

OBJECT_NAME(OBJECT_ID,database_id) AS tablename,

SUM(user_seeks) AS userseeks,

SUM(user_scans) AS userseeks,

SUM(user_lookups) AS userseeks,

SUM(user_updates) AS userseeks

FROM sys.dm_db_index_usage_stats

WHERE database_id > 4

AND LEFT(OBJECT_NAME(OBJECT_ID,database_id),3) <> 'sys'

GROUP BY DB_NAME(database_id),OBJECT_NAME(OBJECT_ID,database_id)[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!