How many general requests the servers receives per day and per hour on average?
Hi 1)Is it possible to get analysis to understand how many general requests the servers receives per day and per hour on average? 2)How many requests are hitting the machine? if any one knows help me............give the script or DMV or article plz.........
it works for me. Is it correct? DECLARE @BRPS BIGINT SELECT @BRPS=cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Batch Requests/sec%' WAITFOR DELAY '000:00:01'---"per sec" SELECT (cntr_value-@BRPS)/10.0 AS "Batch Requests/sec" FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Batch Requests/sec%' select output of the above value i.e per sec *3600 as "per hour" select output of the above value i.e per hour*24 as "per day"
The very best way to do this is to use extended events where you can capture each remote procedure call and sql batch. You can then accumulate exactly how many calls have been placed against your server. [This is the introduction in the SQL Server Books Online]. If you are not using SQL Server 2008 or better (2005 or 2000), you'll have to use a server-side trace to capture the same information. [Gail Shaw's article on Simple-Talk covers this]. The second choice if you are running 2005 or better is to use the Dynamic Management Object, [sys.dm_exec_query_stats], which shows you the aggregate of all calls against queries that are currently in the plan cache in memory. But, as plans age out of cache due to memory pressure, or plans that recompile, or plans with the option to recompile, their statistics go away. This makes this a very spotty method for getting this done. However, it will be a reasonable measure for how many calls you have in a given day, just not at all perfect. It won't correlate to individual users though. You can also use [performance monitor counters]. There is one for SQL Statistics:Batch Requests/sec that will give you a good measure of load. You can also capture Transactions:Transactions to see the number going by. The only reason I don't list these first is because you can't correlate any individual call to a particular query or an individual user. :