February 26, 2013 at 8:24 pm
Is there a way to determine which tables of a database are being queried most often, sorted in descending order?
--Quote me
February 26, 2013 at 11:34 pm
U can get rough estimation from
dm_db_index_usage_stats or sys.dm_db_index_usage_stats
depends that you have indexed table
February 27, 2013 at 3:30 pm
googling dm_db_index_usage_stats or sys.dm_db_index_usage_stats. Thanks.
--Quote me
February 27, 2013 at 3:46 pm
something like this seems pretty close for a fast query:
SELECT
DB_NAME(database_id) AS TheDatabase,
OBJECT_NAME(object_id,database_id) As TheTableName,
SUM(user_seeks) AS user_seeks,
SUM(user_scans) AS user_scans,
SUM(user_lookups) AS user_lookups,
SUM(user_updates) AS user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id=db_id()
GROUP BY
DB_NAME(database_id),
OBJECT_NAME(object_id,database_id)
Lowell
February 27, 2013 at 10:37 pm
I was just coming back here to post that I'd found a query that retrieves last_user_seek, but no counters and Lo!
Holy Goodness, this is great. Thanks Lowell.
I do have a question about the query I found, though it's off topic. Why is UNION used in the CTE to correlate the exact same query 3x? When I remove the last UNION, the last_user_seek data is lost. A minimum of one UNION is needed and I have no idea why UNION is needed.
WITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
on so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)
--Quote me
February 27, 2013 at 11:06 pm
what is difference between
user_seeks
user_scans
user_lookups
user_updates?
--Quote me
February 28, 2013 at 5:20 am
The query you posted is using the same index stats to determine when the LAST time a table was accessed. you had asked for the most frequently used, which my query pretty much gets.
the query you posted uses three different columns to determine which has the highest date per object(hence the union to pull three columns into one)
the dmv we are using sys.dm_db_index_usage_stats has info since the last time the SQL service was started, so keep that in mind. If a table doesn't get access, it's notin the DMV until it does get queried/updated/deleted.
what is difference between
user_seeks
user_scans
user_lookups
user_updates?
that's how the indexes were used. roughly, an index seek (user_seeks) is where the index was used to resolve a WHERE statement like WHERE ID = 42, where the query was specific enough to look for specific values.
an index scan(user_scan) is where the query was not specific enough, so at a minimum a major portion of the index had to be scanned to find matching values..a LIKE statement, or no WHERE statemnet at all in a query could be examples.
user_lookups i'd have to lookup mysylf...i THINK it has to do with finding values in HEAP tables.
user_updates is the # of times an index was used to find data to update the table. gives you an idea for the table as to how often its read vs updated.
Lowell
March 3, 2013 at 11:29 am
Thank you Lowell, for your thorough answer.
Yes, I did recognize that your query provided counters, and that is what I was after.
And now I can see that the UNION statements are slightly different (last_user_lookup, last_user_scan, last_user_seek). Thanks. (I was afraid I was having trouble with set logic. Fortunately, that is not the case this time).
--Quote me
March 3, 2013 at 11:38 am
Just bear in mind that index_usage_stats only has info from the last time the database was started. If you have autoclose on or take the DB offline for any reason, or restart SQL, it will be empty afterwards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2013 at 12:02 pm
Yes, I think I read on a post elsewhere that if the servers go up and down often, might need to build an application to maintain counter numbers throughout the breaks....
--Quote me
March 5, 2013 at 11:49 am
Lowell, or Gila, or someone!, would you mind also telling me which system table I can join to the sys.dm_db_index_usage_stats table to get names of users or userids that are running the most queries?
I would like to be able to address the question:
"How frequently and by whom the database has been accessed".
thank you.
--Quote me
March 5, 2013 at 12:24 pm
I hate to pump up the urgency, but it is rather urgent.
Could someone tell me what is best way to find out: "How frequently and by whom a given database is being accessed".
There are many approaches. I'd like to expand the above query by Lowell to include users. Secondly, I'd like to know how others solve this problem if the servers go up and down.
....urgent:crazy:
--Quote me
March 5, 2013 at 12:46 pm
polkadot (3/5/2013)
Lowell, or Gila, or someone!, would you mind also telling me which system table I can join to the sys.dm_db_index_usage_stats table to get names of users or userids that are running the most queries?
None. The info in index usage is an aggregate, no user information is stored there in any way.
I would like to be able to address the question:
"How frequently and by whom the database has been accessed".
Have fun with that...
You need a 24/7 extended events session or profiler trace for that. Maybe SQLAudit would work too. Make sure you have the space to store all that info, make sure that the impact is acceptable and that you have the time to process and analyse that information.
Server access you can get tracing the logins, that's easy. Database level is harder. Table level....
I hate to pump up the urgency, but it is rather urgent.
....urgent :crazy:
Then hire someone to help you out. We're all volunteers here, we post in our spare time after we've done our jobs and dealt with all our urgent problems and crises. Bumping your post half an hour is completely unnecessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2013 at 12:57 pm
Thank you! I appreciate it very much.
--Quote me
March 5, 2013 at 1:05 pm
So user names are not logged in any system tables, right?
And profiler would have to be run as long as necessary (month?....I know, depends) to get a good idea of who is querying the database and when, correct?
And, since SQL Profiler is resource intensive, accomodations must be made, right?
Have you done this?
If this is not a common approach to solving the problem what are better approaches?
Would you kindly tell me which events you would capture in SQL Profiler?
--Quote me
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply