search

spaghettidba / WorkloadTools

A collection of tools to collect, analyze and replay SQL Server workloads, on premises and in the cloud
MIT License
230 stars 51 forks source link

Performance Improvement #142

Closed a-teece closed 8 months ago

a-teece commented 8 months ago
spaghettidba commented 8 months ago

Great job! Thank you for your contributions! One of the reasons why I did not close replay workers and the associated connection in the original code is the possibility that the connection holds an open transaction and does not have any commands in the queue. In this implementation I am afraid that the connection would get closed. Is this taken care of?

a-teece commented 8 months ago

I haven't actually changed that. You were already closing / opening the connection with no regards to transactions.

All I have done is remove the delay for the "next" command in such a scenario. The reasoning is that a developer will likely

Open connection run command close connection

think time think time think time

Open connection run command close connection

So the replay should be trying to replicate the "think time", but not the time between Open connection/Run Command. This is likly tiny (so not really replicatable given accuracy challenges).

Theoretically if there is a SQL Transaction then the original code could not have closed/opened the connection. If it was a DTC transaction, which would allow the close/open (our app does this) then I don't believe the trace has captured that and none of the code replicates that. So I'm not sure if you have a transaction problem anyway.

spaghettidba commented 8 months ago

Ah I see. Maybe I was just thinking about it but I never coded it correctly. Sorry about that. As you could see, the code is pretty complex (for me at least!) and there is so much to take care of... Thanks again for your help! I hope this is getting closer to being helpful for you!

a-teece commented 8 months ago

It is certainly being helpful now. We're comparing a workload of SQL on AWS EC2 vs Azure Managed Instance. Using this tool to prove the performance goals can be met of a migration.

spaghettidba commented 8 months ago

This is great news, thanks for the feedback!