Is there a way to found out what is the CPU usage of the reporting server? Sometimes the CPU usage is rised up by 40% percent for several seconds. I think the reporting server is causing it but I am not able to track it down.
I have try different techniques:
- SQL Server Performance Dashboard Reports
- sp_whoisactive
- Activity top session report
- this query
- looking at ExecutionLog3 View
The major problem is that the CPU usage value is raised from 20 to 60 percent for several seconds only. A lot of queries are executed and I am not able to identify which ones belongs to the reporting server.
Also, a bit of offtopic, but I have read that is a good practice to use the reporting server of the secondary databases when you have Always on Availability Group – is there any other licence requirements?
Best Answer
Regarding running reports from an Availability Group secondary server, you will need to license that instance of SQL Server. Since the AG requirements require identical equipment, your secondary's licensing cost will reflect the primary server's licensing cost
Using the secondary server strictly for High Availability/Disaster Recovery does not require licensing the secondary server since it is only in use when the primary fails.
Regarding the brief spikes in CPU utilization, is it causing you a problem now? Since it is hard for you to see who is responsible, consider loading sp_whoisactive from Adam Machanic's site: http://whoisactive.com This stored procedure has numerous options to control what information you can query.
Brent Ozar also has an article on using sp_whoisactive to track issues within your system, logging the output to a file for later review. http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/
Perhaps these tools will help you find out what is happening.