The only thing I can think of is finding some what (maybe through a cron job) to record the 'Threads_connected' values every minute, and then calculate the max of these values.
I'm not sure if you can do exactly what you are asking for without resetting the 'Max_used_connections' before your monitoring period.
You can get this (and more) from Dynamic Management Views (DMVs). To get statistics for a particular stored procedure, try the following query.
SELECT
OBJECT_NAME(qt.objectid)
, qs.execution_count AS [Execution Count]
, qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
, qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
, qs.total_worker_time AS [TotalWorkerTime]
, qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
, qs.max_logical_reads
, qs.max_logical_writes
, qs.total_physical_reads
, DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
qt.[dbid] = DB_ID()
AND qt.objectid = OBJECT_ID('StoredProcedureName')
OPTION (RECOMPILE);
To look at the most frequently executed procedures:
SELECT
OBJECT_NAME(qt.objectid)
, qs.execution_count AS [Execution Count]
, qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
, qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
, qs.total_worker_time AS [TotalWorkerTime]
, qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
, qs.max_logical_reads
, qs.max_logical_writes
, qs.total_physical_reads
, DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
qt.[dbid] = DB_ID()
ORDER BY
qs.execution_count DESC
OPTION (RECOMPILE);
The values reported are cumulative since the last restart. If you want to measure over a fixed period, use the command below to reset wait stats.
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
If you wanted to measure fixed timespans across the day, you could feed the query output to a table via an agent job and either a) calculate the values between two runs or b) issue the wait stats reset as the last step in the agent job.
Alternatively, capture a profiler trace and run it through Clear Trace.
Best Answer
Its better to use Workload monitoring tools like
pgbadger
for checking database connections and overall load. It will help you understand which users are connecting for how much time and what queries are being fired by them. For information on installing and configuring pgbadger, refer this page.If you just want to check the number of active connections, you can use
select count(*) from pg_stat_activity where state='active'