10 Replies Latest reply on Mar 12, 2014 10:18 AM by jasoncadman

    Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours

    jasoncadman Expert

      Dear All

       

      I've run a test upgrade from 7.6 to 7.6.2 and the database upgrade took over 44 hours! Now i know that it's spec dependent and database size dependent but I wondered what times others were seeing?

       

      In the development environment we're running:

       

      Dell desktop PC chassis

      windows 2008R2 64 bit

      Inter Core i& 2.8 gig processor

      14 Gig memory

      Sata drives

      10 meg nic

       

      This runs SQL 2008 R2 natively with an 88 gig database

      Running oracle Virtual box

      3 gig memory for TPS server

      on win2k3 r2

      10 meg virtual NIC

       

      Obviously this is going to be slower than our live environment but by how much and do we need to dedicate a whole weekend to upgrade just in case (rhetorical mainly)

       

      Any opinions welcome

       

      thanks

       

      Jason

        • 1. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
          dmshimself ITSMMVPGroup

          I think a lot of that time is being spent converting the varchar attributes.  If you take a look at the metadata log for some of the long running items, you could look at the SQL it was using and just run that to get a timing on an old copy of the database.  IT will be busy copying an attribute to a tempoary one, recreating and then copying back.  Repeat on a copy of the DB in prod and at elast you'd be able to get a rough calculation on the relative speeds.

           

          In one case I did we popped the database onto a SSD drive and this made a big difference to the conversion speed, then copied it back to regular storage when done.

          • 2. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
            jasoncadman Expert

            Good advice thanks Dave

             

            There aren't any time stamps on the metadatalog file but when i ran a test to live recently that took a good long while copying over an attachment data table

             

            How can you tell which are the long running items?

            • 3. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
              jasoncadman Expert

              I'm guessing that this bit is taking a while but cant really tell from the logs

               

              </MetadataLogEntry>

                                  <MetadataLogEntry xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                                                      <Sql>ALTER TABLE im_attachment_message ADD mdm_temp_column nvarchar(max) NULL</Sql>

                                                      <Result>Success</Result>

                                  </MetadataLogEntry>

                                  <MetadataLogEntry xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                                                      <Sql>UPDATE im_attachment_message SET mdm_temp_column = im_body</Sql>

                                                      <Result>Success</Result>

                                  </MetadataLogEntry>

                                  <MetadataLogEntry xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                                                      <Sql>ALTER TABLE im_attachment_message DROP COLUMN im_body</Sql>

                                                      <Result>Success</Result>

                                  </MetadataLogEntry>

                                  <MetadataLogEntry xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                                                      <Sql>ALTER TABLE im_attachment_message ADD im_body nvarchar(max) NULL</Sql>

                                                      <Result>Success</Result>

                                  </MetadataLogEntry>

                                  <MetadataLogEntry xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                                                      <Sql>UPDATE im_attachment_message SET im_body = mdm_temp_column</Sql>

                                                      <Result>Success</Result>

                                  </MetadataLogEntry>

                                  <MetadataLogEntry xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                                                      <Sql>ALTER TABLE im_attachment_message DROP COLUMN mdm_temp_column</Sql>

                                                      <Result>Success</Result>

                                  </MetadataLogEntry>

                                  <MetadataLogEntry xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                                                      <Sql>DBCC CLEANTABLE (0,im_attachment_message,1000);</Sql>

                                                      <Result>Success</Result>

                                  </MetadataLogEntry>

              • 4. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
                dmshimself ITSMMVPGroup

                you could turn database trace on in tps config and then run an upgrade then you would have some timings I think.  Or if you took one of those copy type sequence for a large table like im_incident, and ran that by hand on both

                sql boxes it should give you a rough comparison

                1 of 1 people found this helpful
                • 5. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
                  Stu McNeill Employee

                  Hi Jason, Dave,

                   

                  The longest I've seen it taken is 3 or 4 hours.  44 hours is a new record, congrats!

                   

                  First take a read of this doc which explains a bit more why the upgrade through 7.6.0 and 7.6.1 can take longer than usual: Considerations before upgrading a Service Desk database through version 7.6 or 7.6.1.

                   

                  The short version is there are some major schema changes.  When it converts all the String columns this can take a while when there are many rows to convert.  For instance pm_title and pm_description on the pm_proecss table, im_title and im_text on the im_incident_note table, etc.  This gives the SQL server a lot of work so it will use a lot of CPU, memory, and transaction logging.

                   

                  You can speed that up by changing the recovery mode on the database to "SIMPLE" or increasing the hardware specs on the SQL server.  You can also manually make some of the changes yourself before the upgrade  The way Service Desk converts the columns is the safest but is not the most efficient.  If you compare the data types of the columns on the larger tables you can make those changes yourself manually before running the upgrade which will be quicker and mean the upgrade won't need to touch them.

                   

                  I'd only recommend doing that if you're confident about making those changes and have already successfully upgraded once to confirm what changes were required and that they were successful.

                   

                  Thanks

                  • 6. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
                    jasoncadman Expert

                    That's good stuff.

                     

                    I'll change the recovery model to simple and turn the database access logging on and run it again

                     

                    I suppose that I could try the upgrade on the live server and see how it goes. If it affects performance too much then I'll have to stop it and run it overnight

                     

                    Thanks very much for the advice

                     

                    Jason

                    • 7. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
                      jasoncadman Expert

                      Just had a read of the http://community.landesk.com/support/docs/DOC-28996 document and running the mdm for the unicode prior to the actual upgrade sounds like a good idea. Hopefully that should take some time off

                      • 8. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
                        jasoncadman Expert

                        This is a 7.6 system that has been upgraded from 7.5 SP1 so i'm assuming that the unicode upgrade will already have been done. I don't get a tickbox on the 7.6.2 or the 7.6 i think that it's already been carried out

                         

                        Is that correct?

                         

                        Thanks

                         

                        Jason

                        • 9. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
                          Stu McNeill Employee

                          That is correct Jason.  The big job after 7.6 is the change from ntext to nvarchar(max) which is the columns for any unlimited String attribute.

                          • 10. Re: Upgrade from Service Desk 7.6 to  7.6.2 took 44 hours
                            jasoncadman Expert

                            I ran this in the Test account and reran the upgrade on the live hardware after changing the recovery model to simple and the speed of the upgrade was phemominal. 20 minutes!

                             

                            These were the standard columns that I changed manually:

                            alter table pm_process_assignment

                            alter column pm_description nvarchar(max) NULL

                             

                            alter table im_reminder

                            alter column im_body nvarchar(max) NULL

                             

                            alter table tps_audit_trail_value

                            alter column tps_value nvarchar(max) NULL

                             

                            alter table tps_audit_trail_value

                            alter column tps_original_value nvarchar(max) NULL

                             

                            alter table pm_process

                            alter column usr_processref nvarchar(max) NULL

                             

                            alter table pm_process

                            alter column pm_description nvarchar(max) NULL

                               

                            alter table tps_user_message

                            alter column tps_application_data nvarchar(max) NULL

                             

                            alter table tps_user_message

                            alter column tps_body nvarchar(max) NULL

                             

                            To get the size of the tables in order i ran this:

                             

                            WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows )

                            AS (SELECT

                            s.Name

                            , o.Name

                            , p.used_page_count * 8

                            , p.reserved_page_count * 8

                            , p.row_count

                            , case when i.index_id in ( 0, 1 ) then p.row_count else 0 end

                            FROM sys.dm_db_partition_stats p

                            INNER JOIN sys.objects as o ON o.object_id = p.object_id

                            INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id

                            LEFT OUTER JOIN sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id

                            WHERE o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0)

                             

                            SELECT t.schema_name

                            , t.table_name

                            , sum(t.used) as used_in_kb

                            , sum(t.reserved) as reserved_in_kb

                            ,sum(t.tbl_rows) as rows

                            FROM table_space_usage as t

                            GROUP BY t.schema_name , t.table_name

                            ORDER BY used_in_kb desc

                             

                            to check for the ntext data type on the top 12 largest tables I ran this for each changing the bit in bold for each table name

                             

                            SELECT

                                c.name 'Column Name',

                                t.Name 'Data type',

                                c.max_length 'Max Length',

                                c.precision ,

                                c.scale ,

                                c.is_nullable,

                                ISNULL(i.is_primary_key, 0) 'Primary Key'

                            FROM   

                                sys.columns c

                            INNER JOIN

                                sys.types t ON c.system_type_id = t.system_type_id

                            LEFT OUTER JOIN

                                sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id

                            LEFT OUTER JOIN

                                sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

                            WHERE

                                c.object_id = OBJECT_ID('tps_user_message_recipient_archive') and t.Name ='ntext'

                             

                            I can't confess to creating these scripts but a little googling works wonders

                             

                            Thanks for your help. Hope that this helps others