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. 2. Updating Statistics

USE db_name;

GO

EXEC sp_updatestats;

1. 3. Refreshing your views using SP_REFRESHVIEW
   4. Verifying compatibility level.

What you need?

1. 3 Mock servers:

1. 1^st server will need to have existing production environment configuration.
2. 2^nd server will have configuration same as new production environment.
3. 3^rd server will replay the workload on both the servers.

2. Point in time backup of production database.
3. Capturing workload trace from production.
4. Processing captured workload for replay.

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

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

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

6. Capturing replay workload.
7. Creating DEA report.
8. Cleaning captured replay try by clear trace.
9. 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/