We have a report that currently run on reporting services 2000. We are planning to move the same report to reporting services 2008 R2. However, I found that the processing time for the same report in rs2008 R2 is longer compare to rs2000. This is the record from execution log table from rs2000. TimeDataRetrieval TimeProcessing TimeRendering RowCount ByteCount 1004 7007 192 89772 92534 This is the record from execution log table from rs2008 R2. TimeDataRetrieval TimeProcessing TimeRendering RowCount ByteCount 1907 25305 50 89768 18443 Am I missing something in RS2008 R2? Thanks in advance for any reply.

Are there the same memory configuration settings for both sql servers 2000 and 2008 R2? Do you use reource governor at all in SQL Server 2008 R2?Sergei

Hi Sergei, Thank you for your reply. Memory settings are the same on both servers although SQL 2008 has more RAM assigned to it. No Resource Governor is not used. 4gb on sql 2000 and 6gb on sql 2008.

Hi Lim, Overall, SSRS 2008 R2 impoves more in processing performance than SSRS 2000. In your case, for retrieving data, check datasource connection network, for report processing, if the report is processed on SSRS 2008 R2 firstly, it might take much time, but if it is aways, i would suggest you enable report cache for fast reporting. See http://msdn.microsoft.com/en-us/library/ms155927.aspx for caching reports in reporting services. thanks, Jerry

Hi Jerry, Thanks for your reply. I have the same performance each time I ran the report. I cannot use the report cache processing option because the report has user profile dependencies. Is there any way I can improve the processing time? The report has only one group and no fancy formatting. It is using a stored procedure.

Hi Lim, Can you run t-sql below in SQL Server 2008 R2 and publish results here, please? SET SHOWPLAN_TEXT ON GO your proc @param GO SET SHOWPLAN_TEXT OFF GOSergei

