A customer came to the SQL Server Customer lab to investigate a problem with one of the feature I’m responsible for – data-driven subscriptions.  In looking at their solution, we discussed any number of problems they had encountered.  I realized that it can be difficult to find out where to start looking when a problem occurs.  This post will hopefully provide you a starting point as you endeavor to fix the issues you run into.

 

Reports can take up

-          a lot of memory,

-          a lot of time to execute,

-          a lot of CPU

 

Generally speaking, it is possible for errors to occur as a result:

-          Out of Memory

-          Internal Errors

-          Rendering errors

 

This begs the question, “How to diagnose issues when running reports in the report server?”

 

General process to follow:

  • For Report Execution problems, start with the report server execution log
    • This log will tell you which reports are failing, who ran them, what parameters they used
    • It will also provide the time at which it failed and the server in your deployment on which the report failed
    • You can use the time and server to find the actual stack trace in the trace log files
  • Reference the trace logs based on timestamps ranges you find in the event log
  • You can find information on some of the error codes here:

Logging

-          Report Server exposes a number of log files, these include:

o   Information on report executions and whether they were successful and additional data related to the execution

o   Detailed error stacks that show what the problems were

o   Major events that occurred on your report server that you should be aware of are in the application event log in windows

-          You can read up on all of this here:

o   http://msdn2.microsoft.com/en-us/library/ms157403.aspx

 

Diagnosing processing and rendering problems – topic name “Processing Large Reports”

-          http://msdn2.microsoft.com/en-US/library/ms159638.aspx

 

Monitoring Performance

-          You will want to look at things like memory consumptions, application domain recycles, cpu usage, etc.  To isolate problems you may adjust concurrency for the scheduling service so you know exactly which report is currently running (instructions are below).

-     Application Domain recycles indicate the report server is under memory pressure.  We use them to clean out memory.  If your interactive report execution failed suddenly, and you're monitoring the performance counters for application domain recycles, you may see a correlation.  You should also see information in the trace log related to this.

-          This topic describes the performance counters

o   http://msdn2.microsoft.com/en-us/library/aa972240(SQL.80).aspx

-          Monitoring Report Execution Performance with Execution Logs

o   http://msdn2.microsoft.com/en-us/library/aa964131.aspx

 

Specific actions to help diagnose problems:

-          Adjusting Memory limits – see section “Report Size in Memory

o   If you’re seeing out of memory exceptions you can try increasing the memory used by report server

o   http://msdn2.microsoft.com/en-US/library/ms156002.aspx

 

-          Adjusting concurrency for Scheduling:

o   While trying to determine what is happening, you might reduce the number of simultaneous report executions

§  If you’re always running extremely large reports, setting this to 1 will allow you to use all of the memory for the report

o   In the file rsreportserver.config:

§  <MaxQueueThreads>0</MaxQueueThreads> determines concurrency for scheduling and delivery

§  “0” means the report server will determine the right number

 

Performance Whitepaper:

-          It is recommended to hosting report server and the report server database and data sources from which you get report data on different computers

-          http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx

 

How to configure a scale-out deployment:

-          Scale outs can increase throughput, reliability, and concurrency

-          http://msdn2.microsoft.com/en-us/library/ms159114.aspx

 

Monitoring and triggering subscriptions:

-          Sometimes making the report run on a schedule can help you isolate the performance issues (interactive report execution can lead to excessive load on your server)

·         See ‘how to trigger a subscription’; this works on SQL 2000 RS, and SQL 2005 RS

·         See ‘how to monitor a subscription’

 

Monitoring interactive report executions:

-          The ListJobs SOAP API allows you to see which long running reports are currently executing

-          http://msdn2.microsoft.com/en-gb/library/aa225969(SQL.80).aspX

 

If all else fails:

  • Use SQL Profiler to monitor the actions the report server is taking in the report server database
  • this is useful if you think the report server is not doing anything - you can watch the actual queries run through and watch report server respond to your actions 
  • generally speaking, it is possible to see everything in our various log files, so there should be no need to go to this level

If you have additional questions, feel free to leave me a  comment or post a question on my blog:

-          Lukasz’s Blog: http://blogs.msdn.com/lukaszp

 

Take care and good luck,

-Lukasz