Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Been troubleshooting an issue for a couple weeks now, where a couple reports are running really slowly. These aren't terribly complex reports, they just grab data and do a little aggregation/sorting and display it. No charts or anything. Prior to last Monday both reports ran just fine. There are still some reports that run quickly, but they deal with very small amounts of data and even less processing.

I've verified that parameter sniffing isn't the problem, the report itself works fine both in visual studio and on a test reporting server (even when pointing to the production database.)

So I think it's a problem with the server itself. The sys admin has ruled out any "hardware" issues, with hardware in quotes because it's a VM. The server has plenty of memory as well. We even tried disabling anti-virus.

Here are the times listed from the ExecutionLog table. You can see that the data retrieval is pretty close for both servers but the rendering time is way higher in production.

Server      TimeDataRetrieval   TimeProcessing  TimeRendering
production  114353               4075           189073 
test        140716               3295           1101

So any ideas why the report is taking so long to render on the production server?

share|improve this question
    
Have you checked index fragmentation and stats on the tables involved in the report ? –  Kin Nov 1 '13 at 20:59
    
Yes, but the queries are as fast as they are going to get. I'm worried about the TimeRendering. It shouldn't be 100 times slower on the production server... –  Telos Nov 1 '13 at 21:26
    
Try checking wait-stats for the server. If something is that different between the two systems, my first guess is either locks or I/O issues (slower disks or something). –  Allan S. Hansen Nov 4 '13 at 12:42
    
Checked, I see a little time with database locking for SSRS's database, but that's about it. It's a VM so one of the first things we tried was moving it to our "fastest storage." I got it to be a little faster by setting report snapshots to 1, improving TimeRendering by about 2 minutes. Still taking 4 minutes longer than it should be though... –  Telos Nov 4 '13 at 17:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.