0

I need suggestions on how to benchmark performance or create baselines to test the upgrade from SQL Server 2012 to SQL Server 2016 (both standard edition), also if any post upgrade tasks that needs to be checked to ensure smooth performance after upgradation.

P.S : I have recently created a test snapshot of my 2012 SQL Server database(also have separate snapshot of 2012 SQL Server as well) and have upgraded it to 2016 SQL Server (both standard) in AWS RDS. I want to test the performance and functionality of both the instances to check if the upgrade went smooth and I can proceed it for our main production Database.

Please help me on all the checks to be performed.

I have done replaying a trace file from the current server on the new server. Below are the results:enter image description here

Improve this question
6
  • Did you compare the performance of each event between 2012 and 2016 servers? Was the workload same between two tests? Microsoft has few documents about upgrading sql server version. Start here.
    – SqlWorldWide
    Commented May 7, 2018 at 12:30
  • Hi , Could you explain what do you mean by the workload ? I had replayed again and above are the results , as per the link which you have shared, i found that provider errors are the one which we should look into, since here the provider hit ratio seems to be looking good, i guess this time , the results depicted that it worked, But can you help me on how to compare performance of each event ? Need suggestions on that.
    – Sayantani Nath
    Commented May 8, 2018 at 6:17
  • You can load the trace results into a table and compare each event for duration, read, write, cpu usage etc.
    – SqlWorldWide
    Commented May 8, 2018 at 12:54
  • But , when i check my trace table which is created using Replay Template , it didnt have the columns : CPU , Read, Write etc in it as Replay by default took the below columns : RowNumber EventClass EventSequence TextData ApplicationName LoginName DatabaseName DatabaseID ClientProcessID HostName ServerName BinaryData SPID StartTime EndTime IsSystem NTDomainName NTUserName Error RowCounts RequestID EventSubClass Handle ..
    – Sayantani Nath
    Commented May 9, 2018 at 9:23
  • You need run another trace during replay to capture those so you can compare.
    – SqlWorldWide
    Commented May 9, 2018 at 12:18

1 Answer 1

Reset to default
1

When I did SQL 2005 to 2012 I used the Distributed Replay Controller feature. You run a profiler trace with the DRC template. Then run the command on a few servers and it runs those trace files against a target server.

During the process you check whatever performance monitoring app you use and make a note of the times

Improve this answer
2
  • Hi , can you suggest how to use distributed replay or the steps if you mention here , Thanks in advance .
    – Sayantani Nath
    Commented May 8, 2018 at 7:08
  • It's a command line utility and you install it as a component as part of the SQL install. First you run a profiler trace with the special profiler template it installs. Stop the trace, and copy the trace file to every server you want to run it from. Install DRC on those and then run the command, pointing to your trace and the destination server you want to test against. I did it against a pre-production server with a restored copy of our main database and ran it from half a dozen test servers.
    – Alen
    Commented May 8, 2018 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.