Generate SQL Server CPU usage for last 7 days

Question

I have a requirement to generate CPU usage reports for my SQL server for previous 7 days. I will use a graph to represent it.

Also, I have to keep track of top 10 queries which consumed maximum CPU each day.

I got one post below but I have few doubts.

CPU utilization by database?

Doubt: How I will know that, how was the overall CPU usage yesterday? Do I have to add all the AvgCPU time for distinct queries ran yesterday?

Answer 1

There is no reliable way in Getting cpu usage per day/last 5 days..I see SQLServer has below columns..

select
creation_time,
last_worker_time,
total_worker_time,
execution_count,
last_execution_time
from sys.dm_exec_query_stats

And those reported below on my test instance..

As you can see from Screenshot above..

We can't reliably get ,count of instances a particular query got executed on a particular day..And moreover you will see this entire data gets reset if you restart SQLServer

If you really want to show data on daily basis,you could use perfmon..Here are some tutorials which may help you..

1.Collecting Performance Data into a SQL Server Table
2.Using PerfMon for SQL Server Reporting Services Performance Management