It looks like you're new here. If you want to get involved, click one of these buttons!
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?