TechSapphire Tuesday, 2018-08-21, 9:28 AM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Contact info:

    Name: Yogesh Mehla

    Skype: Kingconspiracy

    Email: yogesh.mehla@gmail.com

    Phone: +91-9023262520

    SQL Server database migration performance testing and check list

    Migrating database is always critical job for Database Administrators, usually which ends into bad performance of same database on newer version of SQL Server. Reason for this problem can be million but general reason is, it is because of growing data size.

    Now question comes, how to be sure about performance after database migration?

    Answer: With pre migration steps using workload replay mechanism.

    Will it guarantee 100% performance stability?

    No, but it will make 95% chances of “no performance degrade”. Still 5% is left for un-predictable.

    What we have today?

    1. Data Migration Assistant (DMA): Gives us early prediction about things which are going to break in newer SQL Server version.
    2. Database Experimentation Assistant (DEA): Helps into performance testing (workload testing) before migration.
    3. General tips and steps (pre migration):
      1. Hardware testing if it is new server using DISKIO utility or hardware base lining.
      2. Backup everything you can.
    4. General tips and steps (post migration):
      1. Executing DBCC UPDATEUSAGE command

    DBCC UPDATEUSAGE (db_name);

      1. Updating Statistics

    USE db_name;

    GO

    EXEC sp_updatestats;

      1. Refreshing your views using SP_REFRESHVIEW
      2. Verifying compatibility level.

     

     

    What you need?

    1. 3 Mock servers:
    1. 1st server will need to have existing production environment configuration.
    2. 2nd server will have configuration same as new production environment.
    3. 3rd server will replay the workload on both the servers.
    1. Point in time backup of production database.
    2. Capturing workload trace from production.
    3. Processing captured workload for replay.

    Command:2 dreplay preprocess -m . -i "C:\Trace\Trace.trc" -d "C:\Trace\Preprocess"

    1. Replaying workload on server 1st and 2nd from server 3rd.

    Command: dreplay replay -m yogeshmehla -d "C:\Trace\Preprocess" -o -s yogeshmehla -w yogeshmehlas

    1. Capturing replay workload.
    2. Creating DEA report.
    3. Cleaning captured replay try by clear trace.
    4. Creating general report from clean trace data.

    Clear trace report query:

    IF OBJECT_ID('Tempdb..#Temp') IS NOT NULL
    DROP TABLE Tempdb..#Temp
    SELECT CASE
               WHEN CHARINDEX('Exec ', vw1.NormalizedTextData) > 0 THEN 'Proc'
               ELSE 'Query'
           END QueryType,
           vw1.*,
           vw2.v2_ExecCount,
           vw2.v2_Total_Duration_micros,
           vw2.v2_AvgDuration_microS,
           v2_AvgDuration_microS - v1_AvgDuration_microS AS Diff_microS,
           vw2.v2_Reads,
           vw2.v2_Writes,
           vw2.v2_CPU INTO #Temp
    FROM
      (SELECT TextDataHashCode,
              NormalizedTextData,
              SUM(ExecutionCount) AS v1_ExecCount,
              SUM(duration) AS v1_Total_Duration_micros,
              SUM(duration) / SUM(executioncount) AS v1_AvgDuration_microS,
              SUM(READS) v1_Reads,
              SUM(Writes) AS v1_Writes,
              SUM(CPU) AS v1_CPU
       FROM DB2012_ReportingActivity_ClearTrace.[dbo].CTTraceSummaryView --Where NormalizedTextData like 'Exec %'
    GROUP BY TextDataHashCode,
             NormalizedTextData) vw1
    INNER JOIN
      (SELECT TextDataHashCode,
              NormalizedTextData,
              SUM(ExecutionCount) AS v2_ExecCount,
              SUM(duration) AS v2_Total_Duration_micros,
              SUM(duration) / SUM(executioncount) AS v2_AvgDuration_microS,
              SUM(READS) v2_Reads,
              SUM(Writes) AS v2_Writes,
              SUM(CPU) AS v2_CPU
       FROM DB2016_ReportingActivity_ClearTrace.[dbo].CTTraceSummaryView --Where NormalizedTextData like 'Exec %'
    GROUP BY TextDataHashCode,
             NormalizedTextData) vw2 ON vw1.TextDataHashCode = vw2.TextDataHashCode --Where case when charindex('Exec ',vw1.NormalizedTextData) > 0 Then 'Proc' Else 'Query' end = 'Proc'
    
     
    SELECT QueryType,
           CASE
               WHEN Diff_microS / 1000000 BETWEEN -2 AND 2 THEN 'Same'
               WHEN Diff_microS / 1000000 > 2 THEN 'Degraded'
               WHEN Diff_microS / 1000000 < -2 THEN 'Improved'
           END AS Stat,
           SUM(CASE
                   WHEN (Diff_microS / 1000000 BETWEEN -5 AND 5) THEN 1
                   ELSE 0
               END) AS '<5',
           SUM(CASE
                   WHEN (ABS(Diff_microS) / 1000000 BETWEEN 6 AND 10) THEN 1
                   ELSE 0
               END) AS '>5',
           SUM(CASE
                   WHEN (ABS(Diff_microS) / 1000000 BETWEEN 11 AND 15) THEN 1
                   ELSE 0
               END) AS '>10',
           SUM(CASE
                   WHEN (ABS(Diff_microS) / 1000000 > 15) THEN 1
                   ELSE 0
               END) AS '>15'
    FROM #Temp T
    GROUP BY QueryType,
             CASE
                 WHEN Diff_microS / 1000000 BETWEEN -2 AND 2 THEN 'Same'
                 WHEN Diff_microS / 1000000 > 2 THEN 'Degraded'
                 WHEN Diff_microS / 1000000 < -2 THEN 'Improved'
             END
    ORDER BY QueryType,
             Stat

     

    Reference links:

    https://www.microsoft.com/en-us/download/details.aspx?id=54090&a03ffa40-ca8b-4f73-0358-c191d75a7468=True&e6b34bbe-475b-1abd-2c51-b5034bcdd6d2=True&751be11f-ede8-5a0c-058c-2ee190a24fa6=True

    https://blogs.msdn.microsoft.com/datamigration/dma/

    https://blogs.msdn.microsoft.com/datamigration/2016/10/25/database-experimentation-assistant-command-line/

    https://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018