Post reply

Data driven subscription with stored procedure bringing server down

  • May 12, 2010 at 1:08 pm

    #232587

    I have created a report in SSRS that uses 2 stored procedures to populate a table that I use in the report. This report takes a parameter.

    I have tested this report and it runs fine and produces results beautifully.

    Usually this report populates about 300K rows in this table which I truncate at the beginning of each run.

    Now I created a data driven subscription for this report. The DDS query returns about 125 rows for the report, that means that this report is being sent to 125 people. This subscription runs at 2pm Friday.

    When it ran (for the 1st time) last Friday, it slowed everything down on the SQL server. All applications started being sluggish and we couldn't figure out what was the issue. The reports were delivered to users on Sat 4 AM although the report says it ran at Friday 2PM.

    The table being populated by the stored proc in this report grew to crazy size (12GB) when usually when it is about 30 MB after individual runs. The database (where the table is) of course grew abnormally.

    What I want to know is what happens in the background when DDS's run? Is my stored proc run 125 times simultaneously? Is it populating my this table at the same time? What made the server go crazy? How do I avoid it?

    I would love to be coached on this by all the experts here. Please advise ASAP.

  • May 13, 2010 at 7:08 am

    #1166834

    This may not answer your question directly, but may offer an alternative solution if you can't get the answer you need elsewhere....

    http://www.sqlservercentral.com/articles/Development/2824/

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • May 14, 2010 at 10:44 am

    #1167665

    Check out http://blogs.msdn.com/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx.

  • May 18, 2010 at 5:58 am

    #1168736

    Hi,

    I think it is best practice to create a test dds scubscription before creating a live version. That way any mistakes that happen can be rectified before the subscription is made live.

    I would initially just run a normal subscription and see how long it takes to run the rpeort.

    If it takes a while to run then there could be something in the report which is slowing everything down. If it runs quickly successfully in about a minute then it looks like your dds subscription is to blame.

    Create a test dds subscription, change the script so that it returns 2 users (yourself and one of your colleagues) and returns the parameter for the report. The number of rows returned should determine how many reports are sent. Run the subscription and let me know how you get on.

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply