SQL SERVER – Performance Tuning – Part 1 of 2 – Getting Started and Configuration

Performance tuning is always a complex subject whenever one has to deal with it. When I was beginning with SQL Server, this was the most difficult area for me. However, there is a saying that if one has to overcome their fear one has to face the fear first. So I did exactly this. I started to practice performance tuning.

Early in my career I often failed when I had to deal with performance tuning tasks. However, each failure taught me something. It took a quite a while and about 100+ various projects before I started to consider myself a guy who knows something about performance tuning.

Every performance tuning exercise gave me new experience. I firmly believe in “Practice makes perfect” when I am involved in performance tuning projects. There were times when I could figure out problems by just looking at few configurations and settings. When asked how I knew this so quickly my usual answer was “experience.”

Quite often I get requests to share my expertise in performance tuning. This is when I wish it were easier. If I had to go back and do one thing during my whole career of performance tuning, I would document every single performance tuning rule that I have come across in my life. Once everything is documented, the expertise of performance tuning can be measured, and the results of these rules can give additional confidence while doing performance tuning.

Performance tuning used to be the responsibility of the DBA and developers who built the code. However, recently I have seen in many organizations that there are dedicated experts who are responsible for performance tuning of the server. Often they have to manage multiple servers and heterogeneous RDBMS. The job is more stressful and more challenging than we know.

I constantly try to test out various aspects of performance tuning as well as all available tools which can help me select the optimal solution for performance tuning. I recently tested out DB Optimizer from Embarcadero and here is very short tutorial of how this tool can be used, as well few of the pros and cons.

Let us divide this article into the following subjects.

  1. Getting Started
  2. Analysis and Detection
  3. Tuning and Optimizing

Getting Started

In this section we will go over basic steps to install DB Optimizer XE2 Version 3.0. First of all go to DB-Optimizer-XE and download the trial version of the DB Optimizer. The installation is very quick and simple. The installation is pretty much selecting button NEXT … NEXT and Finish!

After you install and load the DB Optimizer, and right after the splash screen it will ask for the workspace where all the files of this optimization project will be saved. I think the most difficult part is over by now. From here the tool is very easy to use and self-explanatory. The very first screen has three sections:  overview, tutorial and workbench.

I strongly suggest that if you are new to the tool then go over the tutorial. The tutorial is well organized and very detailed. It not only explains how one can do various performance tuning tasks but it often explains why they are done a certain way.

Once the tutorial is over or if you are an experienced user, head to the workbench directly. Workbench is the area where we will do various performance tuning related tasks.

Configuration

Here is one thing I have to point out: even though there is no wizard or step-by-step setup guide – the interface is very intuitive. For example, as soon as you start workbench, the very first screen is data source. Once data source is configured a simple right click on the data source is all one needs to work with performance tuning aspects.

This tool lets you connect to multiple data sources. We will select SQL Server as the data source and give it an easy name so we can refer to it easily in the future. One specific thing worth noting is that while connecting to the data source, it lets you specify what kind of data source you are connecting to. Are you connecting to Development, Test, QA or Production? This is very essential so that we do not deploy changes to the incorrect server while we are performing various tasks with DB Optimizer.

The next step is to provide details of the connection string and check if the connection is successful by clicking on the Test Connection button at the bottom. If the host address along with username and password is correct, the connection will pop up with a “Connection was successful” message.

Click on Finish after the connection is successfully made to the data source. It will register the data source and bring up the same screen where you started.

Expand the data source which we have registered. Once you expand you will see every single object listed under the data source.

After configuring the data source, the next task is to configure the profile.

Profile configuration is a very easy process and we define where we will be storing various captured data in the database as well what data source will be captured.

Once the data profile is configured go ahead and click on Profile. I think we are now 100% done in configuring the setup. The next step is to analyze our profiled data.

In next blog post we will continue with the second part of  Analysis, Detection, Tuning and Optimizing. Till than you can download and configure DB-Optimizer-XE.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

17 thoughts on “SQL SERVER – Performance Tuning – Part 1 of 2 – Getting Started and Configuration

  1. The Database Engine Tuning Advisor is a blunt tool that is no substitute for knowledge and experience in query tuning. When it was introduced I think I used it once and gave up and continued tuning queries by hand.

    It has its place as a tool for non-technical people who might have to look after databases occasionally.

    I interviewed candidates for a SQL Database Development position recently and one candidate pretty much ruled themselves out by stating that the Tuning Advisor would be their first step in diagnosing a poorly running query.

  2. Do u mean that DTA is not a good tool or it is not advisable to use? I agree it doesnot give 100% recommendation but it will give you an idea about where the issues are?? i would never implement them without first reviewing every suggestion.

    • I simply don’t think it’s a very good tool. The scope of what it can recommend is quite limited (i.e. indexes), and someone with good performance diagnosing skills will do a far better job in less time than it will take to run DTA.

      Not all performance issues can be fixed by adding indexes – quite often the queries need to be modified as well.

      If you’re currently relying on DTA, I’d highly recommend trying to live without it for a while and do your performance tuning manually. You’ll probably find you end up with much better results.

  3. Pingback: SQL SERVER – Performance Tuning – Part 2 of 2 – Analysis, Detection, Tuning and Optimizing « SQL Server Journey with SQL Authority

  4. I agree with John that DTA is very limited, there are other techniques which would yield better results, One thing to keep in mind is how does data relate to the big picture in terms of the business, that kind of perspective cannot be offered by DTA.

  5. In my company I can’t download DB-Optimizer-XE. I was trying to use Databse Engine Tuning Advisor. But is is giving me an error when trying to open the trace file saying some trace defintion file is not there. I checked for the file in Windows Server 2008 machine. Even though msxml6.dll is there the tracedefinition file is not there. Please help me to resolve the issue and proceed with the analysis using DTA

  6. I want to know for optimzing stored procedure , what should be used
    exec storedprocname
    0r
    sp_executesql storedprocname

    please tell me

      • Yes, you were right. In BOL there is a limit of 2G objects. Means it includes procedures, views, functions, indexes, tables. So, theoretically, if I will create only tables, the limit will be 2G tables.

        An a limit of 10GB for data file.

        Thank you

  7. Pingback: SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 4 « SQL Server Journey with SQL Authority

  8. We have installed and configured tool successfully, we have created “data source” and tested connection successfully, but while expanding “Database objects” we are not getting any list and session is getting hung.
    any idea or suggestion.?

    • The issue that you have could be due to the JDBC driver that is used for connectivity. You can change this by right clicking on the datasource and selecting ‘Properties’.
      This allows you to modify the registration details. Select the ‘Advanced’ tab and then the JDBC Driver dropdown box where you can select which JDBC driver to use. Once you have modified this, try connecting to the datasource again. I have same issue resolved by changing JDBC drive.

  9. Pingback: SQL SERVER – Maximize Database Performance with DB Optimizer – SQL in Sixty Seconds #054 | Journey to SQL Authority with Pinal Dave

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