Post reply

SQL Profiler trace "replay" is very, very slow

  • September 29, 2009 at 11:04 am

    #134122

    We're running SQL 2005 Standard SP3 on Windows Server 2003 SP 2 with all patches applied.

    All of the following is in the dev / QA environment, no production.

    I ran a trace using the replay template on a database that resides on Server A from Server B. The trace ran for about two hours only.

    After the trace was done, I copied the trc file from Server B to Server A.

    I restored the DB to the "before trace" state, loaded the trc file and started the replay. The replay takes about thirty minutes to reach 1%, implying about sixty hours to replay the two hour trace.

    I accepted all defaults when replaying the trace.

    I canceled the trace and started modifying the replay parameters for threading. Things started running faster (STILL not as fast as the original trace) but the multi-threading caused blocking locks. This taints the value of the replay.

    Server A is a quad core processor with 4 GB of RAM and SCSI drives. That is, this slow down has nothing to do with lack of hardware.

    I've done this before and the replay was always FASTER than making the original trace because there is no "down time" or waits between user entries.

    What am I missing?

    Thank you in advance for your time and assistance.

  • September 29, 2009 at 8:18 pm

    #1059782

    When collecting trace..have you specified dbid for only that particular db.

  • September 30, 2009 at 11:28 am

    #1060098

    Thank you for your reply.

    The target database was the only user database on the server. The system databases were not excluded from the trace. However, I copied the trc file to the "target" server for replay. So, the system databass were the same (un-restored, but the same) as when the trace was run.

    Thanks for your time and help.

  • September 30, 2009 at 4:52 pm

    #1060233

    Hoes it resolved then?

    MJ

  • September 30, 2009 at 4:59 pm

    #1060236

    No, I still have the problem.

    Thanks

  • November 8, 2009 at 12:20 pm

    #1076477

    1. check reply errors.

  • November 8, 2009 at 12:23 pm

    #1076478

    2. or copy the .trc file to another server, so that the load driver and the target database server are on two seperated boxes.

    3. Have you tried RML utility?

  • November 9, 2009 at 12:18 pm

    #1076851

    I had not tried your second option and had not even heard of the third. Google fixed that.

    It's going to be some time before I can pursue this. I appreciate your time and help.

    Bill

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply