3

We have a number of database which all have the same schema for each customer. I was wondering if there was a query which could select count(*),db_name from somesysview group by db_name;

From a different angle, the same query broken down by user?

3 Answers 3

5

You can use sys.dm_exec_sessions. This will only give you the count based on the current activity. You would need to store this historically if you wanted to trend it. Keep in mind that session ids of 50 or less are system sessions.

--By Database
select 
    db_name(database_id) DatabaseName
    ,count(session_id) as Session_count 
from sys.dm_exec_sessions
where
    session_id > 50
group by
    database_id

--By Login
select 
    login_name
    ,count(session_id) as Session_count 
from sys.dm_exec_sessions
where
    session_id > 50
group by
    login_name
0
2

I would use an Extended Events session and a histogram target for this:

CREATE EVENT SESSION [count_queries] ON SERVER 
ADD EVENT sqlserver.sql_statement_starting (
        ACTION(sqlserver.database_name)
    ) 
    ADD TARGET package0.histogram (
        SET filtering_event_name = N'sqlserver.sql_statement_starting'
            ,source = N'sqlserver.database_name'
    )
    WITH (
            MAX_MEMORY = 4096 KB
            ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
            ,MAX_DISPATCH_LATENCY = 30 SECONDS
            ,MAX_EVENT_SIZE = 0 KB
            ,MEMORY_PARTITION_MODE = NONE
            ,TRACK_CAUSALITY = OFF
            ,STARTUP_STATE = OFF
    )
GO

What you will get is a count of queries (statements starting events) grouped by database_name. Keep it running for a significant amount of time and you will get realistic figures of database usage.

Depending on the behavior of the application, an existing session is not enough to tell how much the database is used: some applications close the connection to the database after every interaction, some others keep it open all the time (connection pooling is a good example). Hence, seeing an idle open session to the database is not a good indication of the amount of activity going on.

1

You could also query the plan cache to get an idea of query activity in different databases.

SELECT DB_NAME(CAST(epa.value AS INT)) AS DatabaseName,
       SUM(qs.execution_count)         AS execution_count,
       SUM(qs.total_worker_time)       AS total_worker_time,
       SUM(qs.total_physical_reads)    AS total_physical_reads,
       SUM(qs.total_logical_writes)    AS total_logical_writes,
       SUM(qs.total_logical_reads)     AS total_logical_reads,
       SUM(qs.total_clr_time)          AS total_clr_time,
       SUM(qs.total_elapsed_time)      AS total_elapsed_time,
       SUM(qs.total_rows)              AS total_rows
FROM   sys.dm_exec_query_stats qs
       CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE  epa.attribute = 'dbid'
GROUP  BY epa.value
ORDER  BY total_worker_time DESC 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.