SQL Server Replaying Traces on Servers With Multiple Databases

We’re currently investigating what the performance gains would equate to if we moved from sql server 2005 to sql server 2008. The best test is to pull a production trace down from delivery, and rerun it against two instances on the same server; one being 2005 and the other 2008. The obstacle we need to overcome is that the Server we’re looking at is running multiple DB’s, and calls aren’t DB specific, they only reference the table names or SP’s, not the DB. Therefore when we extract the calls, we don’t know what database the call is being made against, and a rerun of the trace will yield an object not found message, since we’re not in the right db. To work around this, we’ve setup a custom trace with 3 datapoints to capture; Textdata, Databasename, and Spid. The spid is required, so it comes along for the ride. Once we capture this data into a trace file we import it into a table. From their the following query is run.


select
'Use ' + DatabaseName + char(13) + char(10) + TextData + char(13) + char(10) + 'go'
from tracetable

This will out put a formatted trace results that which’s to the proper db prior to executing the command.