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!)
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:
Code: Select all
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
App: .Net SqlClient Data Provider
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?