SQL Server's performance largely depends on how you created the index, i.e., clustered index or non-clustered index, etc.
Most of the time, developers 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 settings 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 the rows increase), these indexes are not able to cope up and your SQL Server's 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 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.
- The first thing you have to do is you have to calculate the workload. When you deploy your index or when you create the index for the first time, the workload may have been 10% but now, the workload has gone up to 80%. So first, you would like to calculate what is the amount of workload you have.
- Next, you would like to analyze if as per the workloads, the current indexes or the default indexes are appropriate. If they are appropriate, then it's fine. You may have to hunt around for something else to find where the SQL performance can be improved.
- 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 analyze the indexes, are appropriate as the workload cannot be a manual process. You would like to use some kind of tool 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 analyze 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.
- SQL Profiler
- Tuning Advisor
The SQL Profiler helps us to automate the collection of the workload while the Tuning Advisor helps us to take the workload that's 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 - tbl_Company and tbl_Customer.
Let us assume 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 and we can see that.
No index now.
The same for tbl_Company! Currently, there is no index.
So, one of the tables, tbl_Company, has approximately 3000 records and the customer has 5 records. On those 2 tables, the following kinds 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 a workload file then we will take that workload file and then we will use the tuning advisor to see what kind of indexes are suggested.
Let’s run the Profiler
Click Tools then Click on SQL Server Profiler then Click on Connect.
After the step the window will appear like below.
When clicking on Run button it will start capturing lots of events and activities which are happening on the database. For example, it’s capturing some SQL Statements which are getting fired on the 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: It’s capturing lots of SQL statements which are probably not relevant to us. For example, it is capturing a 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 statements which are getting fired on the 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 the database so click on Columnfilters like this:
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 a SQL statement a couple of times then go to profiler, and there you can see SQL server profiler has captured the load like this:
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 the workload file, you have to create it as a trace file and give whatever name 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, and the maximum recommended space 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 the 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%.