50 gig database upgrade to 2013 - how long should it take?

Charleh

I've been running the upgrade process for a 50 gig database for hours now and the progress window is frozen on "Ledger Entry Dimensions" - "Preparing to Update"

I realise that the shift from in the handling of dimensions requires a lot of shifting and comparison of data, but this has been running for several hours now (albeit I can see that the SQL process is using a lot of CPU/memory and there is a running UPDATE statement in SQL Activity Monitor)

I've put the DB in simple recovery model and expanded the size of the database to be twice that of the original data (the size is now over 100GB) - is this a normal amount of time to wait before 1% on an upgrade of this size? I've already moved the data from NAV4 to NAV2009R2 which only took a couple of hours. I need to take into account the time to upgrade to 2013 when planning the production database upgrade - and having the upgrade take more than two days is going to cause all sorts of problems for the client (ideally I'd like to get it done in a day!)

Does anyone have any similar experience?

davmac1

The dimension upgrade process uses SQL statements. If your process is stuck, you will need to debug it and see where the slowdown is occurring.
Have you checked the file sizes of your SQL database and tempdb to see if they are growing?
Have you checked your computer's performance and the SQL Server performance.

Are you running the upgrade on the server hosting the SQL Server? For massive database updates, you want to keep it local - all on one computer.

Charleh

So - update on this:

I'm running everything locally - the machine that is running this processed a v400 to 2009R2 update in a matter of minutes for the 50gig db (it took a few minutes for upgrade step 1 for the biggest company and less than an hour to finish the second upgrade step)

My company database/tempdb are not growing and the machine performance is as so:

30-35% cpu usage (most of that is SQL server)
at points 100% disk utilisation, at the moment not much
almost 100% ram utilisation

I'm on decent machine running 2 quad core i7s and an SSD - 16gig of ram, and like I said - the first upgrade took no time at all.

I've left this running all day and overnight and it's still on the 'preparing for upgrade' stage (0% progress)

Checking SQL shows the following UPDATE statement is running:

update [Company Database$upgrade355_Dim] set DimSetID= (select DimSetID   from [Company Database$upgrade355_DimSetID] as D  where ((D.DimVal1 = [Company Database$upgrade355_Dim].DimVal1) or (D.DimVal1 is null) and ([Company Database$upgrade355_Dim].DimVal1 is null)) and ((D.DimVal2 = [Company Database$upgrade355_Dim].DimVal2) or (D.DimVal2 is null) and ([Company Database$upgrade355_Dim].DimVal2 is null)) and ((D.DimVal3 = ... etc

There don't appear to be any resource waits - I've run the query that SQL management studio runs to get a list of locks for process monitor, and the only entry is:

DB: Company Database
Status: RUNNING
Cmd: UPDATE
App: .Net SqlClient Data Provider
CPU: 290888
I/O (MB): 106
Last Req: 2013-10-01 21:27:08.693

So as far as I can see, SQL is actually just processing.

Checking the stats for IO I get

CPU ms/sec: 949
L. Writes/sec: 2
L. Reads/sec: 831404

Execution plan says that the heavy hitting operation is a key lookup against a clustered index which says it estimates at 80 million executions - this is taking up 79% of the query cost

- in summary, it looks like SQL is doing something, but it's been doing this for hours and hours - is there anything I can do to speed this up?

davmac1

How many dimension records do you have?
Looks like the write activity is very low, so it is having a problem on the read side.

Suggest you repost this in the SQL Performance section and see if you get additional responses from one of the SQL experts familiar with this problem.
I personally have not run into this speed issue during conversion. At my level, I could fix this by looking at the actual system. Maybe one of the others could help you without being hands on.

Charleh

There are over 68 million dimension records in the Ledger Entry Dimension table

I'm going to run this against one of the smaller companies and see what the performance is like. There is a holding company in the same DB which probably has less than 1% of the data in it. I'll post back if I find out any more

Thanks for looking so far