I am attempting to use trace captures to determine how much of a performance issue was database related and how much was code related. However, the trace is taking longer to replay than it took to capture in the first place.

What I was after was the total time it would take to run our SQL statements alone, without the overhead of our actual code. If the replay took half the total time of the capture, I was going to assume that the fault was both code and database, if the replay took almost the same time as the capture, I was going to assume it was database, if the replay took almost no time compared to our capture, I was going to assume it was our code. I was assuming the replay was a very efficient way of executing all the statements.

Instead when I replay my trace it takes at least twice as long to run as the original capture took. This is true whether I allow multiple threads or require it to be sequential. I am new to this, so I could be doing something dumb (possibly the whole idea here being dumb), but it seems to me that replay ought to be faster, not slower.

My capture trace is the replay template + SQL:statement completed (so that it doubles as a tuning capture).

  • 2
    Can you narrow down what is taking longer, for example is each of the individual statements taking longer to execute or is it just that the aggregate time to replay the whole workload is longer? Is it possible that the extra time is accounted for by something other than query execution, such as connecting and authenticating? – James L Jan 6 '14 at 17:08
  • 1
    I am glad I am not the only one... The replay takes twice as long as running the original load! And the situation is the same: orginal load is a single-threaded app running from the same machine as the replay! Actually the whole this is on the same machine - it is a dev environment! – user36500 Apr 1 '14 at 15:37

SQL Server can handle multiple concurrent requests simultaneously from multiple machines and threads. Unfortunately, replay can only spit them out one at a time, serially, from a single machine - introducing multiple layers of bottlenecks. There is probably also some overhead in replay to read and prepare the statements to send, that doesn't exist in your application.

If you want the replay to be more realistic, you should look into SQL Server 2012's Distributed Replay, which is able to distribute the replay across multiple machines. That said, you're not likely to ever match the original load time perfectly, and there is likely still some inherent overhead in the preparation of each statement during replay, even if you are able to eliminate some of the bottlenecks on concurrency.

  • 1
    Distributed replay is available prior to 2012 with the RML tools. I confess I haven't used the 2012 version but it looks very much like a neater, easier to use repackaging of the RML tools. – Mark Storey-Smith Jan 4 '14 at 10:26
  • I wish this was it, but it doesn't add up. The test queries in question were generated and sent serially one at a time in the first place as they were dependent on one another. Preparing the statements to send had to happen at the application level as well as at the replay level. In fact, since the application uses linq to EF then the SQL was generated in the application from a linq statement, which was then sent to the database, whereas with replay the SQL already existed. – John Robertson Jan 6 '14 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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