Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server 2005 database... a copy of it is running in development on a full version of SQL server. Another copy is running in SQL Server 2005 Express on a web server.

I've used SQL Profiler and saved a Tuning trace log from activity on the SQL Express copy of the database.

I want to use the saved trace log in the Database Engine Tuning Advisor...

If I try when connecting the Advisor to the Express database, I am told that Express is not supported.

If I try when connecting the Advisor to the SQL Server database, I get empty results.

Is there any way to do this?

share|improve this question
add comment

4 Answers 4

up vote 0 down vote accepted

You should be able to tune an identical database on the local development server using trace data collected from a remote production server (Express).

But you cannot use trace tables stored in the remote server to do this. According to Considerations for Using Database Engine Tuning Advisor, "Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server"

But I guess you are using trace files, so this should not be an issue.

If you don't get any recommendations, you should first check the list on the previously mentioned MSDN page:

Database Engine Tuning Advisor might not make recommendations under the following circumstances:

The table being tuned contains less than 10 data pages.

The recommended indexes would not offer enough improvement in query performance over the current physical database design.

The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. The user must be a member of the db_owner database role.

share|improve this answer
add comment

I think you would need a copy of the database from the Express instance restored to the full instance. The advisor looks at the schema to make it's tuning advice.

share|improve this answer
add comment

I think all you need to do is trace the DB to a table and then point tuning advisor at the data in the table that contains the trace results. That way I dont think you need to move any databases around.

share|improve this answer
add comment

I also found that I could edit the trace file in SQL Profiler and remove any columns that specify the login or database name. After that, the Tuning Advisor worked.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.