To store information in sql server we created table and kept information in that table but many times we forget about to create index and due to this it directly affect the performance of your application and fetching time of records getting slow.
This article mainly focuses on how MS SQL Server uses indexes to write and read data. SQL Server arranged Data in the form of extents and pages. Each extent is of size 64 KB and having 8 pages of 8 KB sizes. An extent may have data from same or multiple table, but every page holds data from a single table only and logically, data (information) is stored in record sets in the table.
Indexes are arranged in the form of a B‐Tree where the leaf node holds the pointer or the data.
Since all the stored data is in a sorted order, indexes indeed know which record is placed where.
Hence an index greatly optimizes and enhances the data retrieval.
Creating indexes unknowingly for better data retrieval will not serve the purpose. If there are 10 indexes on a table, each time a DML (data manipulation language) is done on the table, all these 10 indexes shall be updated so that they can uniquely figure out the location of the data.
Let’s come and play with indexes.
Clustered Index-
Clustered indexes store and sort the data rows in the table or view based on their key values and these are the columns included in the index definition. Therefore a table can have only one clustered index.
When user created a primary key on the table by default a clustered index will be created. It is like your movie ticket R1/41, you know that you need to reach row num R1 and sit on seat number 41, so index physically leads you to your actual seat location.
Let’s take an example
I have created a Product table and then created a Stored Procedure to insert 199999 records into the Product table.
To for inserting records in the product table simple execute this and sit back for 5 min.
After executing this your table is ready with 199999 records data.
Now Press Control+M. This will “Include the Actual Execution Plan” in the results. Its will give you clear picture.
Now let’s run the below query
The record has been retrieved on a table scan and the logical reads are 1129.
Now click on the Execution plan tab.
Now create a clustered-index on the ID column of the product table.
Now again execute the same query and see the differences.
Now see the Execution plan tab, and result shows that the record has been retrieved on Index seek and logical reads are now 3. After the clustered index creation, SQL Server has been reduce the logical reads and the query has been optimized. Clearly the index knows where to look for the record.
Non Clustered Index
A non‐clustered index is a special type of index in which the logical order of the index that does not match the physical stored order of the rows on disk. A non-clustered index is useful for those columns that have some repeated values like description of content or price etc. in Non Clustered index leaf node a does not consist of the data pages but a pointer to it.
Internally if clustered index define its uses that one or the heap to build itself and when it uses the heap and the leaf node is a physical location of the data.
Now I back to our example, in the product table we already have cluster index on the ID column, if we will execute below query (remember first press Ctrl+M)
Now click on the execution plan tab see the below
We can see that the query first uses the clustered index to get 11 records and then uses a sort operation; the logical reads are as high as 1130. There is also a missing index suggestion.
Now we will create non clustered index on productcode column in product table by executing below query.
And execute the same query
Now you can see that logical reads have been minimized tremendously and this is the beauty of Nonclustered index with clustered index.
Now we will Using DTA
You will see how to optimize the query by using DTA.
Let start with dropping all the indexes from our table
Now we will create two workload for DTA
Go to Start>All Programs>MS SQL Server 2008>Performance Tools>SQL
Server Profiler
Once it load, click on the ‘New Trace’ by connecting to the server, now fill the General tab, fill I the trace name ‘MyTrace’ and click on the ‘save to file’ and save this file….
Next click on the Event Selection tab and unchecks below option.
Now press run and start the trace…
Go back to the profiler, and we will see the SQL query has been captured. Now Stop the trace and save it.
Ok, we have been able to prepare the workload. Now time to seek help from DTA..
Go to Start>All Programs>MS SQL Server 2008>Performance Tools>Database Engine Tuning Advisor. Or
DTA looks like below screen.
Connect to the same server hosting the Ms_Pratice database which has our Product table. In the
‘General tab’ for Workload selection,
Now check the file radio button and browse through to MyTrace.trc that we already captured from the Profiler.
No changes in the “Tuning Option’ Tab.
Now press the ‘Start Analysis” (top left with the green triangle) play button.
Once this job is completed you can see three new tabs, progress, recommendations, and reports.
Now let’s have a look at the recommendation
DTA suggests for the query that it should be creating a clustered index on the ID column of the Product
Table and also gives the estimated size/cost of the index. Now Click on the Definition ([ID]Asc) link under Definition column and you can see the T-Sql query that you need to execute.
Run that query and you shall gain an estimated improvement of 97 %.
Now execute the below query and repete the same process by saving the file..
The first advice is to create a statistics and the second being a non‐clustered index on ProductCode and ProductPrice.
Execute both of query and it improves the performance of your search result.
Now again execute the same query and see the difference.
Creating indexes is totally based on the criteria of querying. There is no hard and fast rule on the number of non‐clustered indexes that can be created on a table.
I hope by using this you can boost the performance of your SQL-Query.
Thank you…