Hi Sergei, The results are ************************************************************************************************************************************* exec [dbo].[RS_GetRegionalSales] 'WA' CREATE PROCEDURE [dbo].[RS_GetRegionalSales] ( @Zone varchar(100) ) AS BEGIN SELECT T.*, Z1.ZoneDesc AS SupplierZone FROM RS_SupplierSales_ByMZone T LEFT OUTER JOIN Region R1 ON R1.RegionID = T.SRegionID LEFT OUTER JOIN Zones Z1 ON Z1.ZoneID = R1.ZoneID WHERE (T.MemberZone = @Zone) |--Compute Scalar(DEFINE:([Expr1032]=CASE WHEN datediff(month,[Expr1047],[Expr1046])>=(12) THEN NULL ELSE [DataWarehouse].[dbo].[Members].[CommencementYear] as [M].[CommencementYear]+[DataWarehouse].[dbo].[Members].[CommencementMonth] as [M].[CommencementMonth] END, [Expr1033]=CASE WHEN datediff(month,[Expr1048],[Expr1046])<(12) THEN 'New' ELSE 'Existing' END, [Expr1034]=CASE WHEN datediff(month,[Expr1048],[Expr1046])<(0) THEN [DataWarehouse].[dbo].[rsPeriodStat].[Total] as [P].[Total]/($1.0000) ELSE CASE WHEN datediff(month,CONVERT_IMPLICIT(datetime,[DataWarehouse].[dbo].[Suppliers].[CommencedTradingDate] as [S].[CommencedTradingDate],0),CONVERT_IMPLICIT(datetime,[DataWarehouse].[dbo].[Control].[PeriodEndDate] as [C].[PeriodEndDate],0))>=(12) THEN [DataWarehouse].[dbo].[rsPeriodStat].[Total] as [P].[Total]/($12.0000) ELSE [DataWarehouse].[dbo].[rsPeriodStat].[Total] as [P].[Total]/CONVERT_IMPLICIT(money,datediff(month,CONVERT_IMPLICIT(datetime,[DataWarehouse].[dbo].[Suppliers].[CommencedTradingDate] as [S].[CommencedTradingDate],0),CONVERT_IMPLICIT(datetime,[DataWarehouse].[dbo].[Control].[PeriodEndDate] as [C].[PeriodEndDate],0))+(1),0) END END)) |--Parallelism(Gather Streams) |--Hash Match(Right Outer Join, HASH:([Z1].[ZoneID])=([R1].[ZoneID]), RESIDUAL:([DataWarehouse].[dbo].[Zones].[ZoneID] as [Z1].[ZoneID]=[DataWarehouse].[dbo].[Region].[ZoneID] as [R1].[ZoneID])) |--Parallelism(Distribute Streams, Broadcast Partitioning) | |--Clustered Index Scan(OBJECT:([DataWarehouse].[dbo].[Zones].[PK_Zones] AS [Z1])) |--Hash Match(Right Outer Join, HASH:([R1].[RegionID])=([S].[RegionID]), RESIDUAL:([DataWarehouse].[dbo].[Region].[RegionID] as [R1].[RegionID]=[DataWarehouse].[dbo].[Suppliers].[RegionID] as [S].[RegionID])) |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([R1].[RegionID])) | |--Clustered Index Scan(OBJECT:([DataWarehouse].[dbo].[Region].[PK_Region] AS [R1])) |--Compute Scalar(DEFINE:([Expr1048]=CONVERT_IMPLICIT(datetime,[DataWarehouse].[dbo].[Suppliers].[CommencedTradingDate] as [S].[CommencedTradingDate],0))) |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([S].[RegionID])) |--Merge Join(Right Outer Join, MERGE:([S].[OrganizationID])=([P].[SupplierOrganizationID]), RESIDUAL:([DataWarehouse].[dbo].[Suppliers].[OrganizationID] as [S].[OrganizationID]=[DataWarehouse].[dbo].[rsPeriodStat].[SupplierOrganizationID] as [P].[SupplierOrganizationID])) |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([S].[OrganizationID]), ORDER BY:([S].[OrganizationID] ASC)) | |--Clustered Index Scan(OBJECT:([DataWarehouse].[dbo].[Suppliers].[PK_Suppliers] AS [S]), ORDERED FORWARD) |--Sort(ORDER BY:([P].[SupplierOrganizationID] ASC)) |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([P].[SupplierOrganizationID])) |--Hash Match(Right Outer Join, HASH:([bt].[BusinessTypeID])=([sc].[BusinessTypeID]), RESIDUAL:([DataWarehouse].[dbo].[BusinessType].[BusinessTypeID] as [bt].[BusinessTypeID]=[DataWarehouse].[dbo].[SIC].[BusinessTypeID] as [sc].[BusinessTypeID])) |--Parallelism(Distribute Streams, Broadcast Partitioning) | |--Clustered Index Scan(OBJECT:([DataWarehouse].[dbo].[BusinessType].[PK_zzNew_BusinessType] AS [bt])) |--Hash Match(Right Outer Join, HASH:([sc].[SICCode])=([M].[SICCode]), RESIDUAL:([DataWarehouse].[dbo].[SIC].[SICCode] as [sc].[SICCode]=[DataWarehouse].[dbo].[Members].[SICCode] as [M].[SICCode])) |--Parallelism(Distribute Streams, Broadcast Partitioning) | |--Clustered Index Scan(OBJECT:([DataWarehouse].[dbo].[SIC].[PK_SIC] AS [sc])) |--Hash Match(Right Outer Join, HASH:([u].[UserID])=([R].[UserID]), RESIDUAL:([DataWarehouse].[dbo].[Users].[UserID] as [u].[UserID]=[DataWarehouse].[dbo].[Region].[UserID] as [R].[UserID])) |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([u].[UserID])) | |--Clustered Index Scan(OBJECT:([DataWarehouse].[dbo].[Users].[PK_Users] AS [u])) |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([R].[UserID])) |--Nested Loops(Inner Join) |--Hash Match(Inner Join, HASH:([M].[OrganizationID])=([P].[MemberOrganizationID]), RESIDUAL:([DataWarehouse].[dbo].[Members].[OrganizationID] as [M].[OrganizationID]=[DataWarehouse].[dbo].[rsPeriodStat].[MemberOrganizationID] as [P].[MemberOrganizationID])) | |--Bitmap(HASH:([M].[OrganizationID]), DEFINE:([Opt_Bitmap1052])) | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([M].[OrganizationID])) | | |--Hash Match(Inner Join, HASH:([R].[RegionID])=([M].[RegionID]), RESIDUAL:([DataWarehouse].[dbo].[Region].[RegionID] as [R].[RegionID]=[DataWarehouse].[dbo].[Members].[RegionID] as [M].[RegionID])) | | |--Bitmap(HASH:([R].[RegionID]), DEFINE:([Bitmap1054])) | | | |--Parallelism(Distribute Streams, Broadcast Partitioning) | | | |--Hash Match(Inner Join, HASH:([Z].[ZoneID])=([R].[ZoneID]), RESIDUAL:([DataWarehouse].[dbo].[Region].[ZoneID] as [R].[ZoneID]=[DataWarehouse].[dbo].[Zones].[ZoneID] as [Z].[ZoneID])) | | | |--Clustered Index Scan(OBJECT:([DataWarehouse].[dbo].[Zones].[PK_Zones] AS [Z]), WHERE:([DataWarehouse].[dbo].[Zones].[ZoneDesc] as [Z].[ZoneDesc]=[@Zone])) | | | |--Clustered Index Scan(OBJECT:([DataWarehouse].[dbo].[Region].[PK_Region] AS [R])) | | |--Compute Scalar(DEFINE:([Expr1047]=CONVERT_IMPLICIT(datetime,[DataWarehouse].[dbo].[Members].[CommencementDate] as [M].[CommencementDate],0))) | | |--Clustered Index Scan(OBJECT:([DataWarehouse].[dbo].[Members].[PK_Members] AS [M]), WHERE:([DataWarehouse].[dbo].[Members].[MemberID] as [M].[MemberID]<='999999' AND PROBE([Bitmap1054],[DataWarehouse].[dbo].[Members].[RegionID] as [M].[RegionID]))) | |--Compute Scalar(DEFINE:([Expr1035]=CASE WHEN CONVERT_IMPLICIT(numeric(19,4),[DataWarehouse].[dbo].[rsPeriodStat].[Total] as [P].[Total],0)<>(0.00) THEN (1) ELSE (0) END)) | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([P].[MemberOrganizationID])) | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1056]) OPTIMIZED WITH UNORDERED PREFETCH) | |--Compute Scalar(DEFINE:([Expr1055]=BmkToPage([Bmk1000]))) | | |--Index Scan(OBJECT:([DataWarehouse].[dbo].[rsPeriodStat].[IX_rsPeriodStat_MemberOrgID] AS [P]), WHERE:(PROBE([Opt_Bitmap1052],[DataWarehouse].[dbo].[rsPeriodStat].[MemberOrganizationID] as [P].[MemberOrganizationID]))) | |--RID Lookup(OBJECT:([DataWarehouse].[dbo].[rsPeriodStat] AS [P]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) |--Compute Scalar(DEFINE:([Expr1046]=CONVERT_IMPLICIT(datetime,[DataWarehouse].[dbo].[Control].[PeriodEndDate] as [C].[PeriodEndDate],0))) |--Table Scan(OBJECT:([DataWarehouse].[dbo].[Control] AS [C])) ************************************************************************************************************************************* Is this meaningful? Thanks again for your time. regards, Lim

Hi Lim, You have got a few of implicit conversions, for example CONVERT_IMPLICIT, for example PeriodEndDate. Not good. The best thisng is to add indexes on coulmns participating in JOINS, for example Z1.ZoneID = R1.ZoneID, R1.RegionID = T.SRegionID and in all underlying views. Also, add an index on RS_SupplierSales_ByMZone.MemberZone (in view on underlying table). Also, it does table scan on the table dbo.Control - Table Scan(OBJECT:([DataWarehouse].[dbo].[Control]. Run Database Engine Tuning Advisor to add missing indexes. Workload will be your [dbo].[RS_GetRegionalSales]. Sergei

Hi Sergei, Thanks for pointing that out. I will look at ways to implement indexed view. At the moment, only tables have indexes. RS_SupplierSales_ByMZone is a view. However, I am still concern about the processing time. Why it takes longer to process the same report in RS2008 R2? If I reduce the number of rows return, the processing time will be shorter.

Hi Lim, It could hardware related issues. Do you have the same RAID config on both servers? What about hard drive speed? Are there any processes that consume CPU on sql server r2 box?Sergei

Can I ask if the 2008 database was restored from a backup of the 2000 database? If it was, have you done the following: Made sure the "compatibility level" was changed to SQL Server 2008 (100). Rebuilt all indexes on the database. I experienced similar issues a few years ago when going from 2000 to 2005 and found that the above steps helped a lot. I found an article at the time that explained it, but cannot find it now. Craig Bryden - Please mark correct answers

Hi Sergei, They are both VM with two CPUs assigned. They basically have same hard drive speed. The only difference is the RAm assigned. 4GB on 2000 and 6GB on R2. SQL server R2 is a new box so no processes that will concume the CPU. Thanks.

Hi Craig, Thanks for your reply. Checked that the compatibility level is set to 2008. However, indexes are not rebuilt. How to rebuilt all indexes? Thanks.

Hi There are many scripts, but here is one I found with a quick google search. Looks like it is decent http://www.mssqltips.com/tip.asp?tip=1367 Craig Bryden - Please mark correct answers