May 28, 2013 at 6:46 am
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
May 28, 2013 at 11:18 am
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 Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 8:52 am
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
May 29, 2013 at 9:37 am
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')
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 10:54 am
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