--- title: Optimize sql query by using Sql-server Clustered and nonClustered indexes description: "This blog talks about \"Boosting the performance of SQL-Query\"" image: https://blog.e-zest.com/hubfs/ebenezer-child-care-oak-creek-1001.jpg --- [![e-Zest logo\_An accion lab company\_Colour](https://blog.e-zest.com/hubfs/images/Logos%20with%20Tagline/Corporate/SVG/e-Zest%20logo_An%20accion%20lab%20company_Colour.svg "e-Zest logo_An accion lab company_Colour")](https://www.e-zest.com/) - [WORK](https://www.e-zest.com/our-work) - [SERVICES](https://www.e-zest.com/our-services) - [Product Engineering](https://www.e-zest.com/digital-product-engineering-services) - [Enterprise Data Services](https://www.e-zest.com/enterprise-data-services) - [Digital Commerce](https://www.e-zest.com/digital-commerce-services) - [Digital Operations](https://www.e-zest.com/digital-operations) - [Digital Consulting](https://www.e-zest.com/digital-transformation-consulting-services) - [Digital Experience Design](https://www.e-zest.com/digital-experience-design) - [INSIGHTS](https://blog.e-zest.com/) - [CAREERS](https://www.e-zest.com/careers) - [ENTERPRISE DATA SERVICES](https://www.e-zest.com/enterprise-data-services) - [GENERATIVE AI](https://www.e-zest.com/generative-ai) - [Healthcare](https://healthcare.e-zest.com) - [Contact Us](https://www.e-zest.com/contact-us) - ![Elevating Product Engineering with AI and ML](https://blog.e-zest.com/hubfs/Elevating%20Product%20Engineering%20with%20AI%20and%20ML.jpg) [BLOGS](https://blog.e-zest.com) #### Elevating Product Engineering with AI and ML As technology evolves and customer choices broaden, there is an increasing ... [Read more](https://blog.e-zest.com/elevating-product-engineering-with-ai-and-ml) - ![e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024](https://blog.e-zest.com/hubfs/e-Zest%20%E2%80%93%20An%20Accion%20Labs%20Company%20-%20Gets%20Recognition%20as%20a%20Contender%20in%20the%20ISG%20Provider%20LensTM%20Study%20on%20Digital%20Engineering%20Services%202024.jpg) [NEWS](https://blog.e-zest.com/news) #### e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024 We are delighted to announce that e-Zest – An Accion Labs Company has been ... [Read more](https://blog.e-zest.com/news/e-zest-an-accion-labs-company-gets-recognition-as-a-contender-in-the-isg-provider-lens-study-on-digital-engineering-services-2024) - ![e-Zest Solutions supports Digital Pune Mission](https://blog.e-zest.com/hubfs/blog-files/Digital%20Literacy%20Bus%20e-Zest.png) [CSR](https://blog.e-zest.com/csr) #### e-Zest Solutions supports Digital Pune Mission **Pune, India:** e-Zest Solutions, a leading digital technology innovation ... [Read more](https://blog.e-zest.com/csr/e-zest-solutions-supports-digital-pune-mission) - [Work](https://www.e-zest.com/our-work) - [Services](https://www.e-zest.com/our-services) - [Insights](https://blog.e-zest.com) - [Careers](https://www.e-zest.com/careers) - [Enterprise Data](https://www.e-zest.com/enterprise-data-services) - [Generative AI](https://www.e-zest.com/generative-ai) - [Innovations](https://www.e-zest.com/innovations) - [Healthcare](https://healthcare.e-zest.com) - [About](https://www.e-zest.com/about-us) - [Contact](https://www.e-zest.com/contact-us) - [Blog](https://blog.e-zest.com/) - [News](https://blog.e-zest.com/news) - [CSR](https://blog.e-zest.com/csr) - [![Linkedin Icon](https://blog.e-zest.com/hubfs/icons/linkedin_icon_white.svg)](https://www.linkedin.com/company/e-zest-solutions/) - [![Twitter Icon](https://blog.e-zest.com/hubfs/twitter-logo-silhouette%20(5).svg)](https://twitter.com/ezest) - [![Facebook Icon](https://blog.e-zest.com/hubfs/facebook-app-logo%20(4).svg)](https://www.facebook.com/61259256638) - [![Instagram Icon](https://blog.e-zest.com/hubfs/instagram-logo%20(4).svg)](https://www.instagram.com/ezestsolutions/) - [![Youtube Icon](https://blog.e-zest.com/hubfs/icons/youtube_icon_white.svg)](https://www.youtube.com/user/ezestcorporate) [![Go to top icon](https://blog.e-zest.com/hubfs/AA%20Assets/top%20button.svg)](https://blog.e-zest.com/optimize-sql-query-by-using-sql-server-clustered-and-nonclustered-indexes#top) Hit enter to search or ESC to close Insights e-Zest members share technology ideas to foster digital transformation. - [Blog](https://blog.e-zest.com) - [News](https://blog.e-zest.com/news) - [Podcasts](https://www.e-zest.com/podcasts) - [Healthcare](https://blog.e-zest.com/healthcare) - [CSR](https://blog.e-zest.com/csr) - [Whitepaper](https://blog.e-zest.com/whitepaper) - [Playbook](https://blog.e-zest.com/playbook) - [Media Kit](https://www.e-zest.com/media-kit) # Optimize sql query by using Sql-server Clustered and nonClustered indexes [Manojkumar Soni](https://blog.e-zest.com/author/manojkumar-soni) Dec 17, 2015 [Technology](https://blog.e-zest.com/topic/technology) - - - - 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. ![ebenezer-child-care-oak-creek-1001.jpg](https://blog.e-zest.com/hs-fs/hubfs/ebenezer-child-care-oak-creek-1001.jpg?width=514&name=ebenezer-child-care-oak-creek-1001.jpg "ebenezer-child-care-oak-creek-1001.jpg") To for inserting records in the product table simple execute this and sit back for 5 min. ![Capture.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture.jpg?width=519&height=50&name=Capture.jpg "Capture.jpg") After executing this your table is ready with 199999 records data. ![Capture..jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture..jpg?width=537&height=61&name=Capture..jpg "Capture..jpg") 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 ![Capture1.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture1.jpg?width=519&height=59&name=Capture1.jpg "Capture1.jpg") ![Capture2.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture2.jpg?width=518&height=103&name=Capture2.jpg "Capture2.jpg") The record has been retrieved on a table scan and the logical reads are 1129. Now click on the Execution plan tab. ![Capture3.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture3.jpg?width=526&height=125&name=Capture3.jpg "Capture3.jpg") Now create a clustered-index on the ID column of the product table. ![Capture4.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture4.jpg?width=515&height=50&name=Capture4.jpg "Capture4.jpg") Now again execute the same query and see the differences. ![Capture5.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture5.jpg?width=510&height=29&name=Capture5.jpg "Capture5.jpg") ![Capture6.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture6.jpg?width=541&name=Capture6.jpg "Capture6.jpg") 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) ![Capture7.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture7.jpg?width=523&height=119&name=Capture7.jpg "Capture7.jpg") Now click on the execution plan tab see the below ![Capture8.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture8.jpg?width=536&height=120&name=Capture8.jpg "Capture8.jpg") 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. ![Capture9.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture9.jpg?width=520&height=15&name=Capture9.jpg "Capture9.jpg") And execute the same query ![Capture10.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture10.jpg?width=525&height=119&name=Capture10.jpg "Capture10.jpg") ![Capture11.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture11.jpg?width=522&height=185&name=Capture11.jpg "Capture11.jpg")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 ![Capture12.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture12.jpg?width=528&height=41&name=Capture12.jpg "Capture12.jpg") 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….  ![Capture13.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture13.jpg?width=543&height=363&name=Capture13.jpg "Capture13.jpg") Next click on the Event Selection tab and unchecks below option. ![Capture14.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture14.jpg?width=550&height=369&name=Capture14.jpg "Capture14.jpg") 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** **![Capture15.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture15.jpg?width=535&height=201&name=Capture15.jpg "Capture15.jpg")** DTA looks like below screen. ![Capture16.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture16.jpg?width=524&height=292&name=Capture16.jpg "Capture16.jpg")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. ![Capture17.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture17.jpg?width=527&height=215&name=Capture17.jpg "Capture17.jpg") Once this job is completed you can see three new tabs, **progress, recommendations, and reports**. Now let’s have a look at the recommendation ![Capture18.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture18.jpg?width=537&height=241&name=Capture18.jpg "Capture18.jpg") 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.. ![Capture19.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture19.jpg?width=513&height=199&name=Capture19.jpg "Capture19.jpg")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. ![Capture20.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture20.jpg?width=352&height=424&name=Capture20.jpg "Capture20.jpg") Now again execute the same query and see the difference. ![Capture21.jpg](https://blog.e-zest.com/hs-fs/hubfs/Capture21.jpg?width=525&height=90&name=Capture21.jpg "Capture21.jpg") 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… - - - ### Similar Blog [![Managing a New Job in the Pandemic](https://blog.e-zest.com/hubfs/Managing%20a%20New%20Job2.jpg) Managing a New Job in the Pandemic A new job and a pandemic, well this has been my story so far. A recent graduate from Pune University, I was really happy when I ... By Sumedh Pathak Jun 01, 2020](https://blog.e-zest.com/managing-a-new-job-in-the-pandemic) [![12 Technology Trends to watch for in 2020](https://blog.e-zest.com/hubfs/Technology%20Trends%202020-200108.png) 12 Technology Trends to watch for in 2020 Technology has evolved at an unimaginable pace in the last decade: what seemed futuristic and impossible until a few years ago is ... By Devendra Deshmukh Jan 10, 2020](https://blog.e-zest.com/12-technology-trends-to-watch-for-in-2020) [![Horizon 2020: How technology is influencing human lives, and its impact on organizations](https://blog.e-zest.com/hubfs/blog-files/Horizon%202020_%20How%20technology%20is%20influencing%20human%20lives%2c%20and%20it%E2%80%99s%20impact%20on%20organisations.png) Horizon 2020: How technology is influencing human lives, and its impact on organizations As we step into a new decade, technologies across the board, from AI to cryptocurrency and online shopping are changing. We are ... By Devendra Deshmukh Dec 12, 2019](https://blog.e-zest.com/horizon-2020-how-technology-is-influencing-human-lives-and-its-impact-on-organisations) [![5 core elements of a Design Checklist!](https://blog.e-zest.com/hubfs/blog-files/5%20Core%20Elements%20for%20A%20Design%20Checklist.png) 5 core elements of a Design Checklist! Every design project is unique, and every project has its unique outcomes based on what eventual customer experience we are ... By Corporate Jul 10, 2019](https://blog.e-zest.com/5-core-elements-of-a-design-checklist) [![Installing Kubernetes cluster on Ubuntu 16.04](https://blog.e-zest.com/hubfs/blog-files/azure-devops.png) Installing Kubernetes cluster on Ubuntu 16.04 **Introduction:**Use of containers for deploying applications and micro-services has been a common practice widely. Orchestration of ... By Nikhil Garge Jun 26, 2019](https://blog.e-zest.com/installing-kubernetes-cluster-on-ubuntu) [What is Memcached? In the web world, every user prefers applications which are really fast. To make the web application faster and equipped to ... By Manojkumar Soni Dec 31, 2014](https://blog.e-zest.com/what-is-memcached/) ## Helping Organizations Thrive in the Digital Age [Contact Us](https://www.e-zest.com/contact-us) - [About](https://www.e-zest.com/about-us) - [Services](https://www.e-zest.com/our-services) - [Work](https://www.e-zest.com/our-work) - [Insights](https://blog.e-zest.com/) - [Healthcare](https://healthcare.e-zest.com/) - [Accion Labs](https://www.accionlabs.com/) - - - - - - Dallas - Detroit - Hannover - Vienna - London - Pune © 2025 e-Zest Solutions - [Privacy Policy](https://www.e-zest.com/privacypolicy) - [Cookie Policy](https://www.e-zest.com/cookie-policy) Contact us Contact us e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.