1

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:

  1. SQL Server Performance Dashboard Reports
  2. sp_whoisactive
  3. Activity top session report
  4. this query
  5. 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?

1

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.

  • Thanks for your answer. I am already using the sp_whoisactive procedure, the issue is a hundres of different queries are cacthed every seconds. I am sure there are a lot of problems with many of them, but my goal here is to detect what is the cost of all queries that are running on the reporting server only - if for example it is 2% of all, I can ignore them and focus on other ones. – gotqn Dec 11 '14 at 19:19

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.