June 29, 2011 at 4:26 am
Hi All,
One of our reporting server query is running very slow, it takes more than one hour to execute,returning just around 6000 rows. One thing which i have noticed is that it first returns 300 rows of data then to process the next row it again takes a long time and it continues. Seems like blocking but there is no DELAY statement in the query. The same query executes in just 1 minute on the OLTP production environment.
I have checked all the indexes and statistics on all the tables in the database and found that they are exactly the same as that of the OLTP server.
Can anyone please guide me on what might be going wrong.
With Thanks,
Satnam
June 29, 2011 at 4:56 am
There's some information that you have to share in order to have a chance to receive a sensible answer.
Take a look at this article and post the information required:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Regards,
Gianluca
-- Gianluca Sartori
June 29, 2011 at 6:22 am
It could be resource contention, blocking, statistics... hard to know without more information. First, check for blocking. Run a quick query against sys.dm_exec_requests. That will tell you if your process is blocked and which process is blocking it. If that's the issue, you'll need to drill down in that area. If not, get the execution plan and compare it to the one on your OLTP system. Especially pay attention to the properties of the SELECT statement. They'll have a compile time value for the parameters, if any, and you want to compare those to the OLTP system and then look at the data to see if it's terribly skewed. You can try updating the statistics.
If it's none of that, we have to start drilling down on what the query is doing and for that we need the query, the structures, execution plans (actual, not estimated), that sort of thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 29, 2011 at 6:37 am
This assumes that you are keeping on log on the SSRS server. This query can give you great pointers about where you are wasting most of your time.
... in the reportserver DB >
SELECT TOP 100
C.[Name]
, C.Path
, L.UserName
, L.Parameters
, L.Status
, L.ByteCount
, L.[RowCount]
, L.TimeDataRetrieval
, L.TimeProcessing
, L.TimeRendering
, DATEDIFF(ms , L.TimeStart , L.TimeEnd) AS FullExec_MS
-- , *
FROM
dbo.ExecutionLog L
INNER JOIN dbo.[Catalog] C
ON L.ReportID = C.ItemID
WHERE C.Name = 'Marges - détails'
-- AND C.[Path] = 'Whatever'
ORDER BY L.TimeEnd DESC
Usually the sum of the first 3 columns should be pretty darn close to the total time it took to render the report (datediff).
So if it takes 5 seconds for the first 3 steps and 1 hour total, you're waiting on a ressource. If not, then you know you have a problem with the query or the complexity of the report. It's quite commun to have prolems in both, but I'll let you check it out for your own machine(s).
If your history logging is not activated open SSMS, connect to server type Reporting Services, then your SSRS servername
Right click on the server node, properties. Then it's the 4th option for me (in french so I can't give you the real name). Check the 1st option an uncheck the 2nd (my server logs about 1 GB / year so I just let it run for now).
June 29, 2011 at 6:42 am
One strange thing i have just observed. In the entire block of code in one section there are two conditions seperated by AND operator, if i comment any one of them then the entire query executes in 10 seconds.
Sounds pretty strange
Any guess.
June 29, 2011 at 6:47 am
Satnam Singh (6/29/2011)
One strange thing i have just observed. In the entire block of code in one section there are two conditions seperated by AND operator, if i comment any one of them then the entire query executes in 10 seconds.Sounds pretty strange
Any guess.
It could lead to a totally different plan. No oddness at all.
-- Gianluca Sartori
June 29, 2011 at 6:47 am
Satnam Singh (6/29/2011)
One strange thing i have just observed. In the entire block of code in one section there are two conditions seperated by AND operator, if i comment any one of them then the entire query executes in 10 seconds.Sounds pretty strange
Any guess.
Please post the actual execution plans for all 2-3 tests you just did.
When was the last time you defragmented the indexes and rebuilt the stats on those tables?
June 29, 2011 at 6:52 am
I will do post it. I was wondering it might be due to some constraints been missing on the tables.
June 29, 2011 at 6:56 am
Satnam Singh (6/29/2011)
I will do post it. I was wondering it might be due to some constraints been missing on the tables.
That would potentially affect data quality, and very rarely performance. But that's way down the list of slow running query issues.
June 29, 2011 at 8:09 am
Ninja's_RGR'us (6/29/2011)
Satnam Singh (6/29/2011)
I will do post it. I was wondering it might be due to some constraints been missing on the tables.That would potentially affect data quality, and very rarely performance. But that's way down the list of slow running query issues.
Not completely true. Constraints are considered[/url] by the optimizer and they will affect performance in a positive way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 29, 2011 at 8:13 am
That's why I said very rarely and not never :w00t:!.
I didn't feel like going down that rabit hole ATM... nor searching again for that link :w00t:.
More to the point I see constraint more has business requirement / data quality than a perf. tool. Tho I agree it has tons of benefits.
But then again I won't create a constraint that doesn't fit the business just in case it helps perf.!
June 29, 2011 at 8:25 am
Ninja's_RGR'us (6/29/2011)
That's why I said very rarely and not never :w00t:!.I didn't feel like going down that rabit hole ATM... nor searching again for that link :w00t:.
More to the point I see constraint more has business requirement / data quality than a perf. tool. Tho I agree it has tons of benefits.
But then again I won't create a constraint that doesn't fit the business just in case it helps perf.!
Oh yea! Agreed, 100%. Constraints are for the business definitions, absolutely.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 30, 2011 at 8:33 am
I have fixed this issue. It was because of some useful missing statistics in one of the huge tables and now the report executes successfully in 5 min and 12 seconds instead of the earlier 1 hr and 42 minutes.
Thank you all for your time and suggestions.
With Thanks,
Satnam
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply