question

arnis avatar image
arnis asked

Same scenario Profiler replay random run times

In each test run DB full backup restored in SQL Server 2012SP1, PC with Server 2012SP1 restarted, SQL Profiler trace file replayed with all default options. The time in replay log last entry "Replay time" not consistent among runs. Worst seen with instance on HDD- 62min and 31min. Can someone explain why seemingly same scenario leads to such difference in replay execution times?
profilertracereplay
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
It's likely to either be due to the system you're running on or the database. The system you're running on may have other processes running that can lead to contention on resources such as the disk or cpu or memory. That will lead to differences, even wide differences, between run times. You should be monitoring the server while you run the replay which will enable you to determine if you're seeing differences in CPU or memory, etc., queuing, or unusual waits. The database could be causing the issue if you're not starting from a common restored state each time. This is because you may be seeing variations in statistics which can lead to variations in execution plans which certainly cause changes in performance. You may also be seeing page splits or something along those lines during subsequent runs that you didn't see on previous ones (or even page splits on early runs, not seen on later ones) that will seriously impact performance. Be sure you're starting from a known state each and every time you do the run. But, the real key to the whole thing is to have monitoring in place so you understand what is happening during the runs.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

arnis avatar image arnis commented ·
Ok, I did not mention this is physical machine doing nothing else beside reply. Do you mean there could be differences after restore of full db backup? I'll try performance counters to see where the difference comes from.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Assuming you restore before each individual run, then no, there are unlikely to be differences. But if you restore, then run once, twice, etc., absolutely, there will be. I'm still back to monitoring being your best friend in this situation.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.