May 14, 2010 at 9:10 am
Hello folks,
How do i find out the number of query executions against a particular database during a particular period of time(I really don't want to use SQL Profiler)
Thanks & Regards,
Sandeep
May 14, 2010 at 9:49 am
without using SQL Profiler, or creating a Server Side Trace, it is not really possible. a server side trace has minimal impact, and I consider it a best practice to have a dml trace in place for exactly this audit type of reasoning.
except for some caching in the DMV's, no data regarding specific queries, or at what time, is saved; the sole exception is DDL commands are saved as part of the default trace, but that just lets you know when people created/altered/dropped tables and other objects, and not DML commands which might select or change data within those objects.
some things are available in some of the data management views; for example i have this saved snippet that shows some of the last used objects:
use cvent_dba
go
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,2;
Lowell
May 14, 2010 at 10:00 am
i also have this saved which uses one of the DMV's to identify slow queries on the server:
use msdb
go
if not exists (select * from sys.schemas where name = 'MS_PerfDashboard')
exec('create schema MS_PerfDashboard')
go
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_QueryTextFromHandle'), 'IsTableFunction') = 1
drop function MS_PerfDashboard.fn_QueryTextFromHandle
go
CREATE function MS_PerfDashboard.fn_QueryTextFromHandle(@handle varbinary(64), @statement_start_offset int, @statement_end_offset int)
RETURNS @query_text TABLE (database_id smallint, object_id int, encrypted bit, query_text nvarchar(max))
begin
if @handle is not null
begin
declare @start int, @end int
declare @dbid smallint, @objectid int, @encrypted bit
declare @batch nvarchar(max), @query nvarchar(max)
-- statement_end_offset is zero prior to beginning query execution (e.g., compilation)
select
@start = isnull(@statement_start_offset, 0),
@end = case when @statement_end_offset is null or @statement_end_offset = 0 then -1
else @statement_end_offset
end
select @dbid = t.dbid,
@objectid = t.objectid,
@encrypted = t.encrypted,
@batch = t.text
from sys.dm_exec_sql_text(@handle) as t
select @query = case
when @encrypted = cast(1 as bit) then N'encrypted text'
else ltrim(substring(@batch, @start / 2 + 1, ((case when @end = -1 then datalength(@batch)
else @end end) - @start) / 2))
end
-- Found internal queries (e.g., CREATE INDEX) with end offset of original batch that is
-- greater than the length of the internal query and thus returns nothing if we don't do this
if datalength(@query) = 0
begin
select @query = @batch
end
insert into @query_text (database_id, object_id, encrypted, query_text)
values (@dbid, @objectid, @encrypted, @query)
end
return
end
go
GRANT SELECT ON MS_PerfDashboard.fn_QueryTextFromHandle TO public
go
use MASTER
GO
SELECT TOP 20 object_schema_name(qt.object_id, qt.database_id) + N'.' + object_name(qt.object_id, qt.database_id) AS 'SPName', qt.query_text , qs.total_worker_time AS 'TotalWorkerTime',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.execution_count AS 'ExecutionCount',
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'CallsPerSecond',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'AgeInCache'
FROM sys.dm_exec_query_stats AS qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
WHERE qt.database_id = db_id()
ORDER BY qs.total_worker_time DESC
Lowell
May 17, 2010 at 9:16 am
thanks for the quick reply
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply