Long-running process

  • Hi,

    We have a third-party application with a SQL Server backend that imports the records we insert into a staging table. Usually, the count is around 4,000 records. When the database was on SQL Server 2005, the import process took only a few minutes. Ever since we moved the database to a SQL Server 2008 R2 instance, the application's import process takes 4-6 hours. No other databases were adversely affected. Do any performance-tuning/database administration experts have some ideas of what I can check? I've run a bunch of queries to try to see if there's a long-running query on the server, but there's nothing that looks out of place. In addition, the queries from the application shouldn't really have changed--only the location of the database itself changed.

    Thanks,

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • Did you update statistics after upgrading the database?

    4000 rows shouldn't take 4-6 hours to import even with RBAR.

    Can you share schema and queries?

  • Jack,

    Thanks for the reply! It looks like quite a few of the statistics are out of date. The database in question is owned by a third party and I'm not sure if I'm allowed by contract to update the statistics; I have a call in to see.

    Thanks again,

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • Sounds like that is at least part of the problem. Are you allowed to do any maintenance on the database?

    You should also check the auto update statistics setting on the database. In theory your load process should eventually cause the statistics for the affected tables to be updated if auto update statistics is on.

    SELECT DATABASEPROPERTY(DB_NAME(), 'IsAutoUpdateStatistics')

  • That property is set to true, so I wonder if there's another contributing factor.

    Mike Scalise, PMP
    https://www.michaelscalise.com

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

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