This post is about the importance of a right index to a query. The following simple query was considered.
<?query --
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
MAX([Extent1].[CreatedDate]) AS [A1]
FROM [dbo].[GeoIP2Location] AS [Extent1] WITH (NOLOCK)
) AS [GroupBy1]
--?>
Even simple, this query was causing troubles on our production systems. The query is generated from .NET Entity Framework. A simple nonclustered index was created on the CreatedDate column for the GeoIP2Location table.
Next figure shows two query plans: before and after the creation of the index.
Figure 1. Query plans comparison before and after the creation of the right index for the query
In the first case the query plan uses the clustered index. Query is covered by index.
In the second case the query plan uses the newly created index. Query is covered by index.
The right index was not existing for the query.
The difference is obvious:
Estimated Subtree Cost: 219.721 vs 0.0032843 (Figure 1)
DesiredMemoery: 200 vs 0 (Figure 1)
The query executes perfectly fast in the both cases. The difference is about the CPU usage.
CPU before (Figure 2):
Figure 2. CPU before the creation of the index for the query
CPU after (Figure 3):
Figure 3. CPU before the creation of the index for the query
Note
This was a case when the optimisation was done by maintaining the database with an addition of an index.
Addition of indexes must be done carefully because it can sometimes impose negative effect.
However, do know that the solution to a poor query performance is not always resolved by addition of indexes.
