![]() When the SQL Server is not obtaining the plan from the query plan cache, then it creates a new plan. ClearQueryPlans removes all query plans from ProductInventoryDatabase. I shall call the method ClearQueryPlans between the first and second query. To prove that concept, we go back to our first scenario. The core concept for the most solutions is removing the existing bad plan from the query plan cache. We can solve the problem in legacy systems in different ways. How can we fix the problem? To be fair, the best solution for the problem is a good data access layer design with even data distribution helps you to avoid the problem. In our first scenario, the problem was that the first plan is perfect for a small set of data and not for s big set of data, but SQL Server has used the first created plan for both scenarios. The SQL Server generates the query plan when the first query arrived and is based on the input parameters. we have retrieved 49925 entities and then 75 entities.įigure 4 - Generated Query Plan based on big dataĪmazingly, we receive something that is now completely different! Moreover, the total duration time is only 1 second this is close to a 70 percent enhancement! Wow! This example is handling only a small set of data, think about what it would be if you had millions of records! So, why we have a new plan? The answer is a straightforward - parameter sniffing. I have also cleared all query plans to make sure that the SQL Server does not use the existing one.Īfter that, I have executed the code once more.įigure 3 - first retrieve 49925, and then 75Īs you can see above. The total return entities are still the same. The second scenario is magical I have moved the small data query code down and the big data query up (switch the code blocks), nothing special. ![]() The total time is close to 2.6 seconds, as shown above in the column “total duration,” and you can see that the execution count is 2, one time for the small query, and the other one for the big one. The SQL Server has generated the Query Plan, as shown Figure 2, based on the input of the first query (with a small number of records), and it has reused the plan for the second query (with a big number of records)!įigure 2 - Generated Query Plan based on small data Enable Query Store to Generate the Query DATABASE ProductInventoryDatabase The code for enabling the Query Store: // Delete and create the database. ![]() Besides, I have enabled the wonderful feature Query Store when the database is created, and I can use it to see the Query Plans. I am using SQL Server 2019 and EF Core 3.0 Preview. To understand the problem, let us a look behind the scenes to see what is happening. Keep it in mind: we have loaded 75 entities, after that, we have loaded 49925 entities. Public DateTimeOffset CreateDate !******************") įigure 1- First retrieve 75 entities, after that 49925 entities We produce many of them, and lastly, we seed them with dummy data. The process of looking at parameter values when compiling a stored procedure or a query is known as parameter sniffing. With this mechanism, the SQL Server can save some query generating/recompiling time. The generated plan can be used for the same query with different parameter values. The first time a query or stored procedure runs on a SQL Server, the SQL Server looks at the passed parameter values to the query and creates an execution plan based on these parameters, and it stores the generated plan with the passed parameters in the query plan cache. In this article, I will discuss one of the most popular, and hidden, problems with Entity Framework and SQL Server, and I share a few solutions for the problem. In most cases, I have discovered that the Entity Framework/Core is not guilty and that the problem is coming from somewhere else. Subsequently, I have investigated the problem to find a root. Many developers are complaining that the Entity Framework is not fast or it has a lack of performance.
0 Comments
Leave a Reply. |