July 13, 2009 at 1:15 pm
I'm trying to replay a trace on a different server where the database id's do not match the source databases on the source server. I have read that Profiler will match using database name if the id's do not match, which is great.
However, I see that the database id's are being used by other databases on my target server. I'm afraid that if I try to replay the trace, Profiler will start executing the SQL against these databases even though they are the wrong databases. If this is the case, it may corrupt these databases.
Does anyone know whether or not this is the case and/or what I should do to prevent any problems?
Hopefully, it will check the database names and ids to make sure they match and just do the name matching, but I doubt it. Let me know, thanks.
July 13, 2009 at 1:57 pm
According to BOL, what you heard about matching on database name is correct, but I have not experienced this personally. From BOL:
The database IDs on the target ideally should be the same as those on the source. However, if they are not the same, matching can be performed based on DatabaseName if it is present in the trace.
What I have done in the past is to save the trace to a table and then update the databaseid column to the databaseid in the dev/test server using T-SQL. You can then open the trace table using Profiler and replay from there or save it as a trace file and then replay from the file.
I'll do some testing on this and re-post.
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 13, 2009 at 2:21 pm
Thanks for your response but I figured it out. I realized that I could set a break point on the replay trace, so I used that before it hit any real sql statements and it was connected to the correct database ids that corresponded to my databases. It looks like it was smart enough to use the database name after all.
Now I have a new question though:
I'm not used to using PerfMon that much, or the replay trace.
I need to capture counters in PerfMon or set up Profiler to capture statistics which will help me analyze performance issues when I change indexing on the database and then replay my trace against it. Are there are any counters to look at physical / logical reads, table scans, index scans, index seeks etc...
Anything that would help prove database performance is better overall with index changes. I'd like to capture them in a log and compare and contrast the results from the same workload through using the replay trace. Thanks!
July 13, 2009 at 2:47 pm
I did just did a quick test and I did not see the behavior BOL states should happen. I think it will only do the DatabaseName matching if the DatabaseID does not exist on the server. I have not had the opportunity to verify that yet. I will try that later.
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 13, 2009 at 2:53 pm
ahenderson (7/13/2009)
I need to capture counters in PerfMon or set up Profiler to capture statistics which will help me analyze performance issues when I change indexing on the database and then replay my trace against it. Are there are any counters to look at physical / logical reads, table scans, index scans, index seeks etc...
I would say that Profiler is your best bet here. You can add the Scans events and then look at the Reads and Writes on the RPC and TSQL Completed events. Then you can also use the DMV's like dm_exec_query_stats and sys.dm_db_index_usage_stats.
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 13, 2009 at 3:12 pm
Ok, sounds good. Thanks for your help.
EDIT: Nevermind.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply