June 18, 2009 at 6:01 am
we have a script that ran fine on sql 2000 - we have now moved it to a different server running sql 2008 and the script is very very slow
Has anyone else encountered this problem and sorted it
June 18, 2009 at 7:15 am
It can happen. The query engine and the query optimizer are different in 2008 than they were in 2000.
First, the obvious stuff, you have up to date statistics on the 2008 database? If not udpate the stats with a full scan. After that, it really depends on the query. If you can, post the query, execution plans (2000 & 2008), structures & sample data. See the link below for the best way to put it all together.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2009 at 7:34 am
We are actually trying to jun a sql server agent job that consists of around 14 different steps - It basically backs up the bespoke database tables and restores them and then creates a lot of custom tables - the job runs ok until it hits a certain Create Table- then it doesn't error but it can take up to 2 days to complete this bit of code - the table does not particularly have a large amount of records (around 3000) and completes in minutes in SQL 2000 - if we comment this Create Table out - the script will simply "stick" at a later point - we suspect that the server is running out of some sort of resource but we have been assured by ourr server people that this isn't the case
Any ideas ??- they are threatening to put us back to SQL 2000 if we can't sort this so any help will be much appreciated
June 18, 2009 at 8:15 am
A create table statement takes a day or more? No, that's pretty abnormal and not expected behavior in 2008 (or 2005 or 2000 or 7... maybe in 6.5).
Ummmm... Let's see. Blocking? Some other kind of contention? If you're really at a loss, try collecting the wait states & queues (here's an MS white paper on that). At least you'll be able to know what it's waiting for. I'd need to drill down a lot on that job (and any others) to understand for sure. Have you monitored mem usage, disk usage, cpu, etc., to see what happens to the server when you're running the process?
It's really hard to say without more info.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply