Indexes play a huge role in the performance of a query. Without indexes, you very simple query to get the last 100 records from a table could take forever to return. Well, maybe not forever, but you get the point. For example, I have a query and its results of the Execution Plan shown in figure 1. As you can see, it took 1 minute and 36 seconds to complete. Not very acceptable in my eyes! In this quick and dirty blog, I just want to show you an example of what results one might see by creating a simple index.

Figure 1: Execution Plan Results before Indexing

After creating an index on OrderDate_SK for this table and re-executing the same exact query to return my last 100 records, the results were returned in 1 second as shown in figure 2 below.

Figure 2: Execution Plan Results after Indexing

As you can see, this little index will play a huge role when joining or filtering on the order date column in the future. It still isn't doing an index seek, but that is better left for another blog.

Until next time, “keep your ear to the grindstone” – Good Will Hunting

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter