December 9, 2009 at 3:06 pm
I have a genuinely bizarre problem that I'm hoping for some insight on.
I have a report that takes a single integer parameter (WebTableID). Based on WebTableID, it runs a query gathering some information and producing a table report with some descriptions down the left, dates across the top and data in the middle.
When someone runs the report, it returns in 2-4 seconds and one of the date columns is out of order and pushed to the end. Looking at the server, there is a short 100% CPU spike.
When I run the report from my machine using the same parameter value against the same server my request takes 4 minutes to return and the dates are all in the correct order. Looking at the server, there is a 4 minute long 100% CPU spike.
There is nothing else running against that server while I am running my report and another user is running theirs (or at least nothing different). We tried running from my machine and another's, and got the same result.
While I love answering "It works fine on my machine", I'm not sure that will fly. 🙂 Plus fine doesn't account for the 4 minutes to run.
Thoughts would be appreciated, even if they are random ramblings of things you might check if this bit of randomness was happening to you.
Cheers
Michelle
December 10, 2009 at 8:14 am
Michelle,
There are a lot of variables involved here, as I'm sure you know. First, without seeing the SQL being called it can be difficult to troubleshoot. Here are couple of questions:
Is the hold up the Report Server or the SQL Server? You could check the SQL Server using Profiler/SQLTrace and/or system DMV's to see if the SQL Server is returning the data in a similar time and manner.
What is the data type of the data column? Is the sorting being done in the SQL or by the report?
Where is the data being pivoted, the report or the SQL? I'm assuming it is being pivoted because the dates are being used as column headers.
Does this report use snapshots?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 10, 2009 at 1:21 pm
Thanks Jack.
I actually tried running the report again to see what was actually throwing up the 100% CPU usage. What I got was the results back in 3 seconds with the data oddly sorted like the others were getting.
I <3 unrepeatable problems. 🙁 I'll see if it happens again, and have a deeper look at what processes are actually maxing out the CPU. The SQL Server is the Report Server.
The report contains a tablix where the pivoting happens. I thought it had the sorting in it, but clearly it was missing. I've corrected that now that I can replicate the problem from my machine.
The report does not use snapshots. The reason for it is that the data might change between runnings with users adding annotations, etc inside the application that calls the report.
Thanks again for thinking about it. If it happens again, I'll post anything further that I learn.
Cheers
Michelle
December 10, 2009 at 2:40 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply