Tech Off Thread

3 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Report on SQL/SSRS 2k5 takes > 10 minutes, query < 3 mins

Back to Forum: Tech Off
  • User profile image
    qwert231

    We have SQL and SSRS 2k5 on a Win 2k3 virtual server with 4Gb on the virt server. (The server running the virt server has > 32Gb)

    When we run our comparison report, it calls a stored proc on database A. The proc pulls data from serveral tables, and from a view on database B.

    If I run Profiler and monitor the calls, I see activity (

    SQL:BatchStarting SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation'), COLLATIONPROPERTY(CONVERT(char, DATABASEPROPERTYEX(DB_NAME(), 'collation')), 'LCID')  

    )

    then wait several minutes till the actual call of the proc shows up.

    RPC:Completed exec sp_executesql N'exec [procGetLicenseSales_ALS_Voucher] @CurrentLicenseYear, @CurrentStartDate, @CurrentEndDate, ''Fishing License'', @PreviousLicenseYear, @OpenLicenseAccounts',N'@CurrentStartDate datetime,@CurrentEndDate datetime,@CurrentLicenseYear int,@PreviousLicenseYear int,@OpenLicenseAccounts nvarchar(4000)',@CurrentStartDate='2010-11-01 00:00:00:000',@CurrentEndDate='2010-11-30 00:00:00:000',@CurrentLicenseYear=2010,@PreviousLicenseYear=2009,@OpenLicenseAccounts=NULL 

    then more time, and usually the report times out.

    This Report was working, albeit slowly, for months.

    If I drop the query (captured from profiler) into SQL Server Management Studio, it takes 2:08 to run.

    Database B just had some changes and data replicated to it (we only read from the data, all new data comes from nightly replication).

    Something has obviously changed, but what change broke the report? How can I test to find out why the SSRS part is taking forever and timing out, but the query runs in about 2 minutes?

     

    Added: Please note, the stored proc returns 18 rows... any time. (We only have 18 products to track.)

    The report takes those 18 rows, and groups them and does some sums. No matrix, only one page, very simple.

  • User profile image
    davewill

    There could be a bunch of things going on here.  I'm guessing the profiler is set to return upon completion of the calls and not the start of the calls.  Therefore the several minutes wait before the stored proc call shows up in Profiler would tell me that something in the guts of the stored proc needs to be investigated.  Another indicator would the the amount of disk I/O reported for each return in Profiler.  Another good indicator would be the use of indexes or lack of.

    Another thing to check is to flush the cache before running the captured query in SQL Server Management Studio.  If the caching is making the difference then pure I/O is the bottleneck.

    Check for non-trivial statistics on the data that might indicate the need for an index.

    Check the number of reads in Profiler to see if they are exponential or in line with the amount of records or pages you are expecting.

    Check the view and see if it encompases anything outside database B.  Check the view and make sure it isn't a heavy weight and is getting rebuilt because of the replication to database B.

  • User profile image
    figuerres

    , davewill wrote

    There could be a bunch of things going on here.  I'm guessing the profiler is set to return upon completion of the calls and not the start of the calls.  Therefore the several minutes wait before the stored proc call shows up in Profiler would tell me that something in the guts of the stored proc needs to be investigated.  Another indicator would the the amount of disk I/O reported for each return in Profiler.  Another good indicator would be the use of indexes or lack of.

    Another thing to check is to flush the cache before running the captured query in SQL Server Management Studio.  If the caching is making the difference then pure I/O is the bottleneck.

    Check for non-trivial statistics on the data that might indicate the need for an index.

    Check the number of reads in Profiler to see if they are exponential or in line with the amount of records or pages you are expecting.

    Check the view and see if it encompases anything outside database B.  Check the view and make sure it isn't a heavy weight and is getting rebuilt because of the replication to database B.

     

    also in my limited exp with using linked servers they suck ....  they are just dog slow.

    i have done a lot of big sql stuff on a single server and when i had to link a remote server it was just plain slow as if i had a 33.6 dialup compared to normal sql stuff.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.