1

I'm trying to find a way (maybe with sys DB) to retrieve information of all the queries executed per hour on the user databases. my client wants me to give him a daily report via SSRS of transaction per hour I would love to hear suggestions scanned the internet with no luck.

edit: im trying to understand where to get this information from its not about how to filter results per hour thanks

SQL_forever
  • 185
  • 1
  • 2
  • 10
  • [Something like this?](https://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) – shanji97 Jan 23 '18 at 13:08
  • Possible duplicate of [How to group time by hour or by 10 minutes](https://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) – shanji97 Jan 23 '18 at 13:08
  • hi no.. i want to know how many transactions \queries per hour are being executed on my databases.its not about how to group by hour its about where to take the info from. thanks for the reply – SQL_forever Jan 23 '18 at 13:21
  • You might have more luck on [Stack DBA](https://dba.stackexchange.com/help/on-topic). If you want to move the question, click *close* >> *off-top because* >> *This question belongs on another site in the Stack Exchange network* >> *Stack DBA*. – David Rushton Jan 23 '18 at 13:23
  • Hi! I am sorry, must have been a misunderstanding. But the general idea is to have a table multiple colums with date, hour, minutes,...,query_count Then every hh:00, take the count of rows where hour=hh-1, and save the records into another table. All this shoud be in a TVF, UDF or a SP, where the main query is executed. – shanji97 Jan 23 '18 at 13:28
  • im sorry i dont understand what youre saying. i want to collect all the queries that are running on that instance and then count them per hour. how do i log them to a table? – SQL_forever Jan 23 '18 at 13:31
  • from which table do i take that info? – SQL_forever Jan 23 '18 at 13:32

3 Answers3

1

You could always have a look through the following tables:

master.sys.dm_exec_sessions
master.sys.dm_exec_connections
master.sys.dm_exec_sql_text
shawkins1
  • 113
  • 8
  • These are not tables but DMVs but can you explain how can one use them to get transactions per hour? The first two gives you the ACTUAL state, what we have now, the third gives you the text of query for given plan handle, but where is statistical data? – sepupic Jan 23 '18 at 13:47
  • you mean maybe creating a new table and sort of loging the connections from this to the other table? cause as the person above me said this are all for current sessions – SQL_forever Jan 23 '18 at 14:16
  • Initially I was just suggesting this to be able to view current queries, you could log the data by Hour etc and create a statistical table for an SSRS report. – shawkins1 Jan 23 '18 at 14:38
1

i ended up finding this(with a few little changes)

thanks everyone for your help this seems to work fine

create table #execution_count 
(time  datetime,
onhour int,
query nvarchar(max),
dbid int,
objectid int,
number int,
encrypted int,
text nvarchar(max)

)

insert into #execution_count 
SELECT deqs.last_execution_time AS [Time], (datepart(hour, deqs.last_execution_time)) as onhour, dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where deqs.last_execution_time > getdate()-1 --and cast(deqs.last_execution_time  as datetime) between '%19:00:00%' and '%2018-01-16 21:00:00%'
and dest.dbid not in(1,2,3,4)
ORDER BY deqs.last_execution_time DESC


select onhour, count(*) as total_executes_per_hour  from #execution_count 
group by onhour
having count(*)>0
drop table #execution_count
SQL_forever
  • 185
  • 1
  • 2
  • 10
0

You can use Perfmon counters Transactions/sec and Batch Requests/sec or the same counters from DMV sys.dm_os_performance_counters.

You can read more on the first counter here: ransactions/sec performance counter

But please take a look at this video too: he Transactions/sec counter has big blind spots because may be you should opt for Batch Requests/sec.

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • hi im aware of the way via perfmon and extended events but as i said i need this for an ssrs report... so i need something that will run pretty often on the server.thanks for the suggestion – SQL_forever Jan 23 '18 at 14:17
  • Make a job that will run every 5 minutes (1 minute, as often as you want) and collect these counter values to a table. Make a report over this table – sepupic Jan 23 '18 at 14:22
  • asking (cause i never tried)- wouldnt it have an impact on the performance of the server? – SQL_forever Jan 23 '18 at 14:51
  • We have such a job enabled for every 5 minutes, and it gather many more counters. Yes of course it's not gratis but there is no way to get periodic information and to have no impact – sepupic Jan 23 '18 at 15:04
  • yeah im just afraid because this client's server is really critical and running perfom every min sounds like it can cause a problem. if it wasnt this client iwould probably do that as well – SQL_forever Jan 23 '18 at 15:17
  • You can schedule it to run once per hour then – sepupic Jan 23 '18 at 15:28
  • but then it wouldnt be accurate i need all of the queries from the entire day – SQL_forever Jan 23 '18 at 15:29
  • Make it as frequently as you want and if later they will say that the server became slow you'll explain them that they need to choose: or to have accurate data or have no performance impact – sepupic Jan 23 '18 at 15:33