How to Use SQL Server Profiler and Tuning Advisor

What is SQL Server Profiler

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which Stored Procedures are affecting performance by executing too slowly.

What is Tuning Advisor

Tuning advisor helps to get the performance report that is generated by SQL Profiler and provide the appropriate indexing. It takes one or more SQL statements as input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements.

Real Time Example

The following is a real time example:

  • First of all start SQL Server 2008
  • Click on New Query
  • Select Northwind database

Let’s fire some select statements.

Now let’s start SQL Profiler, go to the tools.

img2.jpg

You will see it is shows the trace properties, like name, provider name and provider type.

img4.jpg

Image 4.

Now click on the “Run” button.

img5.jpg

Image 5.

You will see that there are many queries running internally but we want only the Northwind report. Let’s make some changes in the trace properties.

img6.jpg

Image 6.

Since we are creating this for tuning puposes, select the template for tuning and select the event selection tab and click the “Column filters” button.

img7.jpg


Image 7.

Select the database name and in the statement provide the database name and click “Ok”.

img8.jpg

Image 8.

Now run the profiler again, you will see only the Northwind database queries being executed and loading into the report.

img9.jpg

Image 9.

Now stop the profile and save this report as a trace file.

img10.jpg

Image 10.

So the trace file has been saved at the given location and with the given name.

Now time has come to start the database engine tuning advisor.

img11.jpg

Image 11.

And now select the workload file that we saved on the local machine and select the database to tune and the “Start analysis” button.

img12.jpg

Image 12.

img13.jpg

Image 13.

Hit the “Start Analysis” button.

img14.jpg

Image 14.

If you get this error then do the following to remove this error:

  • Go to the Tuning Options tab
  • Click the Advanced Options button
  • Check the defined max space for recommendations
  • Enter 5 in the text box
  • Click “Ok”

img15.jpg

Image 15.

Now click “Start Analysis” again.

img16.jpg

Image 16.

img17.jpg

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s