0

I am facing some performance problem of production SQL Server Express.

I would like to profile SQL Server using SQL Server profiler.

What I have done so far:

On production machine:

I turned on tracing according this answer:

CMD prompt as admin

net stop MSSQL$SQLEXPRESS
net start MSSQL$SQLEXPRESS /T4032

Now, using the SQL Server Management Studio run the following:

dbcc traceon(3605, -1)

After half day of profiling I downloaded trace file from production server from

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Log\log.trc

I backuped database and downloaded the backup to developer machine.

On developer machine:

  1. I restored the backup
  2. I opened SQL Server 2014 Profiler
  3. From menu Tools -> Database Tunning advisor
  4. Connect to server localhost (restored backup)
  5. Choose workload log.trc (recorded trace from production machine)
  6. Choose database for workload analysis (restored backup)
  7. Select database and tables to tune (restored backup)

enter image description here

  1. menu Action Start Analysis (F5)

I got the following error:

The specified workload (file or table) has no tunable events. Events must be one of following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting, RPC:Completed, SP:StMtStarting, SP:StMtCompleted for workload trace file or table.

enter image description here

Configuration

Production SQL Server Configuration:

SQL Server Express with Advance Services 2012, x64, RTM 11.0.2218

Developer SQL Server configuration:

SQL Server Developer Edition 2012 2014, SP1, x64, 11.0.3156

What Am I doing wrong?

Community
  • 1
  • 1
Tomas Kubes
  • 23,880
  • 18
  • 111
  • 148
  • 1
    If you open the trace log in SQL Profiler, do you actually see the events that it says are missing? I don't think the out-of-the-box trace watches for these. You will probably have to run a trace separate from that one, by hand, and do your analysis with that. – R. Richards Dec 04 '16 at 13:43
  • No I don't see these in log.trc, so that problem is that I need to record these events in different way? – Tomas Kubes Dec 04 '16 at 14:05
  • 1
    Yes. Run your own trace, use the output from that. – R. Richards Dec 04 '16 at 14:07

0 Answers0