Hi i has a Report which pulls 900 records from a 5 million Data set. Actually i have tuned my Stored Procedure.While running the Sp against SQL SERVER Database it was taking only few sec(4) but when i run that Report which uses that SP it was taking almost 45 sec to show up the data on report manager. In this case how can i tune in Report Manger.Why the both times are different?

Hi - Is your stored procedure using parameters from the report? If so, then I had a similar experience. I'd run the stored proc and it would run in a few seconds when running via SSMS...but then the report would take forever (or very close!) to run. As it turned out the problem had to do with a poor/incorrect execution plan being used because of parameter sniffing. Try google (or bing) sql server stored procedure and parameter sniffing. Basically, what I had to do (and you might too) is to spoof the parameters in the stored proc. So, instead of this: Create Procedure MyProc @Param1 datetime , @Param2 nvarchar(50) AS Declare @MyParam1 datetime = @Param1 Declare @MyParam2 nvarchar(50) = @Param2 SELECT Column1, Column2 FROM dbo.Table1 t Where t.Column1 = @MyParam1 And t.Column2 = @MyParam2 I had to do this...and the results were great...back to excellent performance for the query: Create Procedure MyProc @Param1 datetime , @Param2 nvarchar(50) AS Declare @MyParam1 datetime = @Param1 Declare @MyParam2 nvarchar(50) = @Param2 SELECT Column1, Column2 FROM dbo.Table1 t Where t.Column1 = @MyParam1 And t.Column2 = @MyParam2 Hope this helps. - will - will

There is a new and long article by Erland Sommarskog that may help to troubleshoot this problem Slow in the Application, Fast in SSMS? Understanding Performance MysteriesFor every expert, there is an equal and opposite expert. - Becker's Law My blog

I have seen parameter sniffing cause issues in SSRS report performance on many occasions. So much so, that when I am writing an SP that I know is going to be used in an SSRS report I always declare local variables and use them in place of input parameter values. just fyi