3

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?

Community
  • 1
  • 1
PiKa
  • 31
  • 1
  • 2

2 Answers2

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..

enter image description here

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

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • 1
    Perfmon is really the way to go. Be careful to set the time interval to something like every 30 seconds or 1 minute or you may have (if you don't turn it off) too much information. When you turn on Perfmon there are many, many counters for SQL Server - don't overcollect. – benjamin moskovits Sep 21 '16 at 14:54
0

You may also take a look on MS SQL Data collection sets. Easy to deploy, easy to keep necessary data (as long it stored on dedicated DB), and at least its really fits your requirements for top 10 CPU-expensive queries. You can also slightly modify t-sql for collector agent and target tables on collector server in order to obtain some extra CPU info if you need it.

Andrii Matus
  • 166
  • 4