SQL: How can I get the number of executed queries per database or hour or ...?

Question

Is there any way to see how many queries are executed in like every hour, or per database / hour, or average number of queries on a day, or ...whatever is interesting?

Just for statistics.. I like numbers. I can't just start a trace with Sql Server Profiler, because the UI will crash when too many queries come by.

Does SQL keep track of some basic 'executed queries statistics' somewhere, or are there any tools I can use to get this information?

_{(I use SQL Server 2008 R2)}

Pretypify

sqlserverperformance.idera.com/tsql-optimization/… – Selfsealing

Nice post about index and column/table statistics, but doesn't say anything about number of executed queries – Pretypify

Answer 1

This should work:

select * 
from sys.dm_os_performance_counters
where counter_name = 'Batch Requests/sec'

It actually returns the total Batch Requests. You poll this number periodically and then use this calculation:

ReqsPerSec = (curr.Value - prev.Value) / (curr.time - prev.time)

Brachial

Nice, I didn't know about this counter yet. Do you know when this counter is reset? The counter has a value (about 224 million) but thats like 10 days ago. And this is a total of all databases. Can I also get this info 'per database'? – Pretypify

@Erik Dekker: The counter is reset when SQL Server starts-up. There's no equivalent per-database counter (AFAIK), but there may be some products/tools that can get that (through tracing and extensions). – Brachial

@Kees C. Bakker: Sad to say, but I haven't maintained my site in a very long time. So it really is disabled to everyone, even me. Sorry. :( – Brachial

Answer 2

I am actually just learning about this in my Microsoft Certification.

Though I can't answer your question directly yet, I can send you in the right direction with a couple things:

1. Have a look at the views inside the Server > Databases > System Databases > MSDB > Views > System Views. MSDN Systsem Views
2. Have a look at the views inside the Server > Databases > System Databases > Master > Views > System Views.
3. Take a peak at the trace tools available to SQL Server.

In the views note that you may actually have to join a couple of the views together or get at the underlying tables to get specifically what you are after.

Straightlaced