Report calling a Stored procedure taking too much time while

I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.

Please help--Iam stumped.

There are no infinite loops in the report. I checked.

I suggest you use the SQL Server Profiler to see what queries are sent to the database. Also, take a look at the ExecutionLog table in the ReportServer database to find how much time is spent in data retrieval.|||

All the time is spent in data retrievel while the same SP in management studio takes far less time ? I can't understand whats happening

|||

Also if i cancel the running jobs from Report Manager, they automatically restart after sometime? Any clues?

|||

Perhaps, debugging the stored procedure would help to find what's happening

|||

Sounds like you're running into issues with query plan; it is not uncommon to see this difference when running in Mgmt Studio vs. running in reports.

I recommend reading up on query performance:

http://msdn2.microsoft.com/en-us/library/ms187032.aspx

There are a couple of common problems people have with queries in stored procs (especially). These can often be fixed using the WITH RECOMPILE directive. This is especially true if the size of data your return from a test query is significantly different than that in the production query.

Secondly, if you reference other database objects in your query, using fully qualified names (dbo.storedProcName), you can also get some benefits.

Hope this helps,

-Lukasz