November 6, 2019 at 7:54 pm
Since the last server restart or any point of time.
Without any auditing or anything like that set up. Can DMVs be used to create such query?
Thanks.
Likes to play Chess
November 7, 2019 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 13, 2019 at 7:40 pm
I have this query I run when I am trying to capture long running queries. You can modify it and capture all the select statement.
Create procedure [dbo].[spSelectQueries]
as
INSERT INTO 'TableName'
(SPID, DBName, HostName, LoginTime, LoginName, Running_time, SQL_Text)
SELECT
SPID,
DB_NAME(SP.DBID) AS DBNAME,
HOSTNAME,LOGIN_TIME,LOGINAME,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
EST.text
FROM master.sys.sysprocesses SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
WHERE start_time <= DATEADD(MINUTE, -5, GETDATE())
AND text like '%SELECT%'
AND DB_NAME(SP.DBID) = 'Your DB Name'
ORDER BY CPU DESC
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
November 14, 2019 at 3:10 pm
there is no SQL_TEXT column in either master.sys.sysprocesses or sys.dm_exec_requests..
cannot run this query.
Likes to play Chess
November 14, 2019 at 3:20 pm
Run this query and tell me what you get?
SELECT
SPID,
DB_NAME(SP.DBID) AS DBNAME,
HOSTNAME,LOGIN_TIME,LOGINAME,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
EST.text
FROM master.sys.sysprocesses SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
WHERE start_time <= DATEADD(MINUTE, -5, GETDATE())
AND EST.text like '%SELECT%'
AND DB_NAME(SP.DBID) = 'Your DB Name'
ORDER BY CPU DESC
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
November 14, 2019 at 5:24 pm
no results at all. I tried in 3 dfferent servers. 0 result. while there are bunch of users logged in , running a lot of Select statements...
Likes to play Chess
November 14, 2019 at 7:46 pm
Syed's query will only return items that have been running longer than 5 minutes, not sure from the original question if this is what you were looking for or not.
SQL Server's DMVs track usage at an INDEX level, not really at the TABLE level. You can use sys.dm_db_index_usage_stats to find out how many seeks and scans are performed, so again this isn't really at the statement level, as one statement can have many seeks. Here's a query I use to look at index usage:
SELECT s.name + N'.' + t.name AS table_name, i.name AS index_name, i.type_desc, i.is_unique, i.is_primary_key,
SubString(
(SELECT N', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
ORDER BY ic.key_ordinal FOR XML PATH('')), 3, 1000) AS columns,
SubString(
(SELECT N', ' + c.name
FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
ORDER BY ic.index_column_id FOR XML PATH('')), 3, 1000) AS included,
i.filter_definition, iu.*
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s on t.schema_id = s.schema_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()
WHERE i.name IS NOT NULL
AND i.object_id > 100
AND t.name in ('Agent','Person')
ORDER BY s.name, t.name, i.name;
November 14, 2019 at 7:47 pm
i think the right thing to do is is go straight to sys.dm_db_index_usage_stats, which keeps track of how many times an index was used for seeks/scans/ lookup/updates.
the data is only since the last SQL Service restart, but I think this gives you what you are looking for.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
;WITH ServerStarted AS
(
SELECT
MIN(last_user_seek) AS first_seek,
MIN(last_user_scan) AS first_scan,
MIN(last_user_lookup) AS first_lookup
FROM sys.dm_db_index_usage_stats
),
ServerFirst AS
(
SELECT
CASE
WHEN first_seek < first_scan AND first_seek < first_lookup
THEN first_seek
WHEN first_scan < first_seek AND first_scan < first_lookup
THEN first_scan
ELSE first_lookup
END AS usage_start_date
FROM ServerStarted
)
SELECT
MIN(ServerFirst.usage_start_date) AS ServerLastRestarted,
DB_NAME(statz.database_id) AS DatabaseName,
OBJECT_SCHEMA_NAME(statz.object_id,statz.database_id) AS SchemaName,
OBJECT_NAME(statz.object_id,statz.database_id) AS ObjectName,
SUM(statz.[user_seeks]) + SUM(statz.[user_scans]) + SUM(statz.[user_lookups]) AS TotalSelects
FROM sys.dm_db_index_usage_stats statz
CROSS JOIN ServerFirst
GROUP BY database_id,statz.object_id
ORDER BY database_id,TotalSelects DESC
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply