Simplifying IT, Empowering Businesses

In this blog, let us see how we can identify slow running queries and how to improve SQL server performance by using Database engine tuning advisor.

SQL server performance largely depends on how you created the index i.e. clustered index and non-clustered index etc.

Most of the time developer don’t think even about indexes. By default, SQL server creates a clustered index on the primary key and that’s the only index you will find in most databases by default.

What happens when these default setting are taken to production. It doesn’t work out for a long period of time or it doesn’t work out as your workload increases. So as a workload changes over a period of time. In other words as rows increases these indexes are not able to cope up and your SQL server performance starts degrading.

In other words, as time passes by on a production server you would like to go and rethink on your indexing strategy.

Now, this rethinking of indexing strategy it is not so simple. In other words, it is not just going and right clicking and recreating the index or something. It’s a bit of a complex process. It’s a 3 step process.

1. The first thing you have to do is you have to collect the workload In other words when you deploy your index or when you create the index first time must be the workload was 10% now the workload has gone up to 80% so first, you would like to collect what is the amount of workload you have.
2. Next, you would like to analyse that as per workloads are the current indexes or the default indexes are they appropriate. If they are appropriate then its fine must be you have to hunt around something else to find that where the SQL performance can be improved.
3. But if they are not appropriate then we would like to update indexes as per the workload.

Both of these activities i.e. collection of the workload as well as to analyse of the indexes are appropriate as per the workload cannot be a manual process. You would like to use some kind of tools for this.

Let’s say you are working on a database which has thousands of tables and lots of stored procedures and it has a lot of transactions. It’s humanly not possible to go analyse that workload and come up with an accurate index plan. For the same what SQL server has done is basically it has provided two tools;

1. SQL Profiler
2. Tuning Advisor

The SQL profiler will help us to automate the collection of the workload while the tuning advisor will help us to take the workload which is been gathered by the SQL profiler and come up with appropriate indexes.

So, let’s do a small demonstration. We will pick up a database and we will pick up a couple of tables from the database and first we will collect the workload by using SQL server profiler and then will run tuning advisor on the workload to see that if the index is appropriate or not and then we will see how the performance improves after the tuning advisor.

Here, we have a customer database and this database has 2 tables one is tbl_Company and another is tbl_Customer.

In tbl_Comapany has around 3k records and tbl_Customer has 5-6 records.

Both of these tables don’t have indexes, for example, we go to tbl_Customer table we can see that.

No index now.

The same for tbl_Company currently there is no index for now.

So, one of the tables has i.e. tbl_Company has approximately 3000 records and the customer has 5 records. On those 2 tables following kind of SQL queries are fired from an application.

So now we will use the same query and will generate a workload file using SQL server profiler. We will fire the statement from the back end and will run the profiler and will first generate a workload file.

Once we generate workload file then we will take that workload file and then we will use the tuning advisor to see what kind of indexes is suggest it.

Let’s run the profiler

Click Tools then Click on SQL Server Profiler then Click on Connect.

After the step window will appear like below.

When clicking on Run button it will start capturing lots of events it will start capturing a lot of activities which are happening on the database. For example, it’s capturing some SQL Statement which are getting fires on report server like below.

This tool is heavily used when we want to debug our application or when we want to know what kind of select query is fired and what kind of data is been sent to the select queries or to your SQL queries.

Now, we are running this tool but there is one problem here is that it’s capturing lots of SQL statement which are probably not relevant to us. For example, it is capturing SQL statement which is fired on the report server and our main goal is we want to see how we can improve performance on the Customer database.

We don’t want to capture the workload which is not relevant to our tuning currently.

We just want SQL statement which are getting fired on Customer database and we want to take those SQL statements as our workload and then do the tuning. So go back again and fire the SQL Profiler and put a filter.

Click Tools then Click on SQL server Profiler then Click on Connect.

Select Tuning from the list because our activity is more relevant towards tuning as shown below.

First Select the Tuning Template and second, we would like to filter database so click on Column filters like.

Then Click on Database Name and Click on Like and then provide the name of the database.

Now, it will only capture the SQL statement which is relevant to the customer database.

Now, go to query windows and fire SQL statement couple of times then go to profiler there you can see SQL server profiler has captured the load like.

Now, save this workload and then run the tuning advisor on the workload to get the suggestions.

Stop the profiling first then click on the file and Select Save as then select Trace file.

In order to save workload file, you have to create it as a trace file and give name whatever we want.

Now, the next step is to run a tuning advisor on the workload file.

Tuning advisor

Click on Tools then Click on Database Tuning Advisor and provide our work file here so go and browse our file.

Browse and select the database.

At the top, there is a button called Start Analysis click on it.

In the tuning options, go to advanced options maximum space for recommendation can be specified.

The results show how much improvement can be done if the suggestions are implemented:

It has given one recommendation and it says that your performance will improve 34% if we are going to implement this recommendation.

In order to see recommendation, click on Definition link.

After clicking on Definition link, we can see the preview of the recommendation like.

It will say if we go and create a non-clustered index on the company Code and  Company Id our performance will improve up to 34%.