I want to identify resource (CPU, memory and IO) usage by a database on a SQL Server 2005 and above server, over a week or a month. Can anyone please help in this?


There are several options you can use:

You can use Use Dynamic Management Views (DMVs), such as sys.dm_exec_sessions, sys.dm_os_performance_counters, sys.dm_os_memory_brokers, sys.dm_os_memory_nodes, sys.dm_exec_procedure_stats, sys._dm_os_sys_info, sys.dm_exec_requests, sys.dm_exec_requests, and many more, depending on what you actually want to monitor.

As SQL Server doesn't store the performance metric values in an archive table, you can query the views on a schedule and insert the results into a table that you will use as a repository.You cfan query and analyze the records easily then.

Another question is - what counters to monitor.

For processor usage: Processor : % Processor Time, Processor Queue Length

For memory, Available memory bytes, Total server memory, and Target server memory

You can find a complete list of counters recommended for monitoring here: Performance Monitor Counters

Note that when you use the dm_os_performance_counters view, it's essential to understand the counter_type. There are five different values, and the current value is calculated differently for each value type. You have examples and explanations here: sys.dm_os_performance_counters

Another option is to use a third party tool that collects the metrics you want to monitor, stores them in a repository and shows historic data for the time period you select. As the history data is stored in SQL tables, you can also easily query the data yourself and create reports.

Such a tool is ApexSQL Monitor, and it has built-in graphs for the last day, week, and month. It will have the reports available soon.

enter image description here

Disclaimer: I work for ApexSQL as a Support engineer


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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