In this scenario it would make sense to create a clustered index that contains those two fields, something like the following. The indexes on the dedicated SQL table can be seen in SSMS, as illustrated in Figure 10.8. For more information about indexes, refer to the “Data Concepts” section in Chapter 2 and the “Design and Implement the Data Exploration Layer” section in Chapter 4.

CREATE NONCLUSTERED INDEX IX_READING_ELECTRODE_FREQUENCY
ON dbo.READING( ELECTRODE_ID, FREQUENCY_ID )

FIGURE 10.8 Tuning queries with a nonclustered index

It is also possible to reorganize or rebuild existing indexes instead of creating new ones. You would do this if your analysis showed that the existing indexes are not used and you want to reduce the impact of such a change. SQL commands using ALTER INDEX REORGANIZE and ALTER INDEX REBUILD can be used for such activities. This rebuild and reorganization of the indexes used the tempdb, so make sure there is enough space to handle the indexing activity. Another option to tune your queries is to force a query to use a different query plan. There might be scenarios where the explain plan generated by the Query Optimizer is not the most optimal one, often due to changes in usage behaviors or frequent structural changes. You might also discover when running a query against sys.query_store_plan that there exist multiple plans for the given query, and it is using a nonoptimal one. By using the following SQL command, which executes a Query Store stored procedure, you force the query identified by a query_id to a specific plan identified by plan_id. (Figure 10.7 shows the values for the two parameters.) You would be able to find the same by using one or more of the DMVs in Table 10.4.

EXEC sp_query_store_force:plan @query_id = 8418, @plan_id = 9;

Contrary to the previous example, if you find that a query is forced to a specific explain plan, you can remove the binding by executing the unforce stored procedure using the following SQL command:

EXEC sp_query_store_unforce:plan @query_id = 8418, @plan_id = 9;

Note the Force Plan and Unforce Plan buttons in Figure 10.7, just above and to the right of the graphical illustration of the explain plan. The result of clicking those buttons is the same as that realized by running the stored procedures. As shown in Table 10.5, numerous Query Store stored procedures can be useful for tuning and managing your queries.

TABLE 10.5 Query Store stored procedures

These are rather advanced activities, so you should consult a DBA who has experience with the database and tables you want to change before changing them. You should consult with someone with experience on those specific tables and database prior to tuning them because, although the intent is to improve performance, these changes can cause a disruption as the explain plans are recalculated. The result could also be a degradation in performance, which might require a rollback, so you need to know how to roll back before you implement such changes. Lastly, don’t forget about scaling, which entails increasing the performance level of your dedicated SQL pool. Refer to Table 3.8 for a list of dedicated SQL pool performance levels.

There are truly too many aspects of tuning to discuss here because of the vast variety of ways data analytics solutions can be implemented. This section provides some basic tips and will be helpful for the exam. One last tuning tip before proceeding has to do with materialized views that were introduced in Chapter 2. Remember that if your data analytics solution utilizes standard views and they appear latent, then you should consider converting them to materialized views. A materialized view will deliver better performance for the retrieval of complex queries in general and when compared to standard views, which you did in Exercise 2.3.