question

Alex 1 avatar image
Alex 1 asked

Auditing script

I am looking for efficient script to capture a database activity. So far I am using dmv dm_exec_query_stats to get last executed statements (see the script below), but I am looking something more efficient, to get user and database name. Does someone have such as script?

SELECT SUM(query_stats.total_worker_time)/SUM(query_stats.execution_count)AS [Avg CPU Time], MIN(query_stats.statement_text)AS [Statement Text], MAX(last_execution_time) as Last_Execution_Time ,DB_NAME(MAX(deqp.DBID)) as DatabaseName

FROM(SELECT QS.total_worker_time, QS.execution_count, QS.last_execution_time, QS.plan_handle, QS.statement_start_offset, QS.statement_end_offset, SUBSTRING(ST.text, (QS.statement_start_offset/2)+ 1, ((CASE statement_end_offset WHEN-1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1)AS statement_text FROM sys.dm_exec_query_stats AS QS cross apply sys.dm_exec_sql_text(QS.sql_handle)as ST ) as query_stats CROSS APPLY sys.dm_exec_text_query_plan(query_stats.plan_handle, query_stats.statement_start_offset, query_stats.statement_end_offset) AS deqp WHERE LEFT(query_stats.statement_text, 6) IN ('select', 'insert', 'delete') GROUP BY query_stats.plan_handle
ORDER BY 1 DESC;

auditdmvdynamic-management-view
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered

First off, you know that as soon as a query leaves the cache, it won't be in the information you're pulling from, and, further, that some queries will never be available in the dm_exec_query_stats view?

The problem with trying to get the users is that this is an aggregation of calls based on the query. There's no way, from this data, to correlate X number of query calls to a particular set of users or an individual user. If you're looking to get individual calls to the database, trace events are still your best bet. However, you could run queries against sys.dm_exec_requests, but you'll miss calls and the aggregate runtime information is not available at all.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.