May 30, 2019 at 3:42 pm
Hey All,
I wanted some help with a sql query that can return a list of db object with the number of executions/reads, which program is accessing it or user name.
Thanks for your continued support.
May 30, 2019 at 6:51 pm
Depending on what you are looking for, you can probably find at least some information on one or the other of your requests, but probably not both.
If the object in question is something currently running, you can find most of what you need in sys.dm_exec_requests:
SELECT s.session_id, s.host_name, s.program_name, s.login_name, s.status AS session_status,
DB_NAME(er.database_id) AS database_name,
er.status AS request_status, er.command, --er.percent_complete,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login,
er.wait_type, er.wait_time / 1000.0 AS wait_sec, er.wait_resource,
er.open_transaction_count, er.cpu_time / 1000.0 AS cpu_sec, er.total_elapsed_time / 1000.0 AS total_elapsed_sec, er.reads, er.writes, er.logical_reads,
OBJECT_NAME(st.objectid, st.dbid) AS object_name
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE s.is_user_process = 1
AND s.session_id <> @@SPID
ORDER BY s.session_id;
For tables/index recent usage, some information is temporarily held in sys.dm_db_index_usage_stats:
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, i.name AS index_name, i.type_desc, i.is_unique, i.is_primary_key, ps.size_MB, ps.used_page_count, d.name AS FileGroup,
iu.user_seeks, iu.user_scans, iu.user_lookups, iu.user_updates, iu.last_user_seek, iu.last_user_scan, iu.last_user_lookup, iu.last_user_update,
iu.system_seeks, iu.system_scans, iu.system_lookups, iu.system_updates, iu.last_system_seek, iu.last_system_scan, iu.last_system_lookup, iu.last_system_update
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.data_spaces d on i.data_space_id = d.data_space_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats iu ON i.object_id = iu.object_id AND i.index_id = iu.index_id AND iu.database_id = DB_ID()
LEFT OUTER JOIN (SELECT object_id, index_id, SUM(used_page_count) / 128 AS size_MB, SUM(used_page_count) AS used_page_count FROM sys.dm_db_partition_stats GROUP BY object_id, index_id) ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.name IS NOT NULL
AND i.object_id > 100
ORDER BY 1, t.name, i.name;
For stored procedures recent usage, some information is temporarily held in sys.dm_exec_procedure_stats:
SELECT
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS avg_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.total_worker_time / qs.execution_count / 1000000.0 AS avg_cpu_seconds,
qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
qs.total_logical_reads, qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_logical_writes, qs.total_logical_writes / qs.execution_count AS avg_logical_writes,
qs.execution_count, DB_NAME(qs.database_id) AS database_name,
OBJECT_SCHEMA_NAME(qs.object_id, qs.database_id) + N'.' + OBJECT_NAME(qs.object_id, qs.database_id) AS object_name, o.modify_date,
qs.last_execution_time, qs.cached_time, qp.query_plan, qs.sql_handle, qs.plan_handle
FROM sys.dm_exec_procedure_stats qs
LEFT OUTER JOIN sys.objects o ON qs.object_id = o.object_id
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE qs.database_id = DB_ID()
--AND qs.last_execution_time >= '2019-05-01'
AND qs.total_elapsed_time / qs.execution_count / 1000000.0 >= 0.009
ORDER BY qs.total_elapsed_time DESC;
May 30, 2019 at 9:49 pm
Hi Chris,
Thanks for your quick reply,
Is it possible to know how many queries are running against db tables? In other words, I wanted to know which tables are frequently used/queried by users and Programs in a certain period of time.
May 31, 2019 at 12:43 pm
sys.dm_db_index_usage_stats would be a good way to generally understand how data is accessed. It's not perfect. Here's a good article on it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply