We will modify the About View component with FirstName and LastName filter, using which we should have an index in the data store. If we analyse the data store, we don't have such an index in the following screenshot; it would be wise to create one for better performance:

Let's start working on improvising SQL query performance, and the starting point would be tracing the query using a SQL profiler. So, copy the translated SQL query of translated About View component LINQ query from the right-click the SQL Server Profiler, paste it in the SQL Query Analyzer window, and select Trace Query in SQL Server Profiler, highlighted as shown in the following screenshot:

Once the profiler is attached, execute the query in the Query Analyzer Window, which should be displaying the following results, enable and see the actual execution plan as well:

The SQL profiler will capture anything that occurs in the database. Since we are querying the Person entity, it is capturing all queries with respect to that entity operation, which is evident in the following screenshot:

The query should be optimized using a tuning advisor and it requires the trace data to perform its analysis, so we need to save this profiler data for performance tuning analysis, as shown in the following screenshot:

The data required for the performance tuning is now ready. The next step would be to consume it in a tuning advisor. Right-click the SQL query and select Analyze Query in Database Engine Tuning Advisor for further operations, as shown in the following screenshot:

Surprisingly, it throws the following error: The argument type 'server type = Sql, server name = (localdb)mssqllocaldb, timeout = 30, database = <DatabaseName>, protocol = NotSpecified, workstation = , integrated security = True' cannot be converted into parameter type 'Microsoft.SqlServer.Management.Common.SqlConnectionInfo'. (mscorlib), which means that we are using a tuning advisor against the SQL Express Edition, and it is not supported:

We need at least the Developer Edition to consume a tuning advisor; let's download the Developer Edition (https://www.microsoft.com/en-in/sql-server/sql-server-downloads), as shown in the following screenshot, configure it, and then proceed with the performance tuning:

Once the Developer Edition is installed and the database is configured, open the tuning advisor from the query window, and it should now open properly. Then, create a new session with the following details such as Session name, Workload as File type, and its location. Select the Database for workload analysis, and finally select the databases we need to tune, as shown in the following screenshot:

We are all set to initiate the analysis. Click the Start Analysis button, as shown in the following screenshot, to start the tuning analysis, which will perform tuning analysis within the selected parameters:

When the analysis is completed, we can see the success messages printed out in the Progress tab, as displayed in the following screenshot. Our point of interest would be in generating Recommendations:

Open the Recommendations tab and it will list the recommendations, as shown in the following screenshot. It allows us to tune the performance of the database, which is a SQL query in our scenario:

Click the definition entry inside the Recommendations tab, which opens the following SQL Script Preview, to fix the performance issue. In a regular application, we would be copying this query and executing it in the database. Since we are using the ORM framework, we need to perform the same task using the framework instead, and the crux of the message is that we need an index to fine-tune the SQL query:

The HasIndex() Fluent API method would do the trick for us. It would use the marked column(s) and create a non-clustered index in the data store, and the code required for our example is shown as follows:

    protected override void OnModelCreating(ModelBuilder 
    modelBuilder)
    {
      // Code removed for brevity
      modelBuilder.Entity<Person>()
        .HasIndex(p => new { p.FirstName, p.LastName });
    }

The migration added and updated to the database would create the required non-clustered index, as shown in the following screenshot:

Let's run the same query with execution plan enabled. We can prove that the operation now consumes only one row to retrieve the record for us, as shown in the following screenshot:

We have seen how to create an index required to optimize our retrieval query, which concludes our analysis and solution towards better performance.