July 31, 2008 at 7:52 am
For in-production systems, using Profiler, you have the ability to capture, well, real user activity. Not just test data, but actual data, as it happens, in the frequency and order as it happens.
So in theory, first capturing, and then replaying the activity seems to offer a perfect world of testing index changes to see how those changes might impact the time it takes to replay the captured profile trace.
Has anyone done this, and if so what are your thoughts, or did you find that other tools are better at capturing production activity and then replaying it. I’d love to hear any and all.
Andrew
The more you are prepared, the less you need it.
July 31, 2008 at 8:10 am
I can't say that I have actually done it, but not only can you replay a trace after creating indexes but you can use a saved trace as the workload for the Database Tuning Adviser which will suggest index changes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 8:22 am
Thanks, but I'm really looking for insight from those who have found ways to capture production traces and use them for performance tuning.
As for the tuning advisor, I've found that the information from the DMV's holding index data is the most valuable, but that is another thread. (e.g. sys.dm_db_missing_index_details, et al)
The more you are prepared, the less you need it.
July 31, 2008 at 12:10 pm
Tuning Advisor stinks.
I've used Profiler to capture production and then play it back. It's actually pretty easy. The real key is that you absolutely have to recover to a precise point in time so that you don't have any disparity in the data that will cause the playback to suffer. You also need to be sure that any cross-database or cross-server dependencies are appropriately covered. Other than that, it's basically point and click.
We use it occasionally, regularly, but not often, as a means of testing indexes or refactorings within stored procedures that don't change the interface of the procedures.
The one shortcoming, and it's fairly large, production represents a series of threads from various applications and/or application servers. When you run the playback, it's basically single-threaded, from one machine and it will mean a slower overall performance than what you'll see in your production server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2008 at 1:46 pm
Grant Fritchey (7/31/2008)
Tuning Advisor stinks.
Don't be afraid to speak your mind, Grant.:D
The one shortcoming, and it's fairly large, production represents a series of threads from various applications and/or application servers. When you run the playback, it's basically single-threaded, from one machine and it will mean a slower overall performance than what you'll see in your production server.
The way I understand replay is that is will use multiple threads to simulate the multiple spids in the trace. I happen to be researching Profiler and Tracing for a presentation and in my favorite book, Inside SQL Server 2005 Query Tuning and Optimization, it says this (bolding mine):
The Replay options section is a bit confusing as worded. No matter which option you select, the trace will be replayed on multiple threads, corresponding to at most the Number Of Replay Threads specified. However, the Replay Events In The Order They Were Traced option ensures that all events will be played back in exactly the order in which they occurred, as based upon the EventSequence column. Multiple threads will still be used to simulate multiple spids. The Replay Events Using Multiple Threads option, on the other hand, allows SQL Server Profiler to reorder the order in which each spid starts to execute events, in order to enhance playback performance. Within a given spid, however, the order of events will remain consistent with the EventSequence.
You can read it here: http://msdn.microsoft.com/en-us/library/cc297238.aspx
I do plan on trying out a replay soon and I will trace the replay to see the number of connections and spids.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 5:25 am
Hey Jack,
You know, I do forget about that threading thing. Holdover from the days of 2000. I will say, it still doesn't work quite like having multiple connections from multiple machines.
And, yeah, Tuning Advisor performs in a less than adequate manner. Better?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply